Change data source for pivot table

[Michal]

Hi,

I've created a pivot table on Access database that I 'sent data to
Excel'. Everything look great, but then I realised some of cells in
the source table were missing! So I have updated this nad thougth I
can right-click on pivot table, select wizard, clik arrow back and
then change the source.

To my surprise, I have seen the old Access table (without relevant
cells) and I could not change or update it. Of course I may produce a
new table with a new data selection and this works great, but I do
prefer updating formatted pivot table already - otherwise I'll have
lots of manunal work reformatting it again.

Any ideas?

Thanks,


Michal

 

[Debra Dalgleish]

You can run a macro to refresh the command text, and select all the
fields. For example:

'===========================
Sub RefreshCommandText()
'refresh pivot table command text
ActiveWorkbook.PivotCaches(1).CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\Sales.mdb`.qryInvDate"
End Sub
'=============================