自动刷新数据更改时的数据透视表

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.

数据工作表使用以下宏并在Web抓取新的更新数据之前清除单元格的内容。此数据每1分钟更新一次。

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

我有以下代码将刷新数据更新的数据透视表。 ThisWorkbook.Worksheets( “数据透视表”)。数据透视表( “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.

由于数据大约需要20秒才能完成更新,因此没有数据(因为首先清除了单元格内容),因此需要刷新数据透视表。所以,我得到一个错误。

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.

我已经尝试将.RefreshStyle = xlInsertDeleteCells更新为.RefreshStyle = xlOverwriteCells。但它会覆盖单元格,直到新数据行结束。如果新数据(行数)小于旧数据的行,则不删除末尾的旧数据行。我只想保留最新更新的数据。

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

如何根据上述条件自动刷新数据透视表?

2 个解决方案

#1

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

只需设置.BackgroundQuery = False即可同步执行查询(意味着,它将等待数据在进行数据透视刷新之前加载)。

#2

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

尝试使用do循环,等待抓取完成。

     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

#1