Running Data Refresh Macro, then Table Update Macro, Back-to-Back?

[chasfh]

I have two separate macros in my book: one that refreshes 12 tabs containing CSV data that points to a source outside the book (and which itself receives updated numbers each day); and one that updates data tables in other tabs in my book that rely on these 12 CSV-data tabs to be refreshed.

The idea is to refresh the CSV tabs with the outside numbers first, then update the data tables based on the newly-refreshed CSV data.

The problem I am running into is that when I try to combine the two macros, they basically run at the same time. What I typically see when I watch the book while the macro runs is that a couple of the tabs appear to refresh; then all the tables update based on the CSV data; then, when that's done, the rest of the CSV tabs appear to get refreshed.

Consequently, when this process completes, the book contains some tables that use new numbers because they happen to point to the CSV tabs that got refreshed first, and other tables using old numbers that got updated before the remaining CSV tabs got refreshed. I am trying to combine these macros so that it refreshes all 12 CSV tabs first, before it updates any data tables on other tabs. Am I making sense?

At first I tried going into VB and adding the Refresh_Tabs macro to the Update_Tables macro, with Refresh_tabs at the top, and that didn't work.

Then I found this thread:

How-To Run Multiple Macro's One After The Other

I have 4 macro's. The first macro format's data. The second macro saves the .xlsx with a unique name. The third macro merges data from other excel files. And the fourth macro saves the merged data as another .xlsx. Is there any way to run all the macro's at once and in order? If I group...

www.mrexcel.com

Which looked like it would solve my problem, but even when I followed its instructions as shown here:

Sub Update_Both()​

'​

' Update Macro​

'​

​

'​

​

Call Refresh_Tabs​

Call Update_Tables​

End Sub​

It still basically runs them at the same time, refreshing some CSV tabs, updating all the tables, and refreshing the remaining CSV tabs.

Any ideas how I can accomplish what I'm trying to here?

 

[rlv01]

You could try adding a time delay to see if that helps. Start with a large delay and if that appears to work, reduce the delay in steps.

Sub Update_Both()
    ' Update Macro
    Call Refresh_Tabs
   
    Application.Wait Now + TimeValue("0:00:30") '<-- Start with something rediculously large, then reduce it via experimentation.
    DoEvents
    
    Call Update_Tables
    DoEvents
End Sub

(Tip: For future posts , please try to use code tags like I did above when posting code. It makes your code easier to read and copy.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

)

 

[chasfh]

You could try adding a time delay to see if that helps. Start with a large delay and if that appears to work, reduce the delay in steps.

Sub Update_Both()
    ' Update Macro
    Call Refresh_Tabs
  
    Application.Wait Now + TimeValue("0:00:30") '<-- Start with something rediculously large, then reduce it via experimentation.
    DoEvents
   
    Call Update_Tables
    DoEvents
End Sub

(Tip: For future posts , please try to use code tags like I did above when posting code. It makes your code easier to read and copy.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

)

Unfortunately, this didn't work. It paused the refresh—I guess actually the whole macro—for that period of time, then it resumed running them both simultaneously.

 

[rlv01]

It paused the refresh—I guess actually the whole macro—for that period of time, then it resumed running them both simultaneously.

Given the wait statement is after Call Refresh_Tabs ,there is no way it is pausing any macro code in Refresh_Tabs , and you cannot run macros simultaneously. The code in Refresh_Tabs must complete first.

Try this mod to turn off calculation and the screen while your macros run.

Sub Update_Both()
    ' Update Macro
    
    Dim CalcState
    
    CalcState = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Call Refresh_Tabs
    DoEvents
    
    'Debug Msgbox
    MsgBox "Subroutine Refresh_Tabs has completed exection." & vbCr & vbCr & "Next will be the wait period" 'for debug purposes only 'for debug purposes only - remove when debugging complete
    Application.StatusBar = "Waiting...."
    Application.Wait Now + TimeValue("0:00:30") '<-- Start with something ridiculously large, then reduce it via experimentation.
    Application.StatusBar = ""
    
    'Debug Msgbox
    MsgBox "Wait period over." & vbCr & vbCr & "About to begin Subroutine Update_Tables" 'for debug purposes only - remove when debugging complete
    
    Call Update_Tables
    DoEvents
    
    Application.ScreenUpdating = True
    Application.Calculation = CalcState
    
    'Debug Msgbox
    MsgBox "Subroutine Refresh_Tabs has completed exection."
    
End Sub

 

[chasfh]

Given the wait statement is after Call Refresh_Tabs ,there is no way it is pausing any macro code in Refresh_Tabs , and you cannot run macros simultaneously. The code in Refresh_Tabs must complete first.

Try this mod to turn off calculation and the screen while your macros run.

