Exporting Data to Excel

M

Matt

I am looking for code that will allow the user to export a query to Excel,
however first receive a Save As prompt. The DoCmd.TransferSpreadsheet
acExport code requires a file name to export to. Is there code that will
export without having to specify a file name in the code, and also prompt the
user with Save As?

Thanks,
Matt
 
K

Ken Snell [MVP]

A real simple way would be to let the user enter a filename using the
InputBox function as part of the expression that provides the path and
filename to the TransferSpreadsheet's argument.

If you want to allow the user to see a Windows navigation browse window,
that's much more complicated.
 
T

Tom Wickerath

Hi Matt,

As an alternative to TransferSpreadsheet, you can use the OutputTo method, without specifying the
optional OutputFile parameter:

DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="qryFiles", _
OutputFormat:=acFormatXLS, AutoStart:=False

Note: The ObjectType parameter must match a valid ObjectName. Use acOutputTable if you wish to
specify a table name as the ObjectName.

Tom
________________________________


I am looking for code that will allow the user to export a query to Excel,
however first receive a Save As prompt. The DoCmd.TransferSpreadsheet
acExport code requires a file name to export to. Is there code that will
export without having to specify a file name in the code, and also prompt the
user with Save As?

Thanks,
Matt
 
M

Matt

Tom,

Your suggestion works great. The only issue is if a I click Cancel in the
Output to prompt, I will get an error message stating Output to has been
canceled. I am currently using On Error Resume Next code before the docmd.
I try to avoid this code as much as I can, is there anothe method to use?

Thanks a lot!
Matt
 
T

Tom Wickerath

Hi Matt,

Sure, all you need to do is trap for error 2501, and do nothing as a response. Notice that there
is no message box statement in the error handler for Case 2501:

Option Compare Database
Option Explicit

Sub ExportToExcel()
On Error GoTo ProcError

DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="qryFiles", _
OutputFormat:=acFormatXLS, AutoStart:=False

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 procedure ExportToExcel..."
End Select
Resume ExitProc
End Sub

________________________________


Tom,

Your suggestion works great. The only issue is if a I click Cancel in the
Output to prompt, I will get an error message stating Output to has been
canceled. I am currently using On Error Resume Next code before the docmd.
I try to avoid this code as much as I can, is there anothe method to use?

Thanks a lot!
Matt
 
T

Tom Wickerath

2nd attempt to post reply (a reply posted over 30 minutes ago is still not showing up in my
newsreader)...


Hi Matt,

Sure, all you need to do is trap for error 2501, and do nothing as a response. Notice that there
is no message box statement in the error handler for Case 2501:

Option Compare Database
Option Explicit

Sub ExportToExcel()
On Error GoTo ProcError

DoCmd.OutputTo ObjectType:=acOutputQuery, ObjectName:="qryFiles", _
OutputFormat:=acFormatXLS, AutoStart:=False

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 procedure ExportToExcel..."
End Select
Resume ExitProc
End Sub

________________________________


Tom,

Your suggestion works great. The only issue is if a I click Cancel in the
Output to prompt, I will get an error message stating Output to has been
canceled. I am currently using On Error Resume Next code before the docmd.
I try to avoid this code as much as I can, is there anothe method to use?

Thanks a lot!
Matt
 
J

Jeff C

I am trying to find an alternative to TransferSpreadsheet that will allow me
to format the Excel Data before it drops the file so that I can then use
SendObject to email the Formatted Excel Workbook.

Does this OutputTo method result in an Excel Object and how would I
reference that in VBA?

This is same issue I opened a thread on earlier this morning but am trying
to resolve with research.

Thanks in advance.
 
A

Arvin Meyer [MVP]

OutputTo is what happens in code when you use the menu object:

Tools >>> OfficeLinks >>> Analyze with Excel

DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, "C:\PathToFile",
True

The last argument (True) will open the resulting Excel file, from which you
can then do a File Send and get the Excel equivalent of SendObject.
 
J

Jeff C

Thanks Arvin: I am looping though a large dataset and am hoping to come up
with a way to format an Excel object before saving it as a file so I can
attach it as a variable in the SendObject method
--
Jeff C
Live Well .. Be Happy In All You Do


Arvin Meyer said:
OutputTo is what happens in code when you use the menu object:

Tools >>> OfficeLinks >>> Analyze with Excel

DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, "C:\PathToFile",
True

The last argument (True) will open the resulting Excel file, from which you
can then do a File Send and get the Excel equivalent of SendObject.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
 
Top