Access: Export a query as an Excel file with a save dialog

[emik]

I'm trying to save a query as an Excel file. I know how to export it but I would like to use a SaveAs dialog box so the user can decide where to save it instead of it being hardcoded.

I could put a text field on the form where the write the path, but I'd prefer the save box. Thanks.

Right now I'm doing this:

choice = MsgBox("Would you like to export this report to an Excel file?", vbYesNo + vbQuestion, "Export")
If choice = 6 Then
Dim date_nb As String
date_nb = Format(Date, "yyyy-mm-dd")

'Output the query 'qry_BU_Reporting' to an Excel file on the C: drive
Subject = "qry_BU_Reporting"
FileName = "C:\BU_Report " & date_nb & ".xls"

DoCmd.OutputTo acOutputQuery, Subject, acFormatXLS, FileName, False

MsgBox "Data has been exported to " & FileName & ""
End If

This works and saves it to the posted folder.

 

[jebry]

Hi!

Try this FAQ:

faq222-3917

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst