Hi All,
I am working on an excel project where I have multiple pivot tables in a report attached to an external source cube of data. The pivot tables in the workbook take about 45-60 minutes (blocking the usage of other excel sheets) to go through a total refresh of the data given the level of data that is available. I would like to be able to come in on Monday morning with the complete report having already been refreshed and ready to go. I have been scouring the web for ways to make this happen and have gotten pretty far but have hit a road block and need some help...
Below is the string of VBA text I have entered into the ThisWorkbook portion of "VBAProject" (to run at 8AM on Monday):
Private Sub Workbook_Open()
If Weekday(Date, 2) = 1 Then
Application.OnTime TimeValue("08:00:00"), "RefreshMacro"
End If
End Sub
Also, below is the string of VBA I have entered into a normal module within the "VBAProject" (to refresh all pivot tables in the workbook):
Sub RefreshMacro()
' RefreshMacro Macro
ActiveWorkbook.RefreshAll
End Sub
My understanding is that this should refresh all of the pivot tables in the entire workbook at 8AM on Monday. I have been successful with getting the report to run for a specific time on the current day (computer is awake and worksheet is open), however I have been unsuccessful with an over the weekend refresh (worksheet is open but computer is asleep).
My questions are:
-Does the workbook need to be open on the screen in order for the workbook to refresh?
-Will the workbook still refresh if the computer is asleep? (computers auto-lock after 15 minutes of inactivity)
-Would there be a better way I could accomplish this objective? Would save me a lot of time on Monday mornings waiting for the report to refresh...
Please let me know your thoughts! I appreciate any advice you can offer.
Thanks so much,
Chris
I am working on an excel project where I have multiple pivot tables in a report attached to an external source cube of data. The pivot tables in the workbook take about 45-60 minutes (blocking the usage of other excel sheets) to go through a total refresh of the data given the level of data that is available. I would like to be able to come in on Monday morning with the complete report having already been refreshed and ready to go. I have been scouring the web for ways to make this happen and have gotten pretty far but have hit a road block and need some help...
Below is the string of VBA text I have entered into the ThisWorkbook portion of "VBAProject" (to run at 8AM on Monday):
Private Sub Workbook_Open()
If Weekday(Date, 2) = 1 Then
Application.OnTime TimeValue("08:00:00"), "RefreshMacro"
End If
End Sub
Also, below is the string of VBA I have entered into a normal module within the "VBAProject" (to refresh all pivot tables in the workbook):
Sub RefreshMacro()
' RefreshMacro Macro
ActiveWorkbook.RefreshAll
End Sub
My understanding is that this should refresh all of the pivot tables in the entire workbook at 8AM on Monday. I have been successful with getting the report to run for a specific time on the current day (computer is awake and worksheet is open), however I have been unsuccessful with an over the weekend refresh (worksheet is open but computer is asleep).
My questions are:
-Does the workbook need to be open on the screen in order for the workbook to refresh?
-Will the workbook still refresh if the computer is asleep? (computers auto-lock after 15 minutes of inactivity)
-Would there be a better way I could accomplish this objective? Would save me a lot of time on Monday mornings waiting for the report to refresh...
Please let me know your thoughts! I appreciate any advice you can offer.
Thanks so much,
Chris
