Query Refresh in VBA - is taking so long that the copy/paste after it copies old data

[Domroy]

Building a macro that will refresh a query (with existing extension), take the results (anywhere from 50,000-100,000 lines), and copy to a different spreadsheet.

The macro works, but the query refresh takes so long that the macro moves on without waiting for it to finish. Is there a way to write in a "pause" or "wait for previous step to complete before moving on" step? Or do I just have to teach the person how to refresh the query and then run the macro?

Thank you!

Judi

 

[mole999]

Maybe this will help > https://www.mrexcel.com/forum/excel-questions/36052-when-how-use-doevents-solved.html

 

[Domroy]

Oooooh! I'll try that! Thank you!

 

[Fazza]

hello, Judi

if you have queries in the worksheet - not ADO or similar - have a look at BackGroundQuery property. Set it to false.

Unchecked, like this: referencetoyourqueryobject.backgroundquery:=false
or referencetoyourqueryobject refresh backgroundquery:=false
VBA help via object browser should get you the info

regards

 

[Domroy]

Thank you. That makes sense. Other thing - with programming it to wait, didn't work. I'll try this next and let you know.

 

[Domroy]

Nope, that didn't work either. I'm still stuck. But thanks for the idea!

 

[Fazza]

Maybe there is a manual (worksheet interface) way to set the background refresh?

Otherwise it would help if details are given. There are so many ways to setup & refresh queries (and it varies by Excel version), write VBA, etc.

 

[Domroy]

What other details would you like? Happy to provide them.

 

[Fazza]

Hello, Judy.
Any details - such as previously listed. How the queries are setup & refresh, the Excel version, the VBA, etc.
Are the queries themselves in Excel? If they are on a worksheet, do they need to be? Are recordsets being used - if not, have you tried using them as an alternative.
Depending on what is needed, maybe the query refresh can be done differently - such as on file open.
There are many different ways to do this sort of thing & without knowing any details I can't help. I hope someone else can.
All the best, Fazza

 

[Domroy]

Office 365. Query is set up using get and transform, and there's a data connection to a .txt file. I had tried refresh on open, but the file is so massive, it sometimes crashed the doc on open. I'm not familiar with recordsets, so I haven't used them, no. You want to see the whole code?