DoCmd.Transfer电子表格问题

Question

我正在将查询导出到 Access 2013 中的 Excel。 这是我用于导出的语法

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

数据按预期传输，但查询中的一个字段标题为 Player #，导出到 Excel 时，该字段将变为Player .

如何在导出时保持#完好无损？

Answer 1

虽然问题可以通过DoCmd.TransferSpreadsheet重现，但请考虑保留查询格式的DoCmd.OutputTo。前一种方法可能更侧重于扩展格式。

实际上，DoCmd.OutputTo看起来是外部数据\Excel导出功能区方法的自动化版本（选择保留所有格式）：

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

Answer 2

您可以使用以下函数将内容导出到.xlsx文件，而无需处理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

称之为：CustomExcelExport "qryDataExport", strExportPath