Update File path in VBA

[BMil8]

Hello,

This is my first post here at MrExcel, but I have been using the forum for some time now to search for answers. I haven't been able to find an answer to my latest question: When I move files/folders referenced in my VBA script, the script obviously fails since the path mentioned is no longer available. Is there a way to have the script update automatically when I change file/folder locations?

For example: Script reads

Workbooks.Open Filename:= _
"\\C:\Documents\Folder1\Folder2\MyFile.xlsx"

If I manually move MyFile from Folder2 to Folder3, I have to manually update the file path in the script. Is there any method to have the path automatically update to something such as

Workbooks.Open Filename:= _
"\\C:\Documents\Folder1\Folder3\MyFile.xlsx"

Thank you,
Brian

 

[DonkeyOte]

If the file being opened (MyFile) is in the same folder as the file in which the VBA resides then you can use the path as stipulated by ThisWorkbook, ie:

Workbooks.Open Replace(ThisWorkbook.FullName,ThisWorkbook.Name,"MyFile.xlsx")

If that is not the case then unless the file name is unique then no, at least NAFAIK... if the file is unique you can search for it but IMO - not ideal.