CODEWITHSUNDEEP
vbaexcelpivot-table
Aravinth Srinivasan
Aravinth Srinivasan

Reputation: 55

Auto refresh Pivot tables on data change

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?

Upvotes: 4

Views: 1360

Answers (2)

L42
L42

Reputation: 19727

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).

Upvotes: 1

Josh Anstead
Josh Anstead

Reputation: 328

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

Upvotes: 0

Related Questions