Sub Update_Both()
    ' Update Macro
   
    Dim CalcState
   
    CalcState = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Call Refresh_Tabs
    DoEvents
   
    'Debug Msgbox
    MsgBox "Subroutine Refresh_Tabs has completed exection." & vbCr & vbCr & "Next will be the wait period" 'for debug purposes only 'for debug purposes only - remove when debugging complete
    Application.StatusBar = "Waiting...."
    Application.Wait Now + TimeValue("0:00:30") '<-- Start with something ridiculously large, then reduce it via experimentation.
    Application.StatusBar = ""
   
    'Debug Msgbox
    MsgBox "Wait period over." & vbCr & vbCr & "About to begin Subroutine Update_Tables" 'for debug purposes only - remove when debugging complete
   
    Call Update_Tables
    DoEvents
   
    Application.ScreenUpdating = True
    Application.Calculation = CalcState
   
    'Debug Msgbox
    MsgBox "Subroutine Refresh_Tabs has completed exection."
   
End Sub

Unfortunately, this one turned all the information on my tables to "#N/A" or zeroes, and then it refreshed all the tabs at the end, not the beginning.

Anything else I can try?

 

[Alex Blakenburg]

I think @rlv01 has more experience with this than I have but I have to ask what does "Refresh" entail, are you by any chance using Power Query ?
And if so for both the csv import and the table refresh ? Does table mean pivot or what sort of table and what is its datasource ?

 

[chasfh]

I think @rlv01 has more experience with this than I have but I have to ask what does "Refresh" entail, are you by any chance using Power Query ?
And if so for both the csv import and the table refresh ? Does table mean pivot or what sort of table and what is its datasource ?

I am using neither Power Query nor pivot tables. These are manual tables I have put together that draw in the data from the CSV tabs mostly via XLOOKUP.

The tabs I have in my workbook that import the data do so from CSV files I have downloaded to another folder; I went to the Data pulldown menu and selected From Text/CSV to establish those tabs, which I also called CSV tabs for the purposes of this post, and I can refresh my tabs (Data/Refresh All) once the CSV files in the other folder get updated on a daily basis. I wrote a short macro that refreshes all the tabs, and that's what I am trying to incorporate into the updates tables macro. IOW, I would rather run one macro that runs both macros back to back, rather than run two macros separately.

 

[chasfh]

Just to try to clarify what appears to be happening, in case all the words I am using are written in a manner that apparently serves to confuse:

I am trying to establish an ubermacro that calls two other macros and causes one macro to run first, in its entirety, and then causes the other macro to run in its entirety.

What looks to be happening is that the ubermacro is running the first macro partially, then running the second macro in its entirety, then finishing the first macro afterwards.

 

[rlv01]

What looks to be happening is that the ubermacro is running the first macro partially, then running the second macro in its entirety, then finishing the first macro afterwards.

But that is not what is happening, because VBA cannot spawn multiple threads to run two macros at the same time. Whatever it seems like, a macro like Update_Both
runs macro Refresh_Tabs first, and only when it is complete, does it run macro Update_Tables. This is easy enough to demonstrate with the debugger or a few mods:

Sub Update_Bothx()
    
    ' Update Macro
    Dim ST As Single
    
    ST = Timer
    Call Refresh_Tabs
    
    Select Case MsgBox("Macro Refresh_Tabs complete. Execution time: " & Timer - ST & " seconds" & vbCr & vbCr & "Run Update_Tables?", vbYesNo Or vbQuestion, Application.Name)
        Case vbNo
            Exit Sub
    End Select
    
    ST = Timer
    Call Update_Tables
    
    MsgBox "Macro Update_Tables complete. Execution time: " & Timer - ST & " seconds", vbYesNo Or vbQuestion, Application.Name
End Sub

However, if you have Object or OS calls to refresh half a million querytables or data connections to external data sources, those still may be trying to complete when the macro ends, but that is not a VBA issue.

You should be able to manually assess how much time is needed for the refresh and build in a time delay to let it complete before calling the macro to update your tables.

 

[Alex Blakenburg]

I am using neither Power Query

I went to the Data pulldown menu and selected From Text/CSV to establish those tab

Although you say you aren't using PQ the steps you took to import the data indicates you are.
If you go Data > Queries & Connections, what do you see in the pane that appears on the right hand side ?
If you do see Queries and right click and look at the properties do you see a box ticked that says Enable Background Refresh ?
You need to untick that box.

If you have a lot of queries which it sounds like you might have, the code below will change them all.
If after turning off background refresh your macro is still not completing the csv refresh step before running your second step, it may be that the bug with the query refresh identified 2017 is still there and you will need to do the additional step described in the link below where I originally got this code.

Sub ChangeConnectionRefreshMode()
Dim Connection As Variant
For Each Connection In ActiveWorkbook.Connections
    Connection.OLEDBConnection.BackgroundQuery = False
Next Connection
End Sub

Excel Forum

Pause macro until Power Queries finished refreshing

www.myonlinetraininghub.com