There are two ways to program VBA to get a list of files in a folder quite easily. Please follow my code in VBA to be sure.
Code in VBA:
'--------------------------------------------------------------
Option Explicit
'Nguyen Duy Tuan - duytuan@bluesofts.net
'Website: http://bluesofts.net - duytuan@bluesofts.net
'Way use function Dir in VBA
Sub GetAllFilesByDir()
Dim myFile As String, I&
I = 4 'row
myFile = Dir("C:\A-Tools\*.*") 'Path\file
While Len(myFile) > 0
I = I + 1 ' Inc row + 1
Cells(I, 1).Value = myFile
myFile = Dir 'Continue test/find...
Wend
End Sub
'Way use FSO' ActiveX
Sub GetAllFilesByFSO()
'Reference DLL
Dim fso As New FileSystemObject
Dim myFile As File, I&
I = 5
For Each myFile In fso.GetFolder("C:\A-Tools").Files
I = I + 1
Cells(I, 1).Value = myFile.Name
Next
Set fso = Nothing 'free memory
End Sub
'--------------------------------------------------------------