Access Pivottable wizard with Access VBA

  • Thread starter Thread starter cgh91b30
  • Start date Start date
C

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

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
 
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
 
Back
Top