We help IT Professionals succeed at work.

Automate exporting pivot table from access to excel

mph23
mph23 asked
on
Medium Priority
1,072 Views
Last Modified: 2012-05-07
I am trying to automate in Access 2003 exporting a pivot table to Excel 2003 and retaining the pivot format.

Basically, I am trying to automate the manual process of:
1) opening a query in Access in pivot table view,
2) selecting all (Ctrl A) then
3) pasting into Excel.

I tried the following but it copies the raw data into Excel and loses the pivot formatting:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qTest", "c:\test.xls", False

I also tried the following the code (see code section) from another EE question (#21973564) and I'm getting the following errors:

WB.Columns.AutoFit -> Object doesn't support this property or method
WB.Sheets(1).pivottables(1).Refresh ->Unable to get the PivotTables property of the Worksheet class

Are these errors caused by the fact that it was written for excel 2000 and I'm using Excel 2003?

Any suggestions?

Thanks


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qTest", "c:\test.xls", False
    
    'Declare and Start Microsoft Excel 2000.
    Dim AppExcel As Object, WB As Object
    Set AppExcel = CreateObject("Excel.Application")
    Set WB = AppExcel.Workbooks.Open("c:\test.xls")
    AppExcel.Visible = True
    WB.Columns.AutoFit
           
    WB.Sheets(1).pivottables(1).Refresh
           
    'Quit Microsoft Excel and release the object variable.
    AppExcel.Quit
    Set AppExcel = Nothing
    Set WB = Nothing

Open in new window

Comment
Watch Question

CERTIFIED EXPERT

Commented:
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
 
suvmitraManager

Commented:
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

Open in new window

CERTIFIED EXPERT

Commented:
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

Open in new window

mph23Software developer

Author

Commented:
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?

CERTIFIED EXPERT

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

Author

Commented:
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.

???
CERTIFIED EXPERT

Commented:
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
 
mph23Software developer

Author

Commented:
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
CERTIFIED EXPERT

Commented:
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...
mph23Software developer

Author

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

Commented:
You should be able to have more than 3 row headings, you could concatenate them:
 
e.g. NewColumn: [Field 1] & " - " & [Field2]
Rob HensonFinance Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
mph23Software developer

Author

Commented:
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.


mph23Software developer

Author

Commented:
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.

mph23Software developer

Author

Commented:
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?
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
mph23Software developer

Author

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

Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Thats exactly what I suggested yesterday!!

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

Cheers
Rob H
CERTIFIED EXPERT

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

Thanks,
Jerry
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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
mph23Software developer

Author

Commented:
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!
CERTIFIED EXPERT

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

Jerry
mph23Software developer

Author

Commented:
Prodops,

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

???
mph23Software developer

Author

Commented:
Never mind, I see they're slightly different in the first page.
mph23Software developer

Author

Commented:
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!
CERTIFIED EXPERT

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

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.