Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


TransferSheet with SQL string instead of table

Posted on 2009-12-30
Medium Priority
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

thenelson earned 1000 total points
ID: 26146274
TransferSpreadsheet requires a stored query or a table. You con create the query, run TransferSpreadsheet then delete the query.
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 26146690

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


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

578 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