Autosave File With Today's Date

[misssaraliz]

(This is a cross-post)

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???

 

[jebry]

Hi!

Have your tried "Legislation " & Format(Date, "mmddyy&quot & ".xls"?

hth
Jeff Bridgham
bridgham@purdue.edu

 

[misssaraliz]

Thanks for the suggestion, but it did not work. Now I'm getting the error that the file already exists and I know that it doesn't. Anything else I could try?

 

[acron]

Try Have your tried "C:\My Documents\Legislation " & Format(Now, "mmddyy&quot & ".xls"? .

If you will be asving more than one copy, you could include the time with Format(Now, "mmddyy hhmm&quot

AC

 

[dsi]

To avoid duplicate names, this will append a character to the end of the name. So, if you do this 5 times today, you should get these files:

Legeslation 022002.xls
Legeslation 022002a.xls
Legeslation 022002b.xls
Legeslation 022002c.xls
Legeslation 022002d.xls

Dim sBaseName As String
Dim sFile As String
Dim i As Integer

sBaseName = "C:\My Documents\Legislation " Format(Now, "mmddyy") 

sFile = sBaseName & ".xls"
i = 0
Do While Dir(sFile) <> &quot;&quot;  'File Exists
    sFile = sBaseName & Chr(97 + i) & &quot;.xls&quot;
    i = i +1
Loop

'Save with name in sFile

Hope this helps...