VBA Code to Finish 1st Task Before Moving on to Next?

[ExcelAtEverything]

Hello experts,

Is there some code I can add here to ensure that RefreshInventory completely finishes before moving on to call RefreshSales? I have RefreshInventory set to load to a pivot after all transformations are completed in Power Query, at which point there are several other queries sourcing from that pivot. I'm doing it this way because when I simply referenced the query for them all in PQ, the refresh time was about 2-3 times as long.

Thanks for any assistance!

Sub CallFormat()
     Call RefreshInventory
     Call RefreshSales
        Worksheets("CRP").Activate
        Range("B15").Select
        MsgBox "Refresh Complete"
End Sub

 

[ExcelAtEverything]

I've said 2x now that the timer is for causing a delay between calls. It has no bearing on how long a query or procedure runs.
It doesn't matter if procedure/query A runs 10 minutes one day and 20 the next, or how long procedure/query B runs after that. The timer only causes a delay between procedure/query A and B. The idea is to allow the system to update/refresh screens/data whatever before procedure B runs. Don't forget that the lag time between calls is in microseconds, and therein lies the problem - sometimes. I also mentioned Do Events in my first post, which seems to have been missed.

Micron, respectfully... I've heard you very clearly in fact, & have been responding where I see fit. Nothing was "missed". I don't find what you are saying to be the case in my situation, as that microsecond timer does nothing to solve my problem. When I run my initial code at start of thread, The first query starts running, and then almost immediately the second one starts (whereas I need it to wait until it's completely finished 5-25 minutes later). I keep saying over & over that I need something to make that happen. Meanwhile you continue to talk about something that according to you pushes microseconds in between the two queries. I feel like I'm being crystal clear & super respectful, so not sure what the snark is for. You literally have said LOUD & CLEAR that your solution "...has no bearing on how long a query or procedure runs" & that it's a timer "for causing a delay between calls". I'm communicating my need, & you keep telling me that you have a solution, and then you explain it in a way that clearly does not address my problem. I appreciate your help, but you're making it seem like I'm not comprehending. Respectfully I think it's you who may be missing the point.

 

[Micron]

I guess I am missing the point. How long a query runs is dependent on factors such as how many records need to be scanned, whether or not it contains calculated fields, how efficient the query plan is and so on. I cannot see how you can say you want a query that can/should run in however many minutes it should take to run 25 minutes the next time. I keep repeating myself here:
- the query runs in whatever time it takes
- it runs until it finishes and no further code is processed until the query is finished
- code then resumes and executes the next line(s)
- if that code depends on the system having refreshed data, it can fail because what happens next happens too fast
- the timer function is capable of fractional sections but the suggestion was to impose a 2 or 3 second delay, not "microseconds"
Since you won't even try make a one line change to your code to see if it solves your issue I cannot help here any further. Sorry if you feel that I was snarky. Good luck.

 

[ExcelAtEverything]

I guess I am missing the point. How long a query runs is dependent on factors such as how many records need to be scanned, whether or not it contains calculated fields, how efficient the query plan is and so on. I cannot see how you can say you want a query that can/should run in however many minutes it should take to run 25 minutes the next time. I keep repeating myself here:
- the query runs in whatever time it takes
- it runs until it finishes and no further code is processed until the query is finished
- code then resumes and executes the next line(s)
- if that code depends on the system having refreshed data, it can fail because what happens next happens too fast
- the timer function is capable of fractional sections but the suggestion was to impose a 2 or 3 second delay, not "microseconds"
Since you won't even try make a one line change to your code to see if it solves your issue I cannot help here any further. Sorry if you feel that I was snarky. Good luck.

Micron,
You are more than welcome to step out of this conversation if this is your method of response. While I certainly appreciate all the help from the amazingly talented folks on this site, I am not willing to seek that help from someone who is responding the way that you are here. I don't know why this upsets you so much, so let me try to explain my position to you one more time since you're quick on the snark but not so much on the comprehension side. I'm not sure what part of 'that's not working for me' you don't understand? Your suggestion is not the first time I've tried what you are saying. Additionally, the things you are saying are 'supposed to happen' with query refresh order of operations IS NOT WHAT IS HAPPENING. Your quoted part below IS NOT ACCURATE IN MY CASE. It IS NOT happening that way. My 2nd query DEFINITELY depends on the results of the first, the 2nd query IS REFRESHING ITSELF seconds after the first starts, and the resulting data IS 100% verified INCORRECT. The resulting data is UNREFRESHED DATA. Please read that part again before firing off another snarky email!
I am not sure why you are on these boards if when your suggestion isn't working as described, you get upset, send snarky replies, and then attempt to take even more shots on your way out. There are plenty of awesome & respectful people on this site. If you can't take it & fall apart when someone is taking issue with your response then why are you offering your help? If you feel misjudged & wish to reset, then I'm all ears. If not, then I'm okay with that too .

- it runs until it finishes and no further code is processed until the query is finished
- code then resumes and executes the next line(s)

 

[Micron]

You totally are mis-interpreting my intent and attitude since you don't seem willing to accept my apology re the snarkiness. Unfortunately, I cannot type with tone, but if I could you would realize there is nothing other than emphasis with a minor bit of underlining and pointing out what has been said before. I think I should set you to be ignored - not as punishment but as a favor to you.

If you were to visit AccessForums.net you might see a few similar responses because I'm direct & it's just my style. However, with over 10K posts there, I have helped out thousands of people and yes, a few don't sit well with my style of writing but the vast majority are thankful and receptive of the free assistance. I'm going to opt out of this thread so if you have anything to add beyond retaliation, feel free to pm me.