Keep the Enable Refresh option (VBA, SQL)

  • Thread starter Thread starter Pepe
  • Start date Start date
P

Pepe

Greetings. I have created a PivotTable using VBA and connecting to a
SQL database. The Pivot is created fine, but I cannot refresh the
PivotTable. I cannot simply delete the Pivot or the sheet each time I
need to update the data either, but I cannot figure out what code to
write to KEEP the option to Refresh the data. Basically all I have is
a PivotTable with a snapshot in time of the data and it doesn't help
me because I need to refresh the data often.

Does anyone have any thoughts?

I've been struggling on this one for a while and I'm getting close to
deadlines on some items.

Thanks in advance for any help

Paul
 
Paul,

I haven't seen any response to your request so I found the followin
code in Google. It appears to be what you ar looking for you may hav
to modify it. If this helps the credit goes to the ones who create
it.

HTH
Charles



Sub RefreshTables()
vArr = Array( _
"M:\Data Dir\Project1\MyProject1.xls", _
"M:\Data Dir\Project1\MyProject2.xls", _
"M:\Other Dir\Invoices\MyInvoice.xls", _
"P:\Shared1\SubDir1\SubDir2\Myworbkook.xls")
for i = 1 to ubound(vArr)
set wkbk = Workbooks.Open varr(i)
for each sh in wkbk.Worksheets
for each pt in sh.PivotTables
pt.PivotCache.BackgroundQuery = False
Next
Next
wkbk.RefreshAll
wkbk.Close SaveChanges:=True
Next
End Su
 
Back
Top