Get File Names from folder

[lavppp]

I got this code below from the web and it works great. I would like to modify it to look into a fixed directory instead of being prompt to manually select a folder. Can you help? Thank you.

Option Explicit

Sub GetFileNames()

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

************************************
*** here I would like to search in a specific ***
*** folder without being prompt! ***
************************************

InitialFoldr$ = "G:\" '

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

 

[John_w]

Change:

xDirect$ = .SelectedItems(1) & "\"

to:

xDirect$ = "C:\path\to\the folder\"

and delete the unnecessary FileDialog and related code.

 

[lavppp]

Thank you,

Now that it works, after I saved, and open the file and save... you get the drill,

The macro does not work any more.

Anyone as a clue?

Thanks

 

[John_w]

I don't quite understand your reply. In what way doesn't it work? Post your code.