Macro Refresh All Save the workbook

[snadeemshaikh]

Hi Team,


I want a macro that would automatically Refresh All the whole workbook & save the current workbook on its own every 5 mins.


So the simple code would be,

Sub UpdateData()

ActiveWorkbook.RefreshAll

ActiveWorkbook.Save


Application.OnTime Now + TimeValue("00:05:00"), "UpdateData"

End Sub

Now the problem is my workbook is connected with an Access table & thus Refresh All takes 2-3 minutes. But when above macro runs it automatically prompts for saving the workbook asking to STOP THE REFRESH ALL function. Thus my macro never gets complete.


I need a code which runs REFRESH ALL on the workbook & thus when Refresh All is completed then SAVE the workbook.

 

[SirJB7]

Hi, snadeemshaikh!

Give a look at this:

http://www.pcreview.co.uk/forums/force-macro-wait-till-refreshall-done-t996448.html

It explains two methods, one using a timer for Application.Wait and another using the PivotCache.BackgroundQuery property, which I think is the smartest.

Besides, I think there's extremely overwhelming running such a process that lasts 2-3 minutes each every 5 minutes, but I don't know the contex so you should evaluate the convenience or not of that frequency.

Regards!

 

[snadeemshaikh]

Thanks a ton for that link.


The easiest way I am using is to turn off the Enable Background Refresh in Table properties. Now my macro works smoothly.