Folder Search File List

[enrique63]

I found the attached spreadsheet (code is below) in the vbaexpress knowledge base, which creates a spreadsheet that will list the file names in a folder. I need something that expands on this and will deliver the file name in 3 separate columns. The file names are all composed of a 6 digit number, a one-word description, and a date (i.e. 151314 PSV 2011-11). Is this something that can be done?

Sub GetFileNames()
Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
InitialFoldr$ = "G:\" '<<< Startup folder to begin searching from
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
ActiveCell.Offset(xRow) = xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
End Sub
Many thanks,

Enrique

[Bob Phillips]

Sub GetFileNames()

Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
Dim xFnameParts As Variant

InitialFoldr$ = "G:\" '<<< Startup folder to begin searching from

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then

xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""

xFnameParts = Split(xFname$, " ")
For i = LBound(xFnameParts) To UBound(xFnameParts)

ActiveCell.Offset(xRow, i) = xFnameParts(i)
Next i

xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
End Sub

[enrique63]

Thanks for your response. I copied and pasted over the old code, but get the following error:

"Compile Error: Variable not defined"
with this code highlighted: For i = LBound(xFnameParts) To UBound(xFnameParts)

[Bob Phillips]

Just add a line


Dim i As Long


up with the other variable declarations

[enrique63]

Thank you! That did the trick!