none
Export a pivottable view to excel in a certain location RRS feed

  • Question

  • Hi all,

    I am trying to export a pivottable view to excel in a certain location using vba. So far I have:

            'Send out a pivottable excel sheet 9/15/15
                    Select Case REPORT_TBL("REMOTE_FILE_NAME")
                        Case "Active_Checking_Accounts_With_OD_Limits"
                        DoCmd.OutputTo acQuery, REPORT_TBL("REMOTE_FILE_NAME"), "MicrosoftExcelBiff5(*.xls)", FILE_PATH & "\" & REPORT_TBL("REPORT_FILE_NAME"), False, "", 0
                            DoCmd.OpenQuery "Active_Checking_Accounts_With_OD_Limits", acViewPivotTable, acEdit
                            
                            
                            DoCmd.RunCommand acCmdPivotTableExportToExcel
                            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Active_Checking_Accounts_With_OD_Limits", FILE_PATH & "\" & REPORT_TBL("REPORT_FILE_NAME"), True
                            DoCmd.Close acQuery, "Active_Checking_Accounts_With_OD_Limits"
                            
                    End Select
    
            'RunCommand acCmdPivotTableExportToExcel
            'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Active_Checking_Accounts_With_OD_Limits", FILE_PATH & "\" & REPORT_TBL("REPORT_FILE_NAME"), True
            'DoCmd.OutputTo acQuery, REPORT_TBL("REMOTE_FILE_NAME"), "MicrosoftExcelBiff5(*.xls)", FILE_PATH & "\" & REPORT_TBL("REPORT_FILE_NAME"), False, "", 0
            

    I get the normal Datasheet view but not the PivotTable View. Any ideas?

    Tuesday, September 15, 2015 5:03 PM

Answers

  • >>>I get the normal Datasheet view but not the PivotTable View. Any ideas?

    According to your description, I have made a sample with your codes, unfortunately, I can't reproduce this issue.

    Option Compare Database
    Public Sub ExportXLS()
    
        'DoCmd.OpenQuery "Active_Checking_Accounts_With_OD_Limits", acViewPivotTable, acEdit
        'DoCmd.RunCommand acCmdPivotTableExportToExcel
        'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Active_Checking_Accounts_With_OD_Limits", FILE_PATH & "\" & REPORT_TBL("REPORT_FILE_NAME"), True
        'DoCmd.Close acQuery, "Active_Checking_Accounts_With_OD_Limits"
        
        DoCmd.OpenQuery "empQuy", acViewPivotTable, acEdit
     
        DoCmd.RunCommand acCmdPivotTableExportToExcel
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "empQuy", "empBook.xls", True
        DoCmd.Close acQuery, "empQuy"
    
    End Sub
    

    This result like below:

     1.PNG

    So I suggest you could try to run it on the other machine, could you reproduce this issue?

    • Marked as answer by John Reddrick Tuesday, September 22, 2015 5:38 PM
    Thursday, September 17, 2015 8:55 AM
  • I really didn't solve the problem with Access and pivottable view, but I rewrote the query I had in SQL Server and made a pass through query. So I went with that path to solve my problem because I find it easier to work with SQL Server anyway.
    • Marked as answer by John Reddrick Tuesday, September 22, 2015 5:37 PM
    Tuesday, September 22, 2015 5:37 PM

