Creating a PivotTable in a Different Workbook

  • Thread starter Thread starter Bernard
  • Start date Start date
B

Bernard

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:=xlDatabase,
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
 
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:=xlDatabase, 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.
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:=xlDatabase,
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
Click to expand...
 
Back
Top