Second Sub won't run following first

[tlc_in_OK]

I have a macro that should run 2 different subs, but the second won't "fire" after the first for some reason. The first is to refresh data, the second simply does some calculations after the refresh. Both work fine when run individually. Is there a need to close the data connection or something before the second sub will run?

Here's the code for the first sub, and the one that calls them both. . .

Sub DataRefresh()
        ActiveWorkbook.Connections("ESPSupport1").Refresh
End Sub
 
 
Public Sub InitialMacro()
 Call DataRefresh
 Call CurrentStatUpdate
End Sub

 

[CWatts]

Try removing the call feature and just use the macro name.

I have several sorts merged into one big sort and it works fine, each running in succession without the call. I'm not 100% that's the problem, but perhaps worth a shot.

I asked a while back for the difference between executing a macro by the name directly or by using the call option, but never received a response so maybe someone will be able to fill us in.

 

[CWatts]

Also, dependig on your refresh macro, your calculation macro may actually be executing, before the data has been refreshed. Make a temp sheet and put a line of code in each of your macros. If you get two entries then you know both are firing and you have to look at an alternative, such as a calculation pause.

Sheets("TempSheet").Range("A1").Value = "Macro 1 Works"
Sheets("TempSheet").Range("A2").Value = "Macro 2 Works"

 

[tlc_in_OK]

Thanks C

Tried your test and both ARE firing. Do you know how to pause until the refresh is complete?

I've seen several places where they use the "BackgroundQuery:=False", but that doesn't work with my
refresh statement for some reason. It errors with an "invalid property assignment". Any ideas?

 

[taurean]

Safer Route would be:

Public Sub InitialMacro()
Call DataRefresh
End Sub

And then

Sub DataRefresh()
ActiveWorkbook.Connections("ESPSupport1").Refresh
Call CurrentStatUpdate
End Sub

 

[tlc_in_OK]

Thanks Both. . .

Since I'm using a workbook connection, I was able to "uncheck" the "Enable background refresh" in the connection properties. Never did figure out how to do it programmatically. I did take your advice though, Taurean, and adjusted the way my macros are called. Everything is working fine now. Thanks again.