Export list of file names from folder Excel File|Get File Names and Details in folders-Code Included
#CTTUTORIAL
CODE:
Private Sub CommandButton1_Click()
'Decleare the Variable
Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer
Dim x As FileDialog
'To avoid screen flicker, turn off screen updating.
Application.ScreenUpdating = False
'code for foler selection by user
Set x = Application.FileDialog(msoFileDialogFolderPicker)
x.AllowMultiSelect = False
x.Show
folder = x.SelectedItems(1)
'getting the path here
directory = folder & "\"
' Initialize the variable directory. We use the Dir function to find the first *.xl?? file stored in this directory.
'The Dir function supports the use of multiple character (*) and single character (?) wildcards to search for all _
different type of Excel files.
fileName = Dir(directory & "*.xl??")
'The variable fileName now holds the name of the first Excel file found in the directory. Add a Do While Loop.
Do While fileName != ""
i = i + 1
j = 2
Cells(i, 1) = fileName
'There is no simple way to extract data (or sheet names) from closed Excel files. Therefore, we open the Excel file.
Workbooks.Open (directory & fileName)
'Add all the sheet names of the Excel file to the other columns of row i.
For Each sheet In Workbooks(fileName).Worksheets
Workbooks("files-in-a-directory.xlsm").Worksheets(1).Cells(i, j).Value = sheet.Name
j = j + 1
Next sheet
'Close the Excel file.
Workbooks(fileName).Close
'The Dir function is a special function. To get the other Excel files, you can use the Dir function again with no arguments.
'When no more file names match, the Dir function returns a zero-length string (""). As a result, Excel VBA will leave the Do While loop.
fileName = Dir()
Loop
'Turn on screen updating again (outside the loop).
Application.ScreenUpdating = True
End Sub
Export list of file names from Windows Explorer folder tree to Microsoft Excel|Get File Names and Details in folders-Code Included
Hit LIKE and SUBSCRIBE to our channel if you are interested in more such videos.
In this tutorial, we explained that how you can extract file details (name, size, type, path, created on, modified date) of all the files inside a folder and it's sub-folders.
We have used Recursion to solve this problem.
After watching this video, you'd have fair understanding of how to navigate through files and folders.
