waiting for the refresh 1

[chinster99]

Hi,

The problem I have is that I have about 20 reports scheduled to run on certain days. I have used VBA to refresh the data, make a copy and save the master document. For most of the reports when run in scheduler works fine. However some of the reports does not finish refreshing before it goes to the save part of the code. The weird thing is that it only happens in scheduler, when run normally the code works fine.

The message that comes up is "the action will cancel a pending refresh data command. continue?"

Can anyone help with a method that will make sure the refresh happens each time.

Thanks in advance, code is below

Leo



If ActiveWorkbook.path = origins Then
'if on open the path matches the master doc path then....

ActiveWorkbook.RefreshAll
copy_name = ThisWorkbook.Name

ActiveWorkbook.SaveCopyAs "X:\" & copy_name & ""

LogInformation ThisWorkbook.Name & " was refreshed and saved @ " & _
" " & (Now()) 'log message

ActiveWorkbook.Save ' saves master doc

Application.Quit

 

[Bowers74]

I'm not sure if this will work, but it's worth a try

This will "pause" Excel for 5 seconds:

Application.Wait Now + TimeValue("00:00:05")

I hope this helps (works)!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

If the data is coming in from an external source, the best way to do it is to create a class module for queries which then allows you to utilise the After_Refresh event

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[chinster99]

Thanks for the answers, the time iinterval method seems to work.

cheers

Leo

 

[xlbo]

Fair enough but just wait for the day when your network's running a bit slow - it'll fall over. Not because it isn't a good workaround - just because it IS a workaround.

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244