Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Output query to Excel using query in VB instead of actually query

Posted on 2008-06-12
6
Medium Priority
?
353 Views
Last Modified: 2008-06-13
I have the following code:

Dim sSQL As String

sSQL = "SELECT tblROP_ByWeek.* FROM tblROP_ByWeek WHERE (((tblROP_ByWeek.MonDate)=Forms!frmROP!StartDate)) ORDER BY tblROP_ByWeek.ClientName, tblROP_ByWeek.NewspaperName; "

DoCmd.OutputTo acOutputQuery, "sSQL", acFormatXLS, "C:\WINDOWS\Temp\ROPWeeklyData.xls", True

I want to output this query to Excel.  When I run it, I get error - can't find 'sSQL'.  What do I have to chage to get this to work?
0
Comment
Question by:Travidia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 21773992
DoCmd.OutputTo acOutputQuery, sSQL, acFormatXLS, "C:\WINDOWS\Temp\ROPWeeklyData.xls", True
0
 
LVL 28

Expert Comment

by:omgang
ID: 21773998
sSQL is a variable.  You don't wrap variables in quotes.
OM Gang
0
 

Author Comment

by:Travidia
ID: 21775100
I tried taking the quotes off.  Still get error - can't find the object "Select tblROP_ByWeek...   Make sure the obkect exists...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 28

Expert Comment

by:omgang
ID: 21779048
From VBA help
The OutputTo method carries out the OutputTo action in Visual Basic.

expression.OutputTo(ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, TemplateFile, Encoding)
expression    Required. An expression that returns a DoCmd object.

ObjectType   Required AcOutputObjectType. The type of object to output.

AcOutputObjectType can be one of these AcOutputObjectType constants.
acOutputDataAccessPage Not supported.
acOutputForm
acOutputFunction
acOutputModule
acOutputQuery
acOutputReport
acOutputServerView
acOutputStoredProcedure
acOutputTable

ObjectName   Optional Variant. A string expression that's the valid name of an object of the type selected by the ObjectType argument. If you want to output the active object, specify the object's type for the ObjectType argument and leave this argument blank. If you run Visual Basic code containing the OutputTo method in a library database, Microsoft Access looks for the object with this name, first in the library database, then in the current database.

OutputFormat   Optional Variant. The output format, expressed as an AcFormat constant. If you omit this argument, Microsoft Access prompts you for the output format.

AcFormat can be one of these AcFormat constants.
acFormatASP
acFormatDAP
acFormatHTML
acFormatIIS
acFormatRTF
acFormatSNP
acFormatTXT
acFormatXLS

OutputFile   Optional Variant. A string expression that's the full name, including the path, of the file you want to output the object to. If you leave this argument blank, Microsoft Access prompts you for an output file name.

AutoStart   Optional Variant. Use True (1) to start the appropriate Microsoft Windowsbased application immediately, with the file specified by the OutputFile    argument loaded. Use False (0) if you don't want to start the application. This argument is ignored for Microsoft Internet Information Server (.htx, .idc) files and Microsoft ActiveX Server (*.asp) files. If you leave this argument blank, the default (False) is assumed.

TemplateFile   Optional Variant. A string expression that's the full name, including the path, of the file you want to use as a template for an HTML, HTX, or ASP file.

Encoding   Optional Variant.


The OutputTo action requires a database object, i.e. you can't use an SQL statement explicitly.  You could create a QueryDef object, assign the SQL statement to the QueryDef object and then use that object in the Output to action.  I'll work up an example.
OM Gang
0
 
LVL 28

Accepted Solution

by:
omgang earned 1000 total points
ID: 21779160
OM Gang
Public Function OutputToExcel()
On Error GoTo Err_OutputToExcel
 
    Dim qdf As DAO.QueryDef
    Dim sSQL As String, strQueryName As String
    Dim strDestFileName As String
    
    sSQL = "SELECT tblROP_ByWeek.* FROM tblROP_ByWeek WHERE (((tblROP_ByWeek.MonDate)=Forms!frmROP!StartDate)) ORDER BY tblROP_ByWeek.ClientName, tblROP_ByWeek.NewspaperName; "
    
    strQueryName = "qryOutput"
    
    strDestFileName = "c:\temp\MyOutputFile.xls"
    
    Set qdf = CurrentDb.QueryDefs(strQueryName)
    qdf.SQL = sSQL
    
    DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLS, strDestFileName
    
Exit_OutputToExcel:
    Set qdf = Nothing
    Exit Function
    
Err_OutputToExcel:
    MsgBox Err.Number & ", " & Err.Description, , "Error"
    Resume Exit_OutputToExcel
 
End Function

Open in new window

0
 
LVL 28

Expert Comment

by:omgang
ID: 21779183
Forgot to add - you need to create a query object named qryOutput.  It doesn't matter what the query is because you are going to reassign the SQL statement in the function.  It just needs to exist so the function can utilize it for your OutputTo action.
OM Gang
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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