I developed a workbook that uses Power Query to import data from two CSV files that are saved in a Box Sync folder on my desktop. Box is a cloud storage service that routinely syncs data stored in the cloud on the user's desktop.
When another user attempts to open the workbook I created and refresh the data they are unable to because the data connections to the two csv files are absolute references to the Box Sync folder on my desktop. I believe this problem can be solved if I can find a way to dynamically update a portion of the data connection's file path in Excel.
For example:
Box Sync folders have the same file structure the only difference being the desktop reference portion of the path:
My Box Sync Folder Path: C:\Users\user.a\Box Sync\ParentFolder\SubFolder\2018\csvfile.csv
Other User's Box Sync Path: C:\Users\user.b\Box Sync\ParentFolder\SubFolder\2018\csvfile.csv
What I need to be able to do is dynamically update the 'C:\Users\user.b' portion of the path in the data connection string.
Your help is appreciated, thanks!
