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

x
?
Solved

TransferSheet with SQL string instead of table

Posted on 2009-12-30
2
Medium Priority
?
174 Views
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?

Thanks!

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
    Next

    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

0
Comment
Question by:ltdanp22
2 Comments
 
LVL 39

Accepted Solution

by:
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.
0
 
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

0

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