Export query results

[Guest]

i'd like to export the query results in an excel file in a give spreadsheet.
I'd like to have a prompt that asks me where to put the file and what name
to give to it.
Now I'm working with
DoCmd.OutputTo acOutputQuery, "Report_Incasso_Macro_Ramo", acFormatXLS,
"C:\esempio\prova.xls", True

so I don't have a prompt now.

Thanks
Rossella

 

[Douglas J. Steele]

The OutputTo method won't prompt you, but you can use the code in
http://www.mvps.org/access/api/api0001.htm at "The Access Web" to prompt
your user for a file name, and then replace "C:\esempio\prova.xls" in your
sample code below with the name of the variable in which you've stored the
file name they passed you.

 

[Guest]

Here is a link that has code you can use to allow the user to navigate to a
folder to save the spreadsheet:

http://www.mvps.org/access/api/api0001.htm

Here is some sample code on how to use the API call:

***************Code Start
strCurrYear = Me.txtCurrYear
strCurrMonth = Me.cboPeriod.Column(1)
strDefaultDir = "\\rsltx1-bm01\busmgmt\Vought " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
strDefaultFileName = Me.cboPeriod.Column(1) & _
IIf([Forms]![frmsccbrpt]![cboResource] = "SEL", _
" SCCB Report", " " & Me.cboResource & " Performance Report") &
".xls"
'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_OVERWRITEPROMPT
'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
InitialDir:=strDefaultDir, _
Filter:=strfilter, _
filename:=strDefaultFileName, _
Flags:=lngFlags, _
DialogTitle:="Save Report")
If varGetFileName <> "" Then
DoCmd.TransferSpreadsheet acExport, ,"Report_Incasso_Macro_Ramo", _
varGetFileName, True
End If
***********Code End************

I would also suggest you use the TransferSpreadsheet method rather than the
OutputTo method.