Vba updating spreadsheet

L

lbrickpe

New member
Joined
Nov 3, 2021
Messages
4
Reaction score
0
Points
0
Age
64
Location
Knoxville, TN
Excel Version(s)
2016
I have a VBA problem someone may have an answer to.


I have a module, or macro that runs automatically when I open the spreadsheet. This macro refreshes a web query and populates a page with some data from my online database. There are further things which I would like to happen automatically, using this data: one being to save the spreadsheet with a filename built up from the data pulled by the query, and the other being to write a value back to the database.


Both of these things are do-able, and I have macro's for them that work just fine. The problem is that I cannot 'chain' these macros in any manner (I would like to call macro No2 and 3 from the first macro after the query has updated) because the spreadsheet only updates the spreadsheet cells with the retrieved data AFTER the macro finishes running. I am left with having to manually run the 2nd and 3rd macros once I see the spreadsheet cells have been updated.


I have tried sheet refresh commands and the like in the code, but that does not seem to help. Actually, it seems as if the query refresh only happens after the macro ends.

I would appreciate any advice offered!
 
Code: 
Option Explicit

Sub FirstMacro()
    'Your first macro code here
    
    SecondMacro
End Sub


Sub SecondMacro()
    'Your second macro code here
    
    ThirdMacro
End Sub


Sub ThirdMacro()
    'You third macro code here
    
    
End Sub
 
Hi Logit,

Thanks for the reply. Your structure is basically what I have. The problem is that SecondMacro and ThirdMacro refer to Spreadsheet cells the FirstMacro is meant to update, and the update does not happen till after all the macros have completed. To test this, I put a MsgBox as the last line of ThirdMacro, and the spreadsheet cells only update after I push the MsgBox OK button.

Lawrence
 
Sounds like the macros either need to have their order changed .... or ... the macro coding requires changing.
 
What's the code which does the refreshing? I'm thinking Background or not.
 
Yes, I think that Background Refresh was the problem. I have unchecked "Enable Background Refresh" and now everything waits for the query to refresh, and then the code continues with whatever it needs to do.
Thanks for the tip!
 
Rather than check/unchecking a check box box, you can (I think) more reliably use the likes of:
.Refresh BackgroundQuery:=False
in the code.
 
You must log in or register to reply here.
Back
Top