Pivot Table Code Fails

[Jeff C]

I am generating a series of workbooks from an access database and before the
code loops to the next record set I am trying to build a pivot table in each
one.

When stepping thru the code fails:

ActiveWorkbook.PivotCaches.Add("SourceType:=xlDatabase, SourceData:=
qdf.name!R1C1:R91C11").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


qdf.name is the variable name given to the query from which the workbook is
generated and the name of the worksheet with the data.

I believe the hangup is in how I am trying to pass that variable through to
all the workbooks.

Any help is appreciated

Thanks in advance

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

I am generating a series of workbooks from an access database and before the
code loops to the next record set I am trying to build a pivot table in each
one.

When stepping thru the code fails:

ActiveWorkbook.PivotCaches.Add("SourceType:=xlDatabase, SourceData:=
qdf.name!R1C1:R91C11").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


qdf.name is the variable name given to the query from which the workbook is
generated and the name of the worksheet with the data.

I believe the hangup is in how I am trying to pass that variable through to
all the workbooks.

Still working on the problem:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTemp,
"D:\Users\" & strFacName & ".xls"


Set xls = CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("D:\Users\" & strFacName & ".xls").Worksheets(1)

'Set xwkb.worksheets(1).Name = strFacValue

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"worksheets(1)!R1C1:R2000C11").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10


The ActiveWorkbook definition section still fails