DoCmd.TransferSpreadsheet Issue

问题:

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

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

The data transfers as it should, but one of the fields in the query is titled Player # and when exported to Excel this becomes Player .

How can I keep the # intact with the export?

回答1:

You can use the following function to export stuff to an .xlsx file, without having to deal with the limitations of 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

Call it: CustomExcelExport "qryDataExport", strExportPath

回答2:

While issue is reproducible with DoCmd.TransferSpreadsheet, consider DoCmd.OutputTo which retains formatting of query. The former method may focus more on spreadseet formatting.

And in fact, DoCmd.OutputTo looks to be the automated version of the External Data \ Excel Export ribbon method (selecting to keep all formatting):

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