Macro cancelling pending refresh problem

Morning,

I have a macro which:

Opens up a workbook
Presses refresh all
Waits 10 seconds
Selects 1 cell
Saves the workbook
Closes the workbook

At some point in this process a dialog box comes up saying 'this action will cancel a pending refresh command' or words like that. Does anyone know how I can bypass this? It interrupts the macro.

Thanks

You can try:

Please Login or Register  to view this content.

at the start of your code, then set to TRUE again at the end.

Yes and no - it stops the message appearing but it still stops the rest of the macro from working. All it does is open the book, wait 10 seconds, select the cell then close it - it doesn't refresh it

Hmm. What do you mean by 'refresh all'? Can you provide your code?

Please Login or Register  to view this content.

Hmm. Looks alright, have you tryed stepping through the macro (F8 in the VBA editor) to see exactly where the message box is called (so remove the displayalerts code for now). That should give an idea as to what is interrupting the refresh.

The msg box says "this action will cancel a pending Refresh Data command. Continue?" It has a cancel or OK option..

If you stepped through the code, which line of code is highlighted yellow when this box ocurred?

It doesn't happen when I step through it - so it must be something to do with the timing of the macro?

Now that does sound odd. It's very, very clunky (and I hate myself for suggesting it!) but try increasing your 'wait' time to 15 seconds.

If you're not too fussed about good coding practice and that works: great!

If you are fussed and it works, then it at least nails down a potential source of the issue.

Even on 15 seconds it still brings up the msg box. I'm fairly sure that it's something to do with the saving and closing of the workbook because if I take those two lines out it works perfectly. I'm going to be running this for 26 sheets though - so closing them manually afterwards is going to be quite laborious.....

Try the following instead of your save/close. If your filename includes the extension, you can delete the & ".xls" (including speech marks) from the below.

Please Login or Register  to view this content.

Will that save it?

Yes, the TRUE is a command to save.

Sorry do I actually need to enter the filename or just have FileName?

I'm assuming you have 'filename' defined somewhere in order to open your file initially, use the same string to represent both.

again it "works" but doesn't actually refresh the sheet..

Richard - how would i put that in?

Yes there is a data connection - it's a query
Yes the background refresh is ticked

The query only takes about 1 or 2 seconds to refresh - it is a quick one.

I have tried the wait at 15 seconds and the same thing happens..