Maybe this will help you:
Code:
Private Sub ctlGenFormARE_Click()
'Requires a reference set to Microsoft DAO 3.6 Object Library
Dim r As DAO.Recordset
Dim sSQL As String
Dim FileName As String
Dim FilePath As String
'create file name
FileName = "ARE_FORM_" & Format(Date, "mmddyy") & ".xlsx"
sSQL = "SELECT NetworkFolder_Path FROM tbl_PathDirectoryInfo"
sSQL = sSQL & " WHERE [ObjectType] = 'Form' AND [ObjectStatus] = 'Testing';"
Set r = CurrentDb.OpenRecordset(sSQL)
If Not r.BOF And Not r.EOF Then
'get path
FilePath = r!NetworkFolder_Path
DoCmd.SetWarnings False
'DoCmd.OutputTo acOutputQuery, "qry_Form_ARE", "ExcelWorkbook(*.xlsx)", "z:\2012_testing\Forms\ARE_FORM_MMDDYY.xlsx", False, "", , acExportQualityPrint
DoCmd.OutputTo acOutputQuery, "qry_Form_ARE", "ExcelWorkbook(*.xlsx)", FilePath & "\" & FileName, False, "", , acExportQualityPrint
MsgBox "Creation of Form for Bus Unit ""ARE"" has been completed.", vbInformation, "Export Complete"
DoCmd.SetWarnings True
Else
'no records in recordset
MsgBox "Error: Path not found" & vbNewLine & vbNewLine & "Action: Aborting!!!" & vbNewLine & vbNewLine & "What to do: Contact someone "
End If
'clean up
r.Close
Set r = Nothing
End Sub