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

freelensia

New Member
Joined
Jul 2, 2017
Messages
17
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.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

XLRATOR

Board Regular
Joined
Mar 25, 2014
Messages
115
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,235
Messages
5,443,278
Members
405,223
Latest member
Industrial_Eng_SA

This Week's Hot Topics

Top