Autosave File With Today's Date 1

[misssaraliz]

I already have a macro set up that will automatically export a report and save it in Excel. My problem is in trying to save the file with the date the export was run (mmddyy) listed in the filename. Here is an example of what my filename needs to look like (using today 2/20/02 as an example):

c:\my documents\Legislation 022002.xls

The following syntax works fine in VB:

"C:\My Documents\Legislation " & Month(Date) & Day(Date) & Year(Date) & ".xls"

But this same syntax doesn't work with the macro object in Access.

Any ideas???

 

[Phi1Smith]

Drop this Function into a module and call the function from your existing macro or instead of the macro.

Function OutputFile()
On Error GoTo OutputFile_Err
Dim MyFile As String
' Don't forget to change folder location next line
MyFile = "C:\Folder Location\" & Format(Date, "mmddyy&quot & ".xls"

DoCmd.OutputTo acTable, "Department", "MicrosoftExcel(*.xls)", MyFile, False, ""


OutputFile_Exit:
Exit Function

OutputFile_Err:
MsgBox Error$
Resume OutputFile_Exit

End Function

Good Luck

Phil Smith