Results 1 to 2 of 2

VBA to export query into Excel results in Excel error (when opening the XLSX)

  1. #1
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    278

    VBA to export query into Excel results in Excel error (when opening the XLSX)

    Experts:


    Good morning -- I need some assistance w/ reviewing the VBA below.

    Code:
        If Me.Listbox_N1S_Queries = "All Data (Billet | Organization | Staff Member)" Then
            
            'Declare variables
            Dim ReportPath As String
            Dim ReportPathMsgBox As String
            Dim ReportFileName As String
            Dim OutputPathFileName As String
                    
            'Report path
            ReportPath = "\\report path on shared drive\All Data"                     'Modified the report path for proprietary reasons
            ReportPathMsgBox = "Reportpath \All Data (Query)"
               
            'Append to file name
            ReportFileName = " -- " & Format(Date, "yyyy-mm-dd") & ".xlsx"
            OutputPathFileName = ReportPath & ReportFileName
                    
            'Execute query
            DoCmd.OutputTo acOutputQuery, "Q802_AllFields", acFormatXLSX, OutputPathFileName, False
            
            'Throw message box upon successful extraction of the XLS
            MsgBox "Data has been successfully exported to the following location:" & vbCrLf & ReportPathMsgBox, vbInformation, "Information"
            
        End If
    Although the Excel file outputs successfully, I run into an Excel error when actually attempting to open the file (see attached JPG).


    To determine if a field (or any data) may contribute to the error, I included only a ~10 fields (data type = text, number, date, etc.). Also, when clicking on the "Error log" (see information below ***s), there was
    no particular reference to, e.g., a field name.


    Note: When exporting the same query using an export routine, the file opens just fine. However, I really prefer using the VBA given that I have flexibility to automatically add the current date to the filename.


    My question: Is there anything wrong with the command line "DoCmd.OutputTo acOutputQuery ..."? Is there a different format I should use for Excel 2010 or beyond?

    Thank you,
    EEH





    ************ ERROR LOG ***********************

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <recoveryLog xmlns="http://schemas.openxmlformats.org/sp...summary>Errors were detected in file '[Name of Directory]\All Data (Query)\All Data -- 2019-10-18.xlsx'</summary><repairedRecords><repairedRecord>Repaired Records: Format from /xl/styles.xml part (Styles)</repairedRecord></repairedRecords></recoveryLog>
    Attached Thumbnails Attached Thumbnails Error.JPG   ErrorLog.JPG  

  2. #2
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    278
    I found a solution -- changed line


    FROM:
    DoCmd.OutputTo acOutputQuery, "Q802_AllFields", acFormatXLSX, OutputPathFileName, False


    TO:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Q802_AllFields", OutputPathFileName, False


    It now allows me to open the Excel w/o any errors.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-22-2019, 11:03 AM
  2. Export Query Results to Excel Template
    By laterdater in forum Macros
    Replies: 2
    Last Post: 09-25-2015, 11:20 AM
  3. Replies: 20
    Last Post: 03-13-2014, 12:50 PM
  4. Replies: 3
    Last Post: 01-07-2014, 03:39 PM
  5. Replies: 1
    Last Post: 04-30-2012, 05:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums