Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a PivotTable in a Different Workbook
I have been trying to create the PivotTable in a different
Workbook than the one containing the Macro and the source data, but I have an error every time. ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= "Database").CreatePivotTable TableDestination:="[Test.xls]Sheet1!R3C1", TableName:="PivotTable1" I would like to have the table in the file Test.xls Is there a solution? Thanks for your help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a PivotTable in a Different Workbook
I looked at VBA's help for tabledestination and found this:
TableDestination Required Variant. The cell in the upper-left corner of the PivotTable report’s destination range (the range on the worksheet where the resulting PivotTable report will be placed). The destination range must be on a worksheet in the workbook that contains the PivotCache object specified by expression. ===== But I recorded a macro and the next line in my macro would accept the other workbook's worksheet range: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "DataBase").CreatePivotTable TableDestination:="", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard _ TableDestination:=Workbooks("test.xls") _ .Worksheets("sheet1").Cells(3, 1) I used xl2002, so some of the options may not work for you (defaultversion was added in xl2002, IIRC). Might be worth a try. Bernard wrote: I have been trying to create the PivotTable in a different Workbook than the one containing the Macro and the source data, but I have an error every time. ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= "Database").CreatePivotTable TableDestination:="[Test.xls]Sheet1!R3C1", TableName:="PivotTable1" I would like to have the table in the file Test.xls Is there a solution? Thanks for your help -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating PivotTable in Excel2007 | Excel Discussion (Misc queries) | |||
Problem with creating pivottable | Excel Discussion (Misc queries) | |||
Creating a PivotTable w/o selecting data in an existing PivotTable | Excel Discussion (Misc queries) | |||
Error message when creating PivotTable | Excel Discussion (Misc queries) | |||
Creating Multiple Charts from One PivotTable | Charts and Charting in Excel |