export query into excel

[Marc]

On the click action of a button I would like to give the
opportunity to the user to export the data from a query
directly into Excel. When the user clicks on it, a menu
might ask him (1) where to locate the excel file and (2)
what name to give to it.

Could anyone help me with the code that will prompt the
user to enter the file name and its destination, and then
will export it?

Thank you,

Marc

 

[tina]

since nobody else has answered you yet, i can tell you that the following
code will work, at least in Access 2000 (my guess is it will work in
anything at least A97 and newer):

Dim strLoc As String

strLoc = InputBox("Enter the filepath and filename." & vbCr _
& vbCr & "Example: C:\MyFolder\MyFile.xls", "Export What to
Where?")

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"QueryName", strLoc
MsgBox "REQUEST PROCESSED"

one problem with this code is that even if the user enters an invalid
filepath, you may not get an error code (i didn't) and the msgbox will
display - but of course no export was done.
you could warn your users to verify the existence of the .xls file in
Windows Explorer, but that's a pretty limp solution. hopefully someone (MVPs
help!) will post a better solution - then you and i can both learn!