Automate exporting pivot table from access to excel

Think you need to use autofit on a worksheet object not a workbook object, also declare workbook and excel as Excel.Application and Workbook respectively (you need to add the excel libray reference in tools>references
 

1) SUbmit the entire sub procedure here...please do not post partial.
2) If you disable automatic refresh then it won't export and you have to re-enter the data, however if you select automatic refresh it exports it inot excel - no problem.
3) When you have exported the data into excel and it creates the htm page, select on the pivot table toolbar and select table options and check the refresh on open checkbox.
4) DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "D:\Book1.xls", False, "Sheet1$"
5) Use the Excel Object Model and manually export it. But first you need to generate a SQL statement that will be a duplication of your pivot table. Then save it as a query. After you have that you can easily do a .TransferSpreadSheet to a specific workbook and sheet/range.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "PivotQuery1", "D:\Book1.xls", False, "Sheet2$"
6) See the code example

Private Sub ExportPivotTable()
'Add a reference to MS Office xx.0 Object Library
Dim oCBPT As Office.CommandBarPopup
Dim oCBEPT As Office.CommandBarButton
 
Set oCBPT = Application.CommandBars("Menu Bar").Controls("&PivotTable")
Set oCBEPT = oCBPT.Controls("E&xport to Microsoft Office Excel")
oCBEPT.Execute
End Sub

Select allOpen in new window

Here's a fix for the autofit - you need to apply the method to a worksheet object, not a workbook object...

Option Compare Database
Option Explicit
 
 
Sub ExportQuery()
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qTest", "c:\test.xls", False
    
    'Declare and Start Microsoft Excel 2000.
    Dim AppExcel As Excel.Application
    Dim WB As Workbook
    Dim ws As Worksheet
    
    Set AppExcel = New Excel.Application
    Set WB = AppExcel.Workbooks.Open("c:\test.xls")
        
    AppExcel.Visible = True
    
    Set ws = WB.Worksheets("qtest")
    ws.Columns.AutoFit
    ws.Rows.AutoFit
    WB.Sheets(1).pivottables(1).Refresh
           
    'Quit Microsoft Excel and release the object variable.
    AppExcel.Quit
    Set AppExcel = Nothing
    Set WB = Nothing
 
End Sub

Select allOpen in new window

mongoose:

I am getting a Type Mismatch error on the following line:

    Set WB = AppExcel.Workbooks.Open("c:\test.xls")

I see the parameters for Open is filename as a string so is WB setting the mismatch error?

Have you set a reference to the excel library? Tools>References>Microsoft Excel (version) Library
 

mongoose:

I put Excel. in front of the dim WB and dim ws and that mismatch error went away.

Now, I'm getting the following error on the docmd.transferspreadsheet...

Run-time error '3010':
Table 'qTest' already exists.

???

Try deleting the file "c:\test.xls" and run again - you may need to add
if dir("c:\test.xls" ) <> "" then
kill "c:\test.xls"
end if
 

mongoose:

Yes, I set the reference to the excel library.

Got pass that error, now getting 'Unable to get the PivotTables property of the Worksheet class' error on the following line:

  WB.Sheets(1).pivottables(1).Refresh

looking at your code, the process exports the query data to the spreadsheet, but does not create apivot table - one option would be to change your query to a cross tab query and perform the pivot in access - thus removing the need to run a pivot table process - I've not done any automated pivots from access in excel, but will have a look at the code and see how it works...

When I tried a crosstab query, it didn't allow me to have more than 3 row headings (I have 4).

You should be able to have more than 3 row headings, you could concatenate them:
 
e.g. NewColumn: [Field 1] & " - " & [Field2]

Thanks for the code.

When I compile the code, II get an error on the xlWB methods:

xlWB.Save
xlWB.Close

Method or data member not found.

Never mind, I added Excel to the declaration and it's ok.

Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Working on displaying more row headings. It only displays one right now but it looks like it's working.

Yeah, it works but I noticed that I can't run it twice.

I'm calling it from a button click event on a form.
When I press the button the second time without closing the form, I get an error:

Run-time error '91';
Object variable or With block variable not set

Error occurs on line #124 in your example code above:
 xlWB.ActiveSheet.PivotTables("Pivot Table").AddDataField ActiveSheet.PivotTables("Pivot Table").PivotFields(CStr(xlWS.Cells(1, 4).Value)), "Sum of " & CStr(xlWS.Cells(1, 4).Value), xlSum:

If I close the form and try it again, it works fine.

Any ideas?

THanks, I'll give it a try and let you know how it goes.

Thats exactly what I suggested yesterday!!

Prodops has given more detail with instructions etc, but still the same idea.

Cheers
Rob H

Sorry guys, I did not read every post. I will back out of this question. No points please.

Thanks,
Jerry

Jerry

No worries, no offence taken or anything. If OUR solution gets accepted then you deserve at least some of the points for the more detailed instructions.

Cheers
Rob H

Yes, the detailed instructions really helped because I was trying unsuccessfully to do it on my own earlier.

I haven't finished testing. Will keep you posted.

Thanks!

Thaks Rob. I am usually more careful about that. I appreciate your understanding.

Jerry

Prodops,

Are both pdf fiiles the same?
On the screen, the sizes show different but when I open the files, they look the same.

???

Never mind, I see they're slightly different in the first page.

Thank you to therealmongoose, robhenson and prodops for all your help. I learned lots of different things in this post and appreciate your support. I awarded points to all of you because all of the solutions worked and gave me different ways to solve the problem.

I awarded the most points to prodops because it was his detailed instructions that helped me the most to the final solution. When robhenson initially suggested the method, I tried it but couldn't get it to work so I turned to therealmongoose solution but couldn't get that to work consistently.

Thanks!

Glad you got a working solution and thanks for taking the time to post your feedback!

MPH,
Thanks for the comments and glad you were able to solve the problem.
Rob H.  - Again - sorry for not reading all the posts more carefully!  I owe you one...
All the Best.
Jerry