Browse 551 attributed reviews, viewable separately for our classroom and online training

Details for Vinod Kumar

Vinod Kumar has participated in the following threads:

Added by Vinod Kumar on 04 Dec 2017 at 15:31


I want to list the names and durations of all videos in a folder and its subfolders using Excel VBA.   From the code below I can get the duration of video files, but I can't exclude all other files. This gives me a list of all the file names, which I don't need. Also I am failing to loop through subfolders.

What I want to acheive is for the macro to loop through all subfolders in the the given root folder and list only video names and duration in coloumns A and B.

Some help with this is truly appreciated.

Option Explicit

Dim i As Long, SourceFldr
Dim c As Range, rng As Range
Dim sFile As Variant
Dim oWSHShell As Object
Dim WS As Worksheet
Dim lRow As Long

Sub GetDuration()

    Dim fldr As FileDialog
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    Set oWSHShell = CreateObject("WScript.Shell")

    With fldr
        .Title = "Select a Source Folder"
        .AllowMultiSelect = False
        .InitialFileName = oWSHShell.SpecialFolders("Desktop")
        If .Show <> -1 Then GoTo NextCode
        SourceFldr = .SelectedItems(1)
    End With

    Dim oShell: Set oShell = CreateObject("Shell.Application")
    Dim oDir:   Set oDir = oShell.Namespace(SourceFldr)
        i = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
            For Each sFile In oDir.Items
                Cells(i, 1).Value = oDir.GetDetailsOf(sFile, 0)   'File Name
                Cells(i, 2).Value = oDir.GetDetailsOf(sFile, 27)   'File Lenght
            i = i + 1
     Next sFile

    Set oDir = Nothing
    Set oShell = Nothing

End Sub