DoCmd.TransferSpreadsheet问题 [英] DoCmd.TransferSpreadsheet Issue

问题描述

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

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

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

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?

推荐答案

您可以使用以下函数将内容导出到.xlsx文件，而不必处理DoCmd.TransferSpreadsheet

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

调用它:CustomExcelExport "qryDataExport", strExportPath

这篇关于DoCmd.TransferSpreadsheet问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！