I have an Excel file that is supposed to mirror some data from an MS SQL DB.
This file is synced automatically to a remote location. I would like that whenever the Excel file is synced, the data that was loaded in the file will be up-to-date with the DB.
What I tried to do was:
- Create a connection in the Excel file, and choose to refresh it automatically.
- Keep the Excel file open (so it will keep refreshing).
- Add a global macro that saves the Excel file periodically just before every sync
This solution seemed to work but it made the Excel go crazy (kept saving the file, even when working on a completely different file). It also feels like a cumbersome solution.
I was wondering if anyone here knows or can think of a better solution?