Auto refresh Pivot tables on data change

3
votes

I have a worksheet Data which web scrapes a data based on a dynamic link. There is another PivotTable with pivot tables based on the Data worksheet.

Data worksheet uses the following macro and clears the contents of the cells before web scraping new updated data. This data is updated every 1 minute.

I have the following code which will refresh the pivot tables on data update. ThisWorkbook.Worksheets("PivotTable").PivotTables("PivotTable1").RefreshTable

Since the data takes about 20 seconds to complete updating, there is no data (as the cell contents are cleared first) for the pivot table to refresh. So, I get an error.

Data uses the following code to update data:

With ThisWorkbook.Worksheets("Data").QueryTables.Add(Connection:= _
        "<URL redacted>", Destination:=ThisWorkbook.Worksheets("Data").Range("$A$1"))
        .Name = "DataPull"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=True '        .Delete
End With

I have tried updating the .RefreshStyle = xlInsertDeleteCells to .RefreshStyle = xlOverwriteCells. But it overwrites the cells until the end of the rows of the new data. If new data (number of rows) is less than old data's rows, then the old data rows at the end are not deleted. I only want the data from the latest update to be kept.

How do I auto refresh the pivot tables based on above conditions?

2
vbaexcelpivot-table

2 Answers

1
votes

Just set .BackgroundQuery = False so that your query will be performed synchronously (meaning, it will wait for the data to be loaded before doing the pivot refresh).

0
votes

Try using a do loop while to wait for the scraping to complete.

     Do
        Err.Clear
        On Error Resume Next
        Debug.Print Err.Number
        ThisWorkbook.Worksheets("PivotTable").PivotTables("PivotTable1").RefreshTable
        Debug.Print Err.Number
     Loop While Err.Number > 0