Access Pivottable wizard with Access VBA

[cgh91b30]

I have an access db and need to dynamically set the pivottable datarang
dynamically I can't get it to work, any ideas?


'excel objects
Dim mysheet As Object, indexsheet As Object, myfield As Variant, xlap
As Object

Set xlapp = CreateObject("Excel.Application")


Set mysheet
xlapp.Workbooks.Open("c:\data\ConfigData\template\New_Scorecard_template.xls").sheets(7)


Here is the range in static format accessed in the xls.

'Open Orders Pvt Tbl'!$B$21

Need to make the $Bxx dynamic

Chri

 

[Tom Ogilvy]

Dim rw as Long
rw 21
sStr = "'Open Orders Pvt Tbl'!$B$" & rw

 

[cgh91b30]

Tom

Thanks for the fast reply!

What I need is the object based on

xlapp.Worksheets(3).PivotTables(1).??

to change the 21 to whatever number I need.

Chri

 

[Tom Ogilvy]

xlapp.Worksheets(3).PivotTables(1).TableRange1

will give you the range of the table except for page fields

..TableRange2 includes TableRange1 plus pagefields

..DataBodyRange is the data range of the pivot table

..ColumnRange range that contains the column area


..RowRange range that contains the row area

..PageRange range that contains the page field area