Pivot Table Help

[rkb0378]

I am using VB code in Access to open an Excel spread sheet that contains a Pivot Table:

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application&quot
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("v:\syb\tradesheet.xls", True)

The pivot Table is not refreshing its data from its external links when opening through access. When opening file through excel it refreshs fine. Any help?

 

[xlbo]

Try this
For Each pt in Activeworkbook.pivottables
pt.refresh
next pt
Pivot tables don't refresh on open if opened with code (like auto_open macros don't run when opened with code)
HTH
~Geoff~

 

[rkb0378]

Geoff,

Thanks for the response. I'm new to VB. Could you help me on where to add the code. (Access or Excel (where in Excel))

Thanks again.


Rob

 

[xlbo]

Erm - not too sure about VB syntax but something like this should do it:
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application&quot
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("v:\syb\tradesheet.xls", True)

For Each pt in xlWB.pivottables
pt.refresh
next pt
HTH
~Geoff~