Log In
Register
Home
April 2, 2021
Offline
Hi Mynda,
Please assist me to refresh the pivot which is updated based on the source data which appends data in 15 minute intervals.
I have tried a few VB codes from the internet, but did not help.
Warm Regards
GK
See Answer
November 8, 2013
Offline
Use this code in a standard module:
Option Explicit
Public TimeInterval As Double
Sub RefreshQuery()
ThisWorkbook.Worksheets("SheetName").ListObjects("TableName").QueryTable.Refresh BackgroundQuery:=False
Debug.Print Now()
TimeInterval = Now() + TimeSerial(0, 15, 0)
Application.OnTime TimeInterval, "RefreshQuery"
End Sub
Sub StopTimer()
Application.OnTime earliesttime:=TimeInterval, procedure:="RefreshQuery", schedule:=False
End Sub
The following code should be placed in ThisWorkbook module:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopTimer
End Sub
Private Sub Workbook_Open()
RefreshQuery
End Sub
April 2, 2021
Offline
Thank you Catalin Bombea, will try that today and revert to you.
Regards
~GK
April 2, 2021
Offline
Bad luck mate, getting Run Time Error '9'. Subscript out of range.
Let me know if this information is enough. I had put your first code by create a module under Pivot and the second piece of code in the Pivot worksheet itself.
Regards
~GK
November 8, 2013
Offline
The code provided will refresh a power query.
(ThisWorkbook.Worksheets("SheetName").ListObjects("TableName").QueryTable.Refresh )
To refresh a pivot table:
I assume that you will change the generic names used for sheet and pivot name, right?
[...] and the second piece of code in the Pivot worksheet itself.
Please read more carefully the instructions, the second code must be placed into ThisWorkbook module, NOT a sheet module.
The following code should be placed in ThisWorkbook module:
Answers Post
1 Guest(s)
