Use VBA to get Full File Path

[KK0319]

I will start by apologizing for being a beginner with VBA and writing codes.

The issue I am having is in getting a macro to pull the full file path. I have found codes to pull just file names but I need the whole path (Ex. C:\My Documents\Production\May\1.xlsm)

I have 2 workbooks created. Workbook 1 contains a list of file paths which I entered manually, Workbook 2 contains a macro that pulls specific data from each file listed in Workbook 1. I need a macro to pull all the file names into Workbook 1 so as to eliminate the human error factor.

Here is an example of the code I am currently using that only give the file name, is there a way to modify this to get the full path or can someone provide a new code that will get me the results I need.

Function GetFileName(FileSpec As String) As Variant
Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String

On Error GoTo NoFilesFound
FileCount = 0
FileName = Dir(FileSpec)
If FileName = "" Then GoTo NoFilesFound
Do While FilePath <> ""
FileCount = FileCount + 1
ReDim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFilePath = FileArray
Exit Function
NoFilesFound:
GetFileName = False
End Function
Sub test()
Dim p As String, x As Variant
p = "Z:\Shift Reports\Shift Reports - Vorne\2014\Aseptic\May\*.xlsm"
x = GetFileName(p)
Select Case IsArray(x)
Case True 'files found
MsgBox UBound(x)
Sheets("Sheet1").Range("A:A").Clear
For i = LBound(x) To UBound(x)
Sheets("Sheet1").Cells(i, 1).Value = x(i)
Next i
Case False 'no files found
MsgBox "No matching files"
End Select
End Sub



Please note that I do not want this to open any files or dialog boxes, I just want the macro to pull the file paths and insert them into my workbook in A1.

Thanks

 

[BiocideJ]

Welcome to the board.
Also, FYI, you should use

 tags when posting code.

I haven't tested this, but you should be able to replace this line
[code]FileArray(FileCount) = FileName

With this

FileArray(FileCount) = FileSpec & IIf(Right(FileSpec,1)="\","","\") & FileName

 

[KK0319]

Thank you very much for the tip! Also thank you for the change in the code, works great!