Refreshing Excel Pivots from Access using VBA

[ashley25]

I have created the following code to export a Recordset into 2 seperate excel files, which works fine. However, I am now trying to also refresh the pivot tables during this proicess, which wont work. I have commented out the section I wrote to do this. Can someone assist?

Private Sub ExportFiles()
Dim appExcel As Object, wbExcel As Object
Dim RS As Recordset
 
' Run query
Set RS = CurrentDb.OpenRecordset("ALL")
 
' Create instance of Excel
Set appExcel = CreateObject("Excel.Application")
 
With appExcel
' Disable user prompts
.DisplayAlerts = False
 
' Open template 1
Set wbExcel = .Workbooks.Open("\\POISE\Data\LCS\DomGroup\ICE NMW Performance and DQ\Tasking MI\2014-15\Production Pack Template\OFFICIAL SENSITIVE Tasking Team Production Pack.xlsx")
 
With wbExcel.Worksheets("Tasking Records")
' Copy data to Excel
.Protect "barb", UserInterfaceOnly:=True
.Range("A2").CopyFromRecordset RS
 
'With wbExcel.Worksheets("Dashboard", "Pivot Data")
' 'Refresh Pivots
' .Protect "barb", UserInterfaceOnly:=True
' .Parent.RefreshAll
'End With
 
' Save and close
.Parent.SaveAs "\\POISE\Data\LCS\DomGroup\ICE NMW Performance and DQ\Tasking MI\2014-15\Team Production Pack Output\" & "OFFICIAL SENSITIVE Tasking Team Production Pack " & "Week " & Format(FiscalWeek, "w") & " " & Format(Date, "yyyymmdd") & ".xlsx"
.Parent.Close False
End With
 
' Open template 2
Set wbExcel = .Workbooks.Open("\\POISE\Data\LCS\DomGroup\ICE NMW Performance and DQ\Tasking MI\2014-15\Production Pack Template\OFFICIAL SENSITIVE Tasking SMT Production Pack.xlsx")
 
With wbExcel.Worksheets("Tasking Records")
' Copy data to Excel
.Protect "barb", UserInterfaceOnly:=True
.Range("A2").CopyFromRecordset RS
 
' Save and close
.Parent.SaveAs "\\POISE\Data\LCS\DomGroup\ICE NMW Performance and DQ\Tasking MI\2014-15\SMT Production Pack Output\" & "OFFICIAL SENSITIVE Tasking SMT Production Pack " & "Week " & Format(FiscalWeek, "w") & " " & Format(Date, "yyyymmdd") & ".xlsx"
.Parent.Close False
End With
 
' Close Excel
.Quit
End With
 
' Close recordset
RS.Close
 
' Clean up
Set RS = Nothing
Set wbExcel = Nothing
Set appExcel = Nothing
End Sub[CODE]

 

[namliam]

isnt it simply
wbExcel.refreshall
??

Another suggestion, why not tick the "refresh on open" in excel? That way anytime it is opened it should be updated and you dont have to bother upon export?

 

[pr2-eugin]

Hello ! Please use Code Tags when posting VBA Code. Please make sure you read the Post#2 in that thread.

 

[ashley25]

.refreshall is for a worksheet, and my problem is that I have pivots stored across various tabs. I tried to incoporate .refreshall within the following (which is commented out in the main code)

With wbExcel.Worksheets("Dashboard", "Pivot Data")
' 'Refresh Pivots
' .Protect "barb", UserInterfaceOnly:=True
' .Parent.RefreshAll
'End With

 

[namliam]

refreshall, atleast for me, is at workbook level...
ActiveWorkbook.RefreshAll