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!

Open the workbook by disabling events using application.enableevents = false. this wont trigger the workbook open event — user9167318
I had something a bit similar. We use a tool that keeps our local clients in sync with the server version of all our files. We usually get conflict on files we didn't actually open, but are linked from another file. So in the background on some level that file is actually opened, which in your case would trigger the Workbook_Open event — Luuklag
@TanmayGawankar then you would need to do this on every file that has data linked to his XLSM file. Making all those files xlsm files. jfgoodhwe1: Wouldn't it be easier to simply have your other files link data from the 2003 file? — Luuklag
@TanmayGawankar Thank you, but I don't actually open the workbook, it's using Get Data button or just an equals formula directed to the xls workbook. — jfgoodhew1
@Luuklag I actually just had the exact same thought, and have changed my formula reference now to the 2003 xls. I'll also change the data reference when I find it and see how it goes. Thank you! — jfgoodhew1

Vegard Vegard · Accepted Answer · 2019-04-23T11:58:00

I would think the reason the XLS pops up on your clients is because Excel has to open the linked XLSM in order to access the data, and as such, Workbook_Open gets triggered.

A few alternatives to the suggestions you have in the comments;

Check Environ("Username") before executing Application.OnTime so that it will only run if the file is opened by specific users.
Use a launcher-file to open the XLSM in "admin mode" - you can do this by using the launcher-file to set a Global-scope variable, and then have the XLSM check this variable before running Application.OnTime.
Make the XLSM test for some dummy-file on the local computer - one that you easily can create by hand on the computer you need regular access to the XLSM on. For example the XLSM can look for C:\Data\xlsm-enabler.txt or whatever works, and only run the relevant macro if the file exists.
Similar to the above, use SaveSetting to set a registry key on the "admin" machine(s) and check against this inside the XLSM.
Set your XLSM up to accept parameters, and use such parameters to open the file in "admin mode". This method is outlined several other places @ SO, like here.
This is more of a hunch - you might be able to leverage the Workbook.ReadOnly property in your XLSM as a conditional. This assumes that Excel opens the XLSM in readonly-mode when accessing it via data connections, which I don't know whether is actually the case.

Workbook_Open event erroneously triggered when linking to xlsm workbook

3 Answers