Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing a query to the TransferSpreadsheet method (A2k-SR1)

    Hi,

    I am trying to export the results of a query to Excel using the TransferSpreadsheet method. It works fine if I use the following (triggered by clicking a command button on my form) :

    <font color=red>DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryFactoredAssays", "C:Assay Report.xls"</font color=red>

    What I actually want is a subset of the results of the <font color=red>qryFactoredAssays</font color=red> query, so I tried :

    <font color=red>strSQL = "SELECT * from ""qryFactoredAssays"" WHERE [AssaySetID] = " & Me!AssaySetID & ";"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strSQL, "C:Assay Report.xls"</font color=red>

    I only want the records that correspond to the current </font color=red>AssaySetID</font color=red>. I think I am almost there, but I have not got the {"}'s right. The error message I get is attached.

    TIA,
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing a query to the TransferSpreadsheet method (A2k-SR1)

    In Access 97, TransferSpreadsheet can't use a SQL statement, only a saved table or query. I suspect it is still the same in Access 2000.

    What you can do is
    1. <LI>Save a query with strSQL as SQL statement.. Pick an unlikely name.
      <LI>Call DoCmd.Transferspreadsheet with the saved query name.
      <LI>Delete the temporary query.
    In Access 97, I would use the DAO statement
    CurrentDb.CreateQueryDef("Unlikelyname", strSQL) to create the query, and
    CurrentDb.QueryDefs.Delete "Unlikelyname" to get rid of it.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing a query to the TransferSpreadsheet method (A2k-SR1)

    Thanks Hans, that looks like it will work. Now I have a problem with the syntax of the strSQL statement, because the error message says :

    <font color=448800>Syntax error in query. Incomplete query clause.</font color=448800>

    When in debug mode, the strSQL variable contains :

    <font color=red>SELECT * from "qryFactoredAssays" WHERE [AssaySetID] = 1;</font color=red>

    To my inexperienced eye it looks OK <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Now what have I got wrong ?

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing a query to the TransferSpreadsheet method (A2k-SR1)

    Ah - I worked it out - I mustn't have the quotes around the <font color=red>qryFactoredAssays</font color=red> and it works perfectly.

    Thanks Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •