Workbook_Open event erroneously triggered when linking to xlsm workbook

Question

I am running a [Edit] read-only (3rd party!) [/Edit] SQL database, an xlsm, an xls (saved copy of the xlsm, just without macros), and an mdb (Access database).

They are linked as follows:

• The xlsm gets data from the SQL database every 5 minutes by Application.OnTime code, triggered on Workbook_Open.
• The xlsm then saves itself as a 2003 xls.

• Since the code modules still exist at that moment (the workbook is not yet closed), I can open the Access mdb and refresh the Excel link to a table in the 2003 data, then close Access.

• I then reopen the xlsm, triggering a new Application.Ontime instance.

• I finally close the 2003 xls workbook.

This sequence runs during office hours and at the first run after 5pm sets a timer to start the process again in the morning.

My problem is whenever I open a file with a connection or link to the xlsm, the Workbook_Open event seems to trigger and I end up with the 2003 xls open on the 'client' computers. I know it's not just a leftover Application.OnTime on mine (the PC I originally created the files on) because now it's in use and other clients who have never opened the xlsm get the 2003 file randomly popping up when they're using Excel.

If that's not enough to trigger someone to know what's going on, I'll happily post code - but I am hoping someone has seen this before or knows of this rookie mistake and can simply give me a line I'm missing.

Thanks so much for your help!

Answer 1

An easy solution would be to have all other files link to the .xls file, instead of the .xlsm macro enabled file. Downside to this approach would be that you have to (manually) update all your references.