DoCmd.TransferSpreadsheet Issue

Question

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?

Is the field name enclosed in square brackets within the query object you are exporting? — MoondogsMaDawg, Nov 06 '17 at 14:50
@ChristopherD.- yes it is a short text type in the table and in the query it is listed as [Player #] — BellHopByDayAmetuerCoderByNigh, Nov 06 '17 at 14:51
Try post # 11 found here https://windowssecrets.com/forums/showthread.php/130867-csv-Export-not-keeping-number-sign-on-field-name by AndrewKKWalker. I believe the issue is you are using a reserved symbol in the export https://support.office.com/en-us/article/Access-2007-reserved-words-and-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe?CorrelationId=1e80e7bd-52b9-4d39-84cb-883f5fabf7a9&ui=en-US&rs=en-US&ad=US&ocmsassetID=HA010030643 — Bob Goblin, Nov 06 '17 at 15:05
@user2676140 while your analysis of the problem is spot-on, that solution isn't (it creates a CSV file, not an xslx file). If you want to export to an .xslx in this way, you need to create a custom export function. — Erik A, Nov 06 '17 at 15:16
@ErikvonAsmuth - so I would need to use a custom export function to take the query to excel? Would a recordset work or since that is not a "custom" function that would give me the same output? — BellHopByDayAmetuerCoderByNigh, Nov 06 '17 at 15:20
Yes, you can create a custom export function using recordsets. Give me a sec and I might even write one for you. — Erik A, Nov 06 '17 at 15:21

score 2 · Answer 1 · answered Nov 06 '17 at 19:52

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

Erik A · Accepted Answer · 2017-11-06T17:20:15.607

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

edited Nov 06 '17 at 17:20

answered Nov 06 '17 at 15:36

Erik A

31,639
12
42
67

I just moved the syntax over to my production database and it is hitting the line Set rs = CurrentDb.OpenRecordset(QueryOrTableOrSQL) then leaving the procedure? So no export is actually occuring. - and it is Excel 2013 if that makes a diff. – BellHopByDayAmetuerCoderByNigh Nov 06 '17 at 16:44
Is your query actually returning rows? That would happen if your query returned 0 rows and 0 fields. – Erik A Nov 06 '17 at 16:58
It is returning rows - but it does seem one field has error ed bc it is asking for me to input a value. Could that be the culprit? – BellHopByDayAmetuerCoderByNigh Nov 06 '17 at 17:00
Yes, this doesn't work on parameterized queries (nor does `DoCmd.TransferSpreadsheet` for that matter). You can adapt it to use a `QueryDef` and parameters, and use a `ParamArray` to send the parameters to the query, or you can just fix your query. – Erik A Nov 06 '17 at 17:02
Yeah - I removed those fields and the query is executing as it should, now the procedure above gives me Run-time error '13': Type Mismatch – BellHopByDayAmetuerCoderByNigh Nov 06 '17 at 17:03
There is only 1 row returned in the query that is throwing the error. If I run the query manually it reutns data as expected – BellHopByDayAmetuerCoderByNigh Nov 06 '17 at 17:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/158341/discussion-between-erik-von-asmuth-and-bellhopbydayametuercoderbynigh). – Erik A Nov 06 '17 at 17:06

DoCmd.TransferSpreadsheet Issue

2 Answers2

Linked