Error DoCmd.TransferSpreadsheet

Question

I am exporting a query to Excel in Access 2013. This is the syntax that I use to export

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryDataExport", strExportPath, True

The data is transferred as it should, but one of the fields in the request is called Player #, and when exporting to Excel it becomesPlayer .

How can I keep #intact with export?

Answer 1

You can use the following function to export material to a .xlsx file without having to deal with restrictions DoCmd.TransferSpreadsheet

Public Sub CustomExcelExport(QueryOrTableOrSQL As String, FileLocation As String)
    Dim rs As DAO.Recordset
    Dim excelApp As Object
    Set excelApp = CreateObject("Excel.Application")
    Set rs = CurrentDb.OpenRecordset(QueryOrTableOrSQL)
    excelApp.Workbooks.Add
    Dim colNo As Long: colNo = 1
    Dim rowNo As Long: rowNo = 1
    Dim fld As Variant

    For Each fld In rs.Fields
        excelApp.Cells(rowNo, colNo) = fld.Name
        colNo = colNo + 1
    Next fld
    Do While Not rs.EOF
       colNo = 1
       rowNo = rowNo + 1
       For Each fld In rs.Fields
            excelApp.Cells(rowNo, colNo) = fld.Value
            colNo = colNo + 1
       Next fld
       rs.MoveNext
    Loop
    excelApp.ActiveWorkbook.SaveAs FileLocation, 51 'xlOpenXMLWorkbook
    excelApp.Quit
End Sub

Name it: CustomExcelExport "qryDataExport", strExportPath

Answer 2

DoCmd.TransferSpreadsheet, DoCmd.OutputTo, . .

DoCmd.OutputTo External Data\Excel Export ( ):

DoCmd.OutputTo acOutputQuery, "qryDataExport", acFormatXLSX, strExportPath