3

Background : I have 2 excel sheets:

  1. Contains pivot tables and a Macro "Refresh"
  2. Data from 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:

 Sub Refresh()
 ActiveWorkBook.RefreshAll 
 End Sub

Problem:

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

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

Community
  • 1
  • 1
user42995
  • 335
  • 1
  • 5
  • 15
  • Possibly this is because the first time, the dataset is refreshed from the datasource. After that, it is cached in the pivotcaches, so unless the source data changes, it executes much faster the subsequent times! – Our Man in Bananas Apr 22 '13 at 16:14
  • Can you give me any idea to solve this issue – user42995 Apr 22 '13 at 16:23
  • ** @Philip :** You are right. On the first click it refreshes the data set from `sql server` then on second click it refreshes the pivot cache. – user42995 Apr 25 '13 at 12:45

2 Answers2

1

I had solved my problem:

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

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

user42995
  • 335
  • 1
  • 5
  • 15
0

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

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

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

user2308096
  • 1
  • 1
  • **@user2308096 :** 1. I have only one Active workbook on which i am applying Macro. 2. I have tried with `PivotCache.RefreshPeriod=0.5` but it dint work. 3. Can you elobarate more about the Last method. Thank you in advance – user42995 Apr 22 '13 at 18:30
  • Private Sub Workbook_Open() Application.WindowState= maximized End Sub you can put in refresh thisworkbook.refreshall – user2308096 Apr 22 '13 at 18:58
  • Thanks for your valuable time. I have tried your solution, but could not get the expected result. – user42995 Apr 22 '13 at 19:10
  • you could also do it with activesheet. Like the sheet with your pivot table included. Just do on cell chage you can update your sheet. since you said 2nd time works. Private Sub Worksheet_Change(ByVal Target as Range) activeworkbook.refreshall end sub – user2308096 Apr 22 '13 at 19:54