A way of capturing the new path a file is stored in

[John]

Hi,

I'm trying to find a way to programatically return the path of a file.
Currently the path(s) are hard coded in the code. I would like to be able to
store the new path based on the user selecting the file in the new location:

In VBA i run a procedure that opens a file based on a hard coded path. As
the file exists in another folder Excel tells me it cant find the file.

So i would like to give the user the option to point to the new location of
the file.
When that file is selected, Excel writes the location of the new path into
the code.
Is this possible in VBA, and if so, how would i code this please?

Thanks for helping me out with this.
Regards,
John

 

[Jacob Skaria]

Dear John

Suggest you to use the FilePicker....try the below

Sub Mac()
Dim fd As FileDialog
Dim strFile As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
..Filters.Add "Excel Files", "*.xls"
..AllowMultiSelect = False
If .Show Then strFile = .SelectedItems(1)
End With

MsgBox strFile
End Sub