In this post I will show, how to get all files name from
a folder. Some times we require to type all file names from a folder. Typing for many files a time consume. So to get easily all files name we may use following procedure.
Sample File Download Link.
1.
Open a new excel file.
2.
Open Visual Basic code windows (Hit “ALT+F11”).
3.
Insert a new module.
4.
Copy and paste below codes and save the file as
macro enable workbook.
‘******************************************************************************
Sub GetAllFiles()
Dim fso As Object
Dim objFolder As Object
Dim objFolders As Object
Dim objFile As Object
Dim objFiles As Object
Dim strFileName As String
Dim strFilePath As String
Dim FolderPath, FolderNme As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
FolderNme = .SelectedItems(1)
End With
FolderPath = FolderNme
Set fso =
CreateObject("Scripting.FileSystemObject")
Set objFolder = fso.GetFolder(FolderPath)
Set objFiles = objFolder.Files
For Each objFile In objFiles
strFileName = objFile.Name
strFilePath = objFile.Path
ActiveCell.Value = strFileName
ActiveCell.Offset(0, 1) = strFilePath
ActiveCell.Offset(1, 0).Activate
Next
Set objFolder = Nothing
Set objFile = Nothing
Set fso = Nothing
'Columns(ActiveCell.Column).AutoFit
'Columns(ActiveCell.Offset(0,
1).Column).AutoFit
End Sub
‘******************************************************************************
5.
Now insert a form controls button from developer
tab and assign above macro.
6.
Select cell A1 and run the macro by clicking the button.
7.
Macro will get all files from the folder you
will select.
December 31, 2017
