Exporting Query to Excel using VBA

Before you perform the export operation you have to follow simple steps to produce the right output you want:

1. IDENTIFY Source Query.
The Query is called : ExpenseSummarySelectQ
We will assign a variable called: stDocName for ExpenseSummarySelectQ

2. IDENTIFY Output File and Directory.
The output Excel file will be called: ExpenseSummarySelect 04-11-05.xls
The directory this file will be exported is: \\zwnwb080\Expense
3. SET VARIABLES and their types.
"ExpenseSummarySelectQ" = stDocName type String
"ExpenseSummary" = FileName type String
"04-11-05" = tDate As String
"\\zwnwb080\Expense\" = FileDir As String

To combine the filename and Directory like this:
\\zwnwb080\Expense\ExpenseSummary 04-11-05.xls
You need to combine variables too:
XFile = FileDir & FileName & tDate & " .xls"

6. Compose your Procedure.
Private Sub CMDRunExpSummarySelectQ_Click()
Dim stDocName As String
Dim FileName As String
Dim XFile As String
Dim tDate As String
Dim FileDir As String

stDocName = "ExpenseSummarySelectQ"
FileName = "ExpenseSummary"
tDate = Format(Now(), "mm-dd-yy")
FileDir = "\\zwnwb080\"
xFile = FileDir & FileName & tDate & " .xls"

DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.OutputTo acOutputQuery, "ExpenseSummarySelectQ", _
acFormatXLS, xFile, False
DoCmd.Close acQuery, "ExpenseSummarySelectQ", acSaveNo
MsgBox (xFile & " in " & FileDir & " had been created. Excel Report will now be created.")

End Sub