VBA code to get file path

[Liz_Biz]

Hi Team,

I am looking for a VBA code that would ask to select a file and then give its location path in excel column F.

Thanks,
Liz.

[Olly]

This opens a file picker dialog, then writes the path of the selected file into the next empty cell in column F:

Sub fooFile()
Dim stFile As String
On Error Resume Next
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Show
    stFile = .SelectedItems(1)
End With
If Not stFile = "False" Then
    ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Value = Left(stFile, InStrRev(stFile, "\") - 1)
End If
End Sub

Alternatively, instead of browsing for a specific file, you can just pick a folder:

Sub fooFolder()
Dim stFolder As String
On Error Resume Next
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Show
    stFolder = .SelectedItems(1)
End With
If Not stFolder = "False" Then
    ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Value = stFolder
End If
End Sub

[Liz_Biz]

Thank you so much Olly for looking into this,

Below code is useful but need some modifications.

1) File dialog could select multiple files by using ctrl.
2) Add data from last available cell , not always 2nd one.
3) Path should contain complete path as "C:\Documents and Settings\Liz\Desktop\ABC.xlsx"

Sub fooFile()
Dim stFile As String
On Error Resume Next
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Show
    stFile = .SelectedItems(1)
End With
If Not stFile = "False" Then
    ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Value = Left(stFile, InStrRev(stFile, "\") - 1)
End If
End Sub

[Olly]

I don't understand your reply, liz.

1) Are you saying you WANT to allow multiple file selections, and write them all into Column F?
2) I have no idea what you mean. Please explain.
3. Simply change this line:

ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Value = Left(stFile, InStrRev(stFile, "\") - 1)

to

ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Value = stFile

[Liz_Biz]

1) Yes. Its correct.
2) Its working by solution of 3rd point.

[Olly]

Okay, here's a version which allows multiple selections:

Sub fooFile()
Dim i As Integer
On Error Resume Next
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True
    .Show
    If Not .SelectedItems.Count = 0 Then
        For i = 1 To .SelectedItems.Count
            ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Value = .SelectedItems(i)
        Next i
    End If
End With
End Sub

[Liz_Biz]

Thank you Olly. Your are great guy.

Liz.

[Olly]

Happy to help, thanks for the feedback