Manage external data source locations in Excel when sharing the file across Dropbox

[freelensia]

When working in Excel, sometimes we have external data sources.
In Windows, these files may be stored in a specific location such as C:\Users\Freelensia\Dropbox\data source.xls
When sharing the main file and the data source file with another person through file-sharing services such as Dropbox, the location of the data source will be changed to:
C:\Users\PeterSmith\Dropbox\data source.xls (from the view of the Peter Smith user)
This will break the data connection in the main file when Peter opens it. He can reset the path to the one as seen from his computer, but that will break the connection for the Freelensia user when he/she opens it from his/her end.
Is there a way to permanently fix these locations for multiple users? Such that Excel will correctly get the path when the right user opens it.
I am looking for an inherent Excel property if such a thing exists. Else VBA macros (A table with the file paths for each user, and MsgBox that ask the user to choose the user profile). Else a Windows .bat file could work as well.
Thank you for your help.

 

[XLRATOR]

Hello There,
Try to embed the windows username in the folder path using the Environ$ function as follows:

C:\Users\ & Environ$("Username") & \Dropbox\data source.xls

That should work.

Good luck.

 

[6StringJazzer]

Cross-posted at

https://www.excelforum.com/excel-pr...cel-when-sharing-the-file-across-dropbox.html