OutputTo using dynamic filenames (date)

[benchpolo]

I wrote a funtion in VBA OutputTo an excel file using dynamic filename.

Table: contact
Filename format: contact_10292007.xls
Code:

Function outfile()
    Dim stbl As String
    stbl = "Contact"
    DoCmd.OutputTo acOutputTable, stbl, acFormatXLS, "c:\temp\contact_" & Now() & ".xls"
End Function
 

I am calling this function from a Macro RUNCODE, but when I execute the macro I am getting the error below,

"ScorTab can't save the output data to a file you've selected".

Note: ScorTab is the db name.

[Rabbit]

This is what Now() returns:
10/29/2007 10:34:28 AM
That contains characters you can't use in a file name.

This is what you want returned:
10292007

So this is what you use:

Format(Now(), "mmddyyyy")
 

However, this doesn't work well for keeping files in order.
So you might want to use:

Format(Now(), "yyyymmdd")
 

[benchpolo]

After doing trial & errors on the module code here it what I came out with

Function outfile()
    Dim stbl As String
    stbl = "Contact"
    DoCmd.OutputTo acOutputTable, stbl, acFormatXLS, "c:\temp\contact_" & Format(Now(), "yyyymmdd") & "_" & Format(Time(), "hhmmss") & ".xls"
End Function
 

Output: contact_20071029_102322.xls

Where the filename is "contact_" and the date is "20071029_" and time is "102322.xls".

Thanks for your help.

[Rabbit]

Not a problem. Please use Code tags in the future.