TransferSheet with SQL string instead of table

Posted on 2009-12-30
Last Modified: 2013-11-28
I'm using the code below to transfer the contents of a subform to an excel sheet. I'd like to do this without having to create a query. Is it possible to use TransferSheet with a SQL string somehow?

Also, I use Set qdf = nothing to try to get rid of the query I build in this procedure when I'm done with it but when I return to the navigation pane "qryLPMSummaryFiltered" is still there. Is there a way to delete the query without having to loop through all the querys in QueryDef?


Private Sub cmdTransferToExcel_Click()

    Dim dbs As Database

    Dim qdf As QueryDef


    Set dbs = CurrentDb


    For Each qdf In dbs.QueryDefs

        If qdf.Name = "qryLPMSummaryFiltered" Then

            dbs.QueryDefs.Delete "qryLPMSummaryFiltered"

        End If


    Set qdf = dbs.CreateQueryDef("qryLPMSummaryFiltered", Me!subLPMSummary.Form.RecordSource)

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryLPMSummaryFiltered", "C:\Documents and Settings\dpark\My Documents\LPMSummary.xls", True


    Set qdf = Nothing

    Set dbs = Nothing


End Sub

Open in new window

Question by:ltdanp22
    LVL 39

    Accepted Solution

    TransferSpreadsheet requires a stored query or a table. You con create the query, run TransferSpreadsheet then delete the query.
    LVL 119

    Assisted Solution

    by:Rey Obrero

    you can use the following codes to avoid the error when deleting the query, before recreating the query.

    on error resume next
    DoCmd.DeleteObject acQuery, "qryLPMSummaryFiltered"

    or this one

    if dcount("*","msysobjects","[name]='qryLPMSummaryFiltered'")>0 then
      DoCmd.DeleteObject acQuery, "qryLPMSummaryFiltered"
    end if


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now