VBA Folder name as heading for file listing

[Melissa Styles]

Hi All,

I have managed to solve most of my problem by reading other posts, but there is just a small bit left to automate. I work at a mine site and we have to maintain a register of all of the risk assessments we complete, I have used the following code to get a list of files from a folder on the server to start in C5, and then manually added the file path to C3, and column A cells use "=HYPERLINK($C$3&C5,C5)" to give me the hyperlinks to add to the register.

How can I get the file path to populate cell C3?

Option Explicit

Sub GetFileNames()

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

InitialFoldr$ = "S:\10 Safety\10.02 Risk Management" '<<< 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

 

[Dr. Demento]

Is this what you're looking for??
Adding a File Path and Filename