Exporting Parameter Query to Excel with VBA

[cbgraham_asu]

This problem seems so silly, I"m sure there is an easy answer:

I have a parameter query set up. The user opens a form and picks the
parameters they want from a drop down box. I want the results to go
directly to a new excel workbook.

I started with:
DoCmd.OpenQuery "Q_Open_Orders_All", acViewNormal, acEdit
DoCmd.RunCommand acCmdOutputToExcel
DoCmd.Close acQuery, "Q_Open_Orders_All"

But it's annoying because it opens the query, pauses, and then opens
the spreadsheet. If I could just hide the query so it would stay in
the background it would be perfect.

Next I tried:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Q_Open_Orders_all", _
"C:\OpenOrders.xls", True

Which is nice b/c it gets the data to Excel w/o showing the user the
query, but I don't want to save the data at any specific location.
I'd rather have the user decide whether or not save and to where.
This would be okay if I just didn't have to specify a file location.

Any ideas????
MANY THANKS!

 

[strive4peace]

if you want the user to be able to specify filename as the export is
being done, you can use this method:

'~~~~~~~~~~~~~~~~~`
DoCmd.OutputTo acOutputQuery, "Q_Open_Orders_All", _
acFormatXLS, , True
'~~~~~~~~~~~~~~~~~`

by not specifying the filename, the user will be prompted for it, then
prompted for the query parameters


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[cbgraham_asu]

Thanks!

That's better than either of the two options I had before. Honestly, I
would prefer that they didn't have to save at all in order to view the
data but this will work and it's much cleaner than what i had come up
with.

I appreciate your help
-Christine

 

[Tom Wickerath]

Try using the OutputTo method instead, without including the optional
OutputFile argument:

Private Sub cmdExportQuery_Click()
On Error GoTo ProcError

DoCmd.OutputTo _
ObjectType:=acOutputQuery, _
ObjectName:="Q_Open_Orders_all", _
OutputFormat:=acFormatXLS, _
Autostart:=True

ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2501 'User clicked on Cancel
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
"Error in cmdExportQuery_Click event procedure..."
End Select
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[strive4peace]

you're welcome, Christine

the only other method I can think of is copy and paste -- but I try to
avoid using that!

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

Hi Christine,

Tom's code would actually be better to use than what I gave you -- it
does the same thing, but he has error checking...and, not that is makes
a difference in functionality, he has also labeled his parameters --
this helps to understand the code better



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Rony]

Hi Tom
I need to save the data automatically to a specific folder e.g
C:\Testfile\filename
please help

 

[strive4peace]

output file to XLS, first delete name if there
---


Hi Rony,

DoCmd.OutputTo _
ObjectType:=acOutputQuery, _
ObjectName:="Q_Open_Orders_all", _
OutputFormat:=acFormatXLS, _
OutputFile: = "C:\Testfile\filename.xls", _
Autostart:=True
~~~~~

if you will be overwriting the file each time, if a file with that name
is already there, delete it:

if Len(Dir("C:\Testfile\filename.xls")) > 0 then
Kill "C:\Testfile\filename.xls"
DoEvents
End if


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Rony]

Thanks a lot
--
Ron

output file to XLS, first delete name if there
---


Hi Rony,

DoCmd.OutputTo _
ObjectType:=acOutputQuery, _
ObjectName:="Q_Open_Orders_all", _
OutputFormat:=acFormatXLS, _
OutputFile: = "C:\Testfile\filename.xls", _
Autostart:=True
~~~~~

if you will be overwriting the file each time, if a file with that name
is already there, delete it:

if Len(Dir("C:\Testfile\filename.xls")) > 0 then
Kill "C:\Testfile\filename.xls"
DoEvents
End if


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

you're welcome, Rony happy to help


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*