首次运行时自动刷新excel中的数据透视表数据

Background : I have 2 excel sheets:

背景:我有2张excel表:

Contains pivot tables and a Macro "Refresh"

包含数据透视表和宏“刷新”

Data from SQL server

来自SQL Server的数据

Macro contains VBA code for refreshing data and updating pivot tables automatically. The data is updated every night. The following is the VBA code:

宏包含用于刷新数据和自动更新数据透视表的VBA代码。数据每晚更新。以下是VBA代码:

 Sub Refresh()
 ActiveWorkBook.RefreshAll 
 End Sub

Problem:

When I run my Macro for first time, it takes 5min to execute and my pivot table is not updated with new data.

当我第一次运行宏时,执行需要5分钟,我的数据透视表不会更新新数据。
When I run it for second time, it executes perfectly.

当我第二次运行它时,它会完美地执行。

I want my Macro to execute perfectly on first run.

我希望我的宏在第一次运行时完美执行。

Any help is greatly appreciated.

任何帮助是极大的赞赏。

Note: This is my first post so please tolerate my edit.

注意:这是我的第一篇文章,所以请容忍我的编辑。

2 个解决方案

#1

I had solved my problem:

我解决了我的问题:

I removed Macro and used an Active-x-Control button.So, on button_click() to refreshes ExternalData as well as pivotTables

我删除了Macro并使用了一个Active-x-Control按钮。因此,在button_click()上刷新ExternalData以及pivotTables
So, here is the code: I removed the code below

所以,这是代码:我删除了下面的代码

ActiveWorkBook.RefreshAll

and updated it with

并用它更新

Private Sub CommandButton1_Click()
Dim Data_query As Worksheet
Set Data_query = Sheets("Data")
Data_query.Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Application.ScreenUpdating = False
Sheets("sheet1").Select
Sheets("sheet1").PivotTables("PivotTable1").PivotCache.Refresh
Sheets("sheet1").PivotTables("PivotTable1").RefreshTable
End Sub

So now both ExternalData as well as pivotTables get refreshed at same time.

所以现在ExternalData和pivotTables同时刷新。

#2

It could be possible that when you have the workbook opening, you had other workbook opening at the same time? Try it with only the macro workbook. Also, specify the sheet with the pivot table could work as well. sheet1.refreshall

有可能当您打开工作簿时,您可以同时打开其他工作簿吗?仅使用宏工作簿尝试它。此外,指定具有数据透视表的工作表也可以工作。 sheet1.refreshall

Another method is that you set a timer with api and specify when you want to refresh.

另一种方法是使用api设置计时器并指定要刷新的时间。

Last method is you set the refresh when you open the workbook, go to thisworkbook and on open you put in the code.

最后一种方法是在打开工作簿时设置刷新,转到此工作簿并在打开时输入代码。

#1