Create Excel Pivot table from access

[mmogul]

I have an Access application where i create an excel worksheet from an Access database and then need to create an excel pivot table. The following code successfully creates the workbook and populates a sheet with the query. But when I try to create the pivot table I get the following error: "Object doesn't support this property or method"

My guess is that I'm not fully qualifying some objects - but if so, I don't know the syntax.

Here is the code:

Dim db As Database
Dim rs As DAO.Recordset

Dim xlWB As Object
Dim xlWS As Object
Dim xlRng As Object  ' define a range name

'open workbook
        Set xlWB = xlApp.Workbooks.Open(sDestinationFile)
        Set xlWS = xlWB.Worksheets("Data")
        Set rs = db.OpenRecordset("qryCondensed_Assumed", dbOpenSnapshot)
        
        

        Set xlRng = xlWS.Range("StartReport")   'the starting location for the report on the spreadsheet
     
        'copy Access recordset to range in a worksheet.
        xlRng.CopyFromRecordset rs
        
        'create pivot table  *** PROBLEM HERE ***

        xlWB.ActiveWorkbook.PivotCaches.Add _
        (SourceType:=1, SourceData:="Data!R1C1:R3390C38").CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1", DefaultVersion:=10

Appreciate any help.

 

[dhookom]

I typically use the macro recorder in Excel to write the code. Then I copy and paste it into Access. You may have to make a few modifications but it generally works well.

Duane
Hook'D on Access
MS Access MVP

 

[mmogul]

That is what i did -- which is why I am thinking that some of the objects may just need some qualification.

 

[PHV]

Replace thi:
xlWB.ActiveWorkbook.PivotCaches.Add
with either this:
xlApp.ActiveWorkbook.PivotCaches.Add
or this:
xlWB.PivotCaches.Add

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mmogul]

Both work. Thanks!

Now I just need to figure out how to set up the rest of the pivot. Should be easier now.

 

[ICCIIT]

Personally, I work the other way around....

1) Create the Access data source you need for your Excel pivot table (either a query or table)

2) as a 1 off copy the returned data in to an Excel worksheet

3) Create the Excel pivot table(s) using the pasted data in the worksheet

4) Use the "External Data" option within Excel to create a permanent ODBC query back to the original data source in your Access db and substitute this for the pasted data used in step 2 as the source data within the Excel workbook

5) Refresh Excel pivot tables as required either when file opened or manual using External Data toolbar.

Just as an alternative....

 

[mmogul]

Thanks ICCITT. I like that approach. However, in this case, the end users do not have access to the original data source.

 

[ICCIIT]

mmogul

They shouldn't need access to the original data as long as the ODBC connection is in place and they know how to refresh the data using the "External toolbar".

Give it a try with another user and see what happens when they use refresh.