I can't for the life of me figure out how to update my pivot tables in my workbook with VBA.
I have this code that I've read works for Excel 2007 and greater (I'm running 2016):
I also try to loop through all of my tables on the worksheet:
I also try to update them manually on the screen:
For each of the above, I started with just the 'refreshtable' method, but then read that it refreshes on the back-end and you need to update, which is why I added the 'update' method. I also added in the pivotcache.refresh to see if that can get it to work. I get no errors, just no update.
All of the above code runs back to back and it still won't update.
I then changed the PivotTable Options to update the table on workbook open, which does work, but not the way I want. I can see the values quickly change as soon as I open the workbook, but obviously none of the code above is doing anything. I need to reference this workbook from other programs/devices, so the update on workbook open option is not kicking in when I view this on my phone, for example.
Lastly, I tried to save, close, reopen, and resave the workbook at the end of my code:
This also doesn't work. I think VBA is reopening and closing, but without a user doing it manually, the PivotTable option of refreshing on open isn't firing.
Anyone have any idea how to fix this?
Thanks!
I have this code that I've read works for Excel 2007 and greater (I'm running 2016):
Code:
wb_myDatabase.RefreshAll
I also try to loop through all of my tables on the worksheet:
Code:
For Each myPivot In wb_myDatabase.Worksheets("Summary").PivotTables
myPivot.PivotCache.Refresh
myPivot.RefreshTable
myPivot.Update
Next myPivot
I also try to update them manually on the screen:
Code:
wb_myDatabase.Worksheets("Summary").PivotTables("PivotTable1").PivotCache.Refresh
wb_myDatabase.Worksheets("Summary").PivotTables("PivotTable1").RefreshTable
wb_myDatabase.Worksheets("Summary").PivotTables("PivotTable1").Update
wb_myDatabase.Worksheets("Summary").PivotTables("PivotTable4").PivotCache.Refresh
wb_myDatabase.Worksheets("Summary").PivotTables("PivotTable4").RefreshTable
wb_myDatabase.Worksheets("Summary").PivotTables("PivotTable4").Update
For each of the above, I started with just the 'refreshtable' method, but then read that it refreshes on the back-end and you need to update, which is why I added the 'update' method. I also added in the pivotcache.refresh to see if that can get it to work. I get no errors, just no update.
All of the above code runs back to back and it still won't update.
I then changed the PivotTable Options to update the table on workbook open, which does work, but not the way I want. I can see the values quickly change as soon as I open the workbook, but obviously none of the code above is doing anything. I need to reference this workbook from other programs/devices, so the update on workbook open option is not kicking in when I view this on my phone, for example.
Lastly, I tried to save, close, reopen, and resave the workbook at the end of my code:
Code:
wb_myDatabase.Close
Set wb_myDatabase = Nothing
Set wb_myDatabase = Workbooks.Open("myLocation\myFile.xlsm")
wb_myDatabase.Save
wb_myDatabase.Close
Set wb_myDatabase = Nothing
This also doesn't work. I think VBA is reopening and closing, but without a user doing it manually, the PivotTable option of refreshing on open isn't firing.
Anyone have any idea how to fix this?
Thanks!