All replies

  • have you tried opening that pivot view on screen - then using the ribbon's external tab to manually export it to excel?  does that result in what you seek?

    if so - there is a prompt to save the export steps - and you could use that method......

    Wednesday, September 16, 2015 4:27 PM
  • >>>I get the normal Datasheet view but not the PivotTable View. Any ideas?

    According to your description, I have made a sample with your codes, unfortunately, I can't reproduce this issue.

    Option Compare Database
    Public Sub ExportXLS()
    
        'DoCmd.OpenQuery "Active_Checking_Accounts_With_OD_Limits", acViewPivotTable, acEdit
        'DoCmd.RunCommand acCmdPivotTableExportToExcel
        'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Active_Checking_Accounts_With_OD_Limits", FILE_PATH & "\" & REPORT_TBL("REPORT_FILE_NAME"), True
        'DoCmd.Close acQuery, "Active_Checking_Accounts_With_OD_Limits"
        
        DoCmd.OpenQuery "empQuy", acViewPivotTable, acEdit
     
        DoCmd.RunCommand acCmdPivotTableExportToExcel
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "empQuy", "empBook.xls", True
        DoCmd.Close acQuery, "empQuy"
    
    End Sub
    

    This result like below:

     1.PNG

    So I suggest you could try to run it on the other machine, could you reproduce this issue?

    • Marked as answer by John Reddrick Tuesday, September 22, 2015 5:38 PM
    Thursday, September 17, 2015 8:55 AM
  • When I try and open the query up in PivotTable View and export it using the External Data tab on the ribbon to excel on my desktop, it gives me the Datasheet view. Am I missing a reference or something. I apologize for the late response and for my lack of knowledge in Access. My query takes a little time to generate, maybe around 40 seconds so I don't know if that has anything to do with it, I am thinking not.
    Thursday, September 17, 2015 5:41 PM
  • I will have to try this. I am using Access 2010 and my co-worker who I hope will be free is using Access 2013 which I am not sure it has PivotTable view.
    Thursday, September 17, 2015 5:44 PM
  • I do get an error when running a macro. I get "The command or action "OutputTo" isn't available now."

    When I comment everything out except this line:

    DoCmd.OpenQuery "Active_Checking_Accounts_With_OD_Limits", acViewPivotTable, acEdit

    Thursday, September 17, 2015 6:44 PM
  • >>>When I try and open the query up in PivotTable View and export it using the External Data tab on the ribbon to excel on my desktop, it gives me the Datasheet view.

    Open the query in PivotTable view
    1.If the query is not already open, in the Navigation Pane, double-click the query.
    2.On the Home tab, in the Views group, click View, and then click PivotTable View.

    >>>I am using Access 2010 and my co-worker who I hope will be free is using Access 2013 which I am not sure it has PivotTable view.

    There are no options to create PivotCharts and PivotTables in Access 2013.

    >>>I get "The command or action "OutputTo" isn't available now."

    Base on this error information, this line code "DoCmd.OutputTo acQuery, REPORT_TBL("REMOTE_FILE_NAME"), "MicrosoftExcelBiff5(*.xls)", FILE_PATH & "\" & REPORT_TBL("REPORT_FILE_NAME"), False, "", 0"
    would cause error "The command or action "OutputTo" isn't available now.".

    So I suggest that you need to make sure your DoCmd.OutputTo method is correct, you could refer to DoCmd.OutputTo Method (Access)
    https://msdn.microsoft.com/en-us/library/office/ff192065.aspx

    For more information, click here to refer about Changes in Office 2013

    Monday, September 21, 2015 8:52 AM
  • >>>When I try and open the query up in PivotTable View and export it using the External Data tab on the ribbon to excel on my desktop, it gives me the Datasheet view.

    Open the query in PivotTable view
    1.If the query is not already open, in the Navigation Pane, double-click the query.
    2.On the Home tab, in the Views group, click View, and then click PivotTable View.

    >>>I am using Access 2010 and my co-worker who I hope will be free is using Access 2013 which I am not sure it has PivotTable view.

    There are no options to create PivotCharts and PivotTables in Access 2013.

    >>>I get "The command or action "OutputTo" isn't available now."

    Base on this error information, this line code "DoCmd.OutputTo acQuery, REPORT_TBL("REMOTE_FILE_NAME"), "MicrosoftExcelBiff5(*.xls)", FILE_PATH & "\" & REPORT_TBL("REPORT_FILE_NAME"), False, "", 0"
    would cause error "The command or action "OutputTo" isn't available now.".

    So I suggest that you need to make sure your DoCmd.OutputTo method is correct, you could refer to DoCmd.OutputTo Method (Access)
    https://msdn.microsoft.com/en-us/library/office/ff192065.aspx

    For more information, click here to refer about Changes in Office 2013

    Tuesday, September 22, 2015 1:07 AM
  • I really didn't solve the problem with Access and pivottable view, but I rewrote the query I had in SQL Server and made a pass through query. So I went with that path to solve my problem because I find it easier to work with SQL Server anyway.
    • Marked as answer by John Reddrick Tuesday, September 22, 2015 5:37 PM
    Tuesday, September 22, 2015 5:37 PM