Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Code Fails
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:=xlData base, 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 Live Well .. Be Happy In All You Do |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Code Fails
-- Jeff C Live Well .. Be Happy In All You Do "Jeff C" wrote: 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:=xlData base, 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:=xlDatab ase, SourceData:= _ "worksheets(1)!R1C1:R2000C11").CreatePivotTabl e TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 The ActiveWorkbook definition section still fails Any help is appreciated Thanks in advance -- Jeff C Live Well .. Be Happy In All You Do |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Wizard Fails to Recognize New Rows of a Named Range | Excel Worksheet Functions | |||
Pivot Table Wizard Fails to Recognize New Rows of Database | Excel Discussion (Misc queries) | |||
Pivot Table Creation Macro fails with Error #5 "Invalid procedure call or argument" | Excel Programming | |||
vlookup fails on data from a pivot table | Excel Worksheet Functions | |||
VBA Code for a pivot table to open database and to reference table current page | Excel Programming |