Refreshing an Excel Pivot Table data source with VBA in MS Access -

i prefer producing excel report has no links in it, lightweight , can emailed.

so have report generate ms access - writes table tab of workbook have built pivot table uses tab it's source.

what can't seem working

xlpivot.pivottables("pvafp").refreshtable 

my code follows:

public sub viewafp(strpathtotemplate string, optional dbfullpath string)  '10 excel objects created dim xlapp object dim wb object dim xlsheet object dim xlpivot object dim rngworkingrange object dim intcol integer  dim db database  dim rsafp_pivot dao.recordset dim rsafp_data dao.recordset    dim fld variant  dim strlocation string   'uses 'my documents\cob folder of user 'c:\users\[username]\documents\cob strlocation = "c:\users\" & environ$("username") & "\documents\cob\afp_summary_" & datestring & ".xlsx"    set xlapp = createobject("excel.application")  set wb = xlapp.workbooks.open(strpathtotemplate)  set xlsheet = wb.sheets("afp data") set xlpivot = wb.sheets("afp pivot")    'allow external engines if len(dbfullpath) < 1     set db = currentdb else     set db = opendatabase(dbfullpath, true) end if   set rsafp_data = db.openrecordset("select * afp_data")  intcol = 1  each fld in rsafp_data.fields     xlsheet.cells(1, intcol).value = fld.name     debug.print fld.name     intcol = intcol + 1 next   xlsheet.range("a2").copyfromrecordset rsafp_data   set rngworkingrange = xlsheet.range("a1").currentregion       xlsheet.listobjects.add(xlsrcrange, rngworkingrange, , xlyes).name _         = "afp_data"     xlsheet.listobjects("afp_data").tablestyle = "tablestylelight9"  'set pvafp point table     xlpivot.pivottables("pvafp").changepivotcache wb.pivotcaches. _         create(sourcetype:=xldatabase, sourcedata:="afp data!afp_data", _         version:=xlpivottableversion12)    xlpivot.select  xlpivot.pivottables("pvafp").refreshtable  wb.refreshall wb.saveas filename:=strlocation  xlapp.quit  set xlsheet = nothing set fld = nothing  set rsafp_data = nothing  set db = nothing set wb = nothing set xlapp = nothing  end sub 

would nice ready use, because user needs "refresh all", before can done pivot table

see if works you:

xlpivot.pivottables("pvafp").pivotcache.refresh