Use VBA to export form data to Excel, but keep getting "We Found a Problem..." when opening the file in Excel

[jco23]

I'm using "DoCmd.RunCommand acCmdOutputToExcel" to export query results from a form to Excel. The data exports, but each time, the user gets the following error message when Excel attempts to automatically open the file, "We found a problem with some content in "myform.xlsx. Do you want us to try to recover as much as we can? If you trust the source of the workbook, click Yes."
After clicking yes, the workbook opens with the data, but still get another message indicating what "repairs" were made: "Repaired Records: Format from /xl/styles.xml part (Styles).
after pressing close, everything is fine (except that the values in the date column are not in the right format).

What additional coding could I include to prevent these messages from popping up for each user (as well as getting the dates to be in the correct format)?

I presume I could use, "DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12" or "DoCmd.OutputTo acOutputReport", but then I would need to specify a location for the file to be saved (and they only seem to save in .xls format rather than .xlsx), and I'd rather avoid that (unless this is the most efficient method).

thanks!

 

[Gasman]

Try
docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml

Another way would be by recordset.

Function SendToExcel(strTQName As String, strSheetName As String, strPath As String)

' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
' strPath is full path and name of Excel workbook

    'Dim ApXL As Object, xlWBk As Object, xlWSh As Object
    Dim ApXL As Excel.Application
    Dim xlWBk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet
    Dim rs As DAO.Recordset

    On Error GoTo Err_Handler
'    Debug.Print "strTQname: " & strTQName
'    Debug.Print "strSheetName: " & strSheetName
'    Debug.Print "strPath: " & strPath

    Set rs = CurrentDb.OpenRecordset(strTQName)
    If rs.EOF Then
        MsgBox "No records to export"
        Exit Function
    End If
    Set ApXL = Excel.Application


    Set xlWBk = ApXL.Workbooks.Open(strPath)

   

    'Exit Function

    Set xlWsh = xlWBk.Worksheets(strSheetName)

    rs.MoveFirst

    xlWsh.Range("A2").CopyFromRecordset rs

    'Selects the first cell to unselect all cells

    'xlWsh.Range("A2").Select
   ' xlWBk.Worksheets("Chart").Select
    ApXL.Visible = True
    'xlWsh.Activate

'    xlWsh.Cells.Rows(4).AutoFilter
'
'    xlWsh.Cells.Rows(4).EntireColumn.AutoFit



    rs.Close

    Set rs = Nothing

    'Remove prompts to save the report

    ApXL.DisplayAlerts = False

    xlWBk.Save    'As "Put the path where you want the file saved OR change to just save your existing file", 51

    ApXL.DisplayAlerts = True
    
    Exit Function

Err_Handler:

    DoCmd.SetWarnings True

    MsgBox Err.Description, vbExclamation, Err.Number

    Exit Function

End Function

 

[June7]

I tested DoCmd.RunCommand acCmdOutputToExcel to export a table and form. Don't get errors. Excel opens just fine.

What date format do you want?

acSpreadsheetTypeExcel12Xml will allow saving as .xlsx

 

[jco23]

Try
docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml

Another way would be by recordset.

Function SendToExcel(strTQName As String, strSheetName As String, strPath As String)

' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
' strPath is full path and name of Excel workbook

    'Dim ApXL As Object, xlWBk As Object, xlWSh As Object
    Dim ApXL As Excel.Application
    Dim xlWBk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet
    Dim rs As DAO.Recordset

    On Error GoTo Err_Handler
'    Debug.Print "strTQname: " & strTQName
'    Debug.Print "strSheetName: " & strSheetName
'    Debug.Print "strPath: " & strPath

    Set rs = CurrentDb.OpenRecordset(strTQName)
    If rs.EOF Then
        MsgBox "No records to export"
        Exit Function
    End If
    Set ApXL = Excel.Application


    Set xlWBk = ApXL.Workbooks.Open(strPath)

  

    'Exit Function

    Set xlWsh = xlWBk.Worksheets(strSheetName)

    rs.MoveFirst

    xlWsh.Range("A2").CopyFromRecordset rs

    'Selects the first cell to unselect all cells

    'xlWsh.Range("A2").Select
   ' xlWBk.Worksheets("Chart").Select
    ApXL.Visible = True
    'xlWsh.Activate

'    xlWsh.Cells.Rows(4).AutoFilter
'
'    xlWsh.Cells.Rows(4).EntireColumn.AutoFit



    rs.Close

    Set rs = Nothing

    'Remove prompts to save the report

    ApXL.DisplayAlerts = False

    xlWBk.Save    'As "Put the path where you want the file saved OR change to just save your existing file", 51

    ApXL.DisplayAlerts = True
   
    Exit Function

Err_Handler:

    DoCmd.SetWarnings True

    MsgBox Err.Description, vbExclamation, Err.Number

    Exit Function

End Function

Try
docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml

Another way would be by recordset.

Function SendToExcel(strTQName As String, strSheetName As String, strPath As String)

' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
' strPath is full path and name of Excel workbook

    'Dim ApXL As Object, xlWBk As Object, xlWSh As Object
    Dim ApXL As Excel.Application
    Dim xlWBk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet
    Dim rs As DAO.Recordset

    On Error GoTo Err_Handler
'    Debug.Print "strTQname: " & strTQName
'    Debug.Print "strSheetName: " & strSheetName
'    Debug.Print "strPath: " & strPath

    Set rs = CurrentDb.OpenRecordset(strTQName)
    If rs.EOF Then
        MsgBox "No records to export"
        Exit Function
    End If
    Set ApXL = Excel.Application


    Set xlWBk = ApXL.Workbooks.Open(strPath)

  

    'Exit Function

    Set xlWsh = xlWBk.Worksheets(strSheetName)

    rs.MoveFirst

    xlWsh.Range("A2").CopyFromRecordset rs

    'Selects the first cell to unselect all cells

    'xlWsh.Range("A2").Select
   ' xlWBk.Worksheets("Chart").Select
    ApXL.Visible = True
    'xlWsh.Activate

'    xlWsh.Cells.Rows(4).AutoFilter
'
'    xlWsh.Cells.Rows(4).EntireColumn.AutoFit



    rs.Close

    Set rs = Nothing

    'Remove prompts to save the report

    ApXL.DisplayAlerts = False

    xlWBk.Save    'As "Put the path where you want the file saved OR change to just save your existing file", 51

    ApXL.DisplayAlerts = True
   
    Exit Function

Err_Handler:

    DoCmd.SetWarnings True

    MsgBox Err.Description, vbExclamation, Err.Number

    Exit Function

End Function

ah, I was leaving out the "xml" from there. that does help, thank you.
but still would rather NOT save the file to the user's CPU or network....

 

[jco23]

I tested DoCmd.RunCommand acCmdOutputToExcel to export a table and form. Don't get errors. Excel opens just fine.

What date format do you want?

acSpreadsheetTypeExcel12Xml will allow saving as .xlsx

just the standard mm/dd/yy hh:nn format.
the XML output does give me that. thx.

 

[June7]

DoCmd.RunCommand acCmdOutputToExcel does save a file and automatically opens. It uses the default database folder set in Access Options.