Which occurs first: Code in Workbook_Open procedure, or an ODBC connection set to refresh on open?

[M McAllister]

(Using Windows 7, Excel 2010)
I have a workbook that has 6 ODBC connections to refresh data tables on open (Background Refresh). I also have code in a "Workbook_Open" procedure that I need to process AFTER the data tables refresh.

So here's the question(s):
Does the ODBC Refresh happen first? If so, then great, it's doing what I need it to do.

If the code runs first (or even concurrently), then how can I stop the code until the refresh takes place?

I'm very new at using code to manipulate a spreadsheet, so any reference material related to this would be appreciated.

Thanks,
MM

 

[Jon von der Heyden]

I believe open event fires 1st. The way to go about it is to use a class module to trap the query after_refresh event and plonk your code there. Use the open event to populate a collection to house a class instance for each query table...

What is your code doing?

 

[M McAllister]

Jon,

Thanks for your reply. Unfortunately, I'm a noob with regards to VBA. How difficult is it to accomplish what you described?

Most of what I can accomplish is by recording a macro and working through it. Which I how I "wrote" the code. So far it just manipulates the data in the tables (filters/unfilters/copy and paste).

I was hoping to either pause the code for the refresh to execute or use VBA to execute the refresh in the Open Event. My last resort would be to put the code in a macro and run it manually with a button.

Thanks,
Mathew

 

[Jon von der Heyden]

Hi Matt

You can use ActiveWorkbook.RefreshAll to refresh all query tables and pivot tables in the workbook, and plonk this in the open event before your other code runs.

 

[M McAllister]

John,

WOW. Sometimes I overthink a simple solution. You're right. I can drop that in up front and accomplish the same thing as the Connection Settings. I would assume that I should uncheck the "Refresh data when opening the file" option in the Connection Settings. That would just update everything again, after the code ran, correct?

Thanks again, John. I appreciate your help.

Mathew

BTW, Is there a forum here for resources for VBA (Books, Classes, etc)? I've been using the "Front End" of Excel for a long time and I'm beyond the novice stage. I'd really like to do a deep dive in VBA, but I'm not sure the best place to start (and, No, I have VERY little programming experience). Any suggestions?

 

[Jon von der Heyden]

I can drop that in up front and accomplish the same thing as the Connection Settings. I would assume that I should uncheck the "Refresh data when opening the file" option in the Connection Settings. That would just update everything again, after the code ran, correct?[/quote]

Perhaps, it depends what your open event is doing and whether or not the data will change based on that...

BTW, Is there a forum here for resources for VBA (Books, Classes, etc)? I've been using the "Front End" of Excel for a long time and I'm beyond the novice stage. I'd really like to do a deep dive in VBA, but I'm not sure the best place to start (and, No, I have VERY little programming experience). Any suggestions?

[/quote]
MrExcel has a forum to discus books and products: MrExcel's Products: Books, CDs, Podcasts
MrExcel has books on just about every Excel topic that could assist you in your learning.

In addition to MrExcel's books I also recommend John Walkenbachs Power Programming... Oh and Excel VBA by Wrox...

 

[M McAllister]

Thanks Jon. I'll check them out.

Mathew