VBA to automate creation of pivot table in excel

[SteveApa]

Hi All.


I have a database that exports raw data to excel. I want to vba a pivot table from access. I created the vba in excel and am now trying to put it into the database. The VBA works perfectlly when run from excel.. I have no problems putting the data in excel or manipulating it once it is in excel, but when I try to create the pivot table, I get "Invalid procedure call or arguement"...

All my data from access in excel Sheet1!R1C1:R299C25.

code cliff notes:
vba running from access

Public Sub Do_Pivot_1(xlapp As Object)
xlapp.ActiveWorkbook.PivotCaches.Create(SourceType :=xlDatabase, SourceData:= _
"Sheet1!R1C1:R299C25", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15

again, this code works fine when I run it from a module in excel (of course with out the xlapp.)

Thanks.

[SteveApa]

Got it! http://support.microsoft.com/kb/940166