Exporting Access 2007 Pivot Table to Excel error using DoCmd.RunCommand acCmdPivotTableExportToExcel RRS feed

  • Question

  • I'm running into a problem using

        DoCmd.OpenTable "TableName", acViewPivotTable, acEdit

        DoCmd.RunCommand acCmdPivotTableExportToExcel

    This is exporting an Access 2007 PivotTable to Excel 2007.

    I keep getting an error "The converter failed to save the file". I think this format defaults to .xls eventhough I'm using Office 2007 and Windows 7. For some reason the default program to open .xls files in not Excel but Microsoft Open XML converter. When I change the default program to excel I still get the error.

    Is there a way to export a pivot table to .xlsx?

    Thursday, January 5, 2012 8:20 PM


  • I found a solution.

    If you open excel first, the error does not occur.


    Set xlApp = CreateObject("Excel.Application")

         xlApp.Visible = True

     DoCmd.OpenTable "TableName", acViewPivotTable, acEdit

    DoCmd.RunCommand acCmdPivotTableExportToExcel

    • Proposed as answer by danishani Tuesday, January 10, 2012 11:13 PM
    • Marked as answer by danishani Wednesday, January 18, 2012 6:54 PM
    Thursday, January 5, 2012 9:56 PM