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>