none
How To Export An Excel Worksheet Using The DoCmd.TransferSpreadsheet

    Question

  • Hello:

    I have used the following type of code many times to export an Excel worksheet from an Access table or query:

     

     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
     "qryExportOTSToExcel", cdlg.GetName, True
    
    
    The issue I am facing is that instead of hard-coding the "qryExportOTSToExcel", I want to use a dynamic SQL Statement or set up a QueryDef.  So far, Access complains that it must be a standard table or query name.  Any ideas?

     


    Rich Locus Logicwurks, LLC www.logicwurks.com
    Wednesday, August 24, 2011 10:14 PM

Answers

  • RichLocus wrote:

     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
     "qryExportOTSToExcel", cdlg.GetName, True
    

    The issue I am facing is that instead of hard-coding the
    "qryExportOTSToExcel", I want to use a dynamic SQL Statement or set up a
    QueryDef.

    TransferSpreadsheet doesn't support SQL statements. You have to create a
    qdf, export to Excel and delete after processing:

    strSQL = "SELECT ... FROM ..."
    strQry = "MyTmpQry"

    Set Db = CurrentDb
    Set Qdf = Db.CreateQueryDef(strQry, strSQL)

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
      strQry, cdlg.GetName, True

    DoCmd.DeleteObject acQuery, strQry


    Peter Doering [MVP Access]

    • Proposed as answer by danishani Wednesday, August 24, 2011 11:24 PM
    • Marked as answer by RichLocus Thursday, August 25, 2011 4:24 PM
    Wednesday, August 24, 2011 11:06 PM
  • I would set up a base query which you can then modify using a querydef object.  You can do a temporary query but I think it is more problematic because you then, I THINK, have to append it to the querydefs to be available for this.

    So, let's say you create the base query in the qbe grid -  Bring in any table and then just in one field type 1 and then just save as qryBase.

     

    Next we can use:

     

    Dim strSQL As String

    strSQL = "Select Field1, Field2, Field3, Field4 From TableNameHere ORDER BY FieldNameHere"

    CurrentDb.QueryDefs("qryBase").SQL = strSQL

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
     "qryBase", cdlg.GetName, True

     


    Bob Larson, Access MVP 2008-2010, 2011
    • Marked as answer by RichLocus Thursday, August 25, 2011 4:02 PM
    Wednesday, August 24, 2011 11:06 PM

All replies

  • I would set up a base query which you can then modify using a querydef object.  You can do a temporary query but I think it is more problematic because you then, I THINK, have to append it to the querydefs to be available for this.

    So, let's say you create the base query in the qbe grid -  Bring in any table and then just in one field type 1 and then just save as qryBase.

     

    Next we can use:

     

    Dim strSQL As String

    strSQL = "Select Field1, Field2, Field3, Field4 From TableNameHere ORDER BY FieldNameHere"

    CurrentDb.QueryDefs("qryBase").SQL = strSQL

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
     "qryBase", cdlg.GetName, True

     


    Bob Larson, Access MVP 2008-2010, 2011
    • Marked as answer by RichLocus Thursday, August 25, 2011 4:02 PM
    Wednesday, August 24, 2011 11:06 PM
  • RichLocus wrote:

     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
     "qryExportOTSToExcel", cdlg.GetName, True
    

    The issue I am facing is that instead of hard-coding the
    "qryExportOTSToExcel", I want to use a dynamic SQL Statement or set up a
    QueryDef.

    TransferSpreadsheet doesn't support SQL statements. You have to create a
    qdf, export to Excel and delete after processing:

    strSQL = "SELECT ... FROM ..."
    strQry = "MyTmpQry"

    Set Db = CurrentDb
    Set Qdf = Db.CreateQueryDef(strQry, strSQL)

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
      strQry, cdlg.GetName, True

    DoCmd.DeleteObject acQuery, strQry


    Peter Doering [MVP Access]

    • Proposed as answer by danishani Wednesday, August 24, 2011 11:24 PM
    • Marked as answer by RichLocus Thursday, August 25, 2011 4:24 PM
    Wednesday, August 24, 2011 11:06 PM