Thursday, April 21, 2011

Excel pivot refresh, save and close in Access VBA code

I am using the following code to refresh Excel pivot tables from an Access application. What is the best way to save and close the Excel app after the pivots refresh? In my last attempt the code was trying to save and close before the pivots had refreshed.

Private Sub Command161_Click()   
  Dim objXL As Object, x

  On Error Resume Next
  Set objXL = CreateObject("Excel.Application")
  With objXL.Application
    .Visible = True
    'Open the Workbook
    .Workbooks.Open "myfilepath.xls"
    'Refresh Pivots
    x = .ActiveWorkbook.RefreshAll
  End With    
  Set objXL = Nothing
End Sub
From stackoverflow
  • Set the pivottable.pivotcache.backgroundquery property to False for synchronous updates.


Post a Comment