Try to export queries to excel worksheets; Error '3191' cannot define field more than once.

[JC-Access]

I am trying to export multiple access queries to a single excel workbook as individual worksheets. It creates the workbook and sheet, runs the query, but isn't placing the data into the worksheet.

Any help is greatly appreciated.



When I run the module it errors out on this line, with the error "run-time error: 3191" Cannot define field more than once.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "mySheet1", xlsxPath, True

Sub ExportToXlsx()
    Dim cdb As DAO.Database, qdf As DAO.QueryDef
    Set cdb = CurrentDb

    Const xlsxPath = "d:\foo.xlsx"

    ' create .xlsx file if it doesn't already exist, and add the first worksheet
    Set qdf = cdb.CreateQueryDef("mySheet1", _
            "SELECT [Backup Properties].*, [Backup Properties].Application FROM [Backup Properties]WHERE (([Backup Properties].Application)='Remedy CSD Servers')")
    Set qdf = Nothing
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "mySheet1", xlsxPath, True
    DoCmd.DeleteObject acQuery, "mySheet1"

    ' file exists now, so this will add a second worksheet to the file
    Set qdf = cdb.CreateQueryDef("mySheet2", _
            "SELECT * FROM Clients WHERE ID Between 6 And 10")
    Set qdf = Nothing
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "mySheet2", xlsxPath, True
    DoCmd.DeleteObject acQuery, "mySheet2"

    Set cdb = Nothing
End Sub

[ranman256]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qsQuery", "c:\file.xls", True, "sheet1"

[JC-Access]

Thank you for your reply.

I replaced that line and it isn't working. Does that line replace a larger section of the script?

[ranman256]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qsQuery1", "c:\file.xls", True, "sheet1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qsQuery2", "c:\file.xls", True, "sheet2"

you dont need
DoCmd.DeleteObject acQuery, "mySheet2"

if it still doesnt work, then you are missing something, either a query mySheet1, or change the export file type to :acSpreadsheetTypeExcel12

[CJ_London]

your issue is here

....SELECT [Backup Properties].*, [Backup Properties].Application FROM...

Your error is Error '3191' cannot define field more than once - and you defining it twice, once in the .* and once specifically. Remove the bit highlighted in red