Refresh Data in Excel on User's PC

[DomSza]

Hi all,



I'm working on a new data management system for our business using Microsoft Access 2016. We have been using it for a while to connect various spreadsheets to a single data source.

The issue I'm having is that I can only refresh the data on each spreadsheet from the computer that the connection was set up on. For example, the Data Connection to Access on Spreadsheet A uses the reference C://Users/Dom/database.accdb, which works perfectly fine on my computer. When I run this on someone else's computer, it obviously cannot find the path C://Users/Dom/database.accdb.

Is there a way to update data in excel with a variable file path based on the User's computer? I'm quite familiar with VBA so I'm not opposed to including a macro on each spreadsheet which updates data.

Thanks for your help, please let me know if I can provide any more information.

EDIT - We are using Dropbox to Sync our files, so we do not have the same file paths as we would have on a server.

[Minty]

In Excel you could get the current path from
CurDir() returns C:\Users\[username]\Documents
Not sure if this helps though?

[DomSza]

I have found a solution to this, in case anyone stumbles across this thread.

Instead of finding some technical way of doing this, I'm just moving the dropbox folder on each PC to the same location. Every user's PC will have the path C:\Dropbox\Database.accdb.