Excel with external data links - difference between File | Open versus double-clicking .xlsm file?

Question

Hi,

I have an Excel workbook with multiple links to external data. Some of this data comes from a 3rd party add-on and some comes from SQL server via microsoft query. All of my data links work if I refresh them manually. I have written some VBA code that I call in the Workbook_Open event that goes through all of my datalinks and refreshes them programmatically before saving the workbook under a different name and then closing. This works exactly how I want when I open Excel first and then go to the File menu and choose Open to open my .xlsm file.

When I do not open Excel first, and instead double-click on my .xlsm file to open Excel and my workbook in one step, I do not get any error messages, but my code that refreshes my data links does not retrieve the data that it should. As my data-links require that a server connection is established, I am wondering if when I open Excel by double-clicking, there may not be enough time for the server connections to be established properly before my VBA code is run.

If anyone here can give me an idea how I might be able to get around this issue, I would greatly appreciate it.

Thanks in advance,
Paul

Answer 1

Hi Palcouk and anyone else who looks at this thread,

I believe I have found a solution to my problem. I suspected all along that my problem had something to do with my VBA code that refreshes my externally linked data in certain circumstances executing before the required server connections have been fully established and ready to serve up my data.

To test this theory, I added the following code at the beginning of my code that refreshes my externally linked data....

Dim StartTime As Double

Dim PauseTime As Double

PauseTime = 10

StartTime = Timer

Do While Timer < StartTime + PauseTime

      DoEvents

Loop

... The VBA Timer function (I believe) returns the number of seconds that have elapsed since midnight. The DoEvents function (I believe) relinquishes control to Windows so that background tasks can be performed. My intention in the way I made use of these functions above was to delay execution of my "refresh" code for 10 seconds so that my server connections have ample time to become properly established before I attempt to use them.

After adding the code above, my "Refresh" code has worked properly in all scenarios in which I have tested it (Opening Excel first and doing File | Open, double-clicking on the .xlsm file, and when I open it using the scripting language behind an process visualization and control application I am working on.

So, I think I am good for now. I appreciate Palcouk's effort and anyone else who took the time to read my post.

Best regards,
Paul

Answer 2

Hi Palcouk,

If I open Excel, go to the File Menu, and select my .xlsm file from the "Recent" list (as opposed to choosing "Open" and browsing to the file location), the VBA code behind my Workbook_Open event works as it should (all my externally linked data is refreshed as I want).

If I do not open Excel first and instead right click on the Excel shortcut on the Taskbar and select my workbook file (.xlsm) from the list of recent files here, my VBA code does not refresh my externally linked data. This is the same behavior I get when I double-click on the .xlsm file.

I appreciate your help with this.

Thanks and best regards,
Paul

Answer 3

And if you open Excel. then that doc from Recent's does it then work as expected?

If the excel short cut is pinned to the task bar, rt click will show the 10 most recent docs