Automate excel file export with current date

[Clandon]

I have just finished a quick macro to automate a daily report. I have read about all the Transfer Spreadsheet horrors and have run into a similar problem. I think I'll have to do a DoCmd.Transfer spreadsheet, but I'm not at all familiar with VB.

Here's the macro
transfertext
transfertext
setwarnings
openquery
setwarnings
close
Setwarnings
Openquery
TransferSpreadhseet
close

I am exporting to a network folder in excel very successfully. File name "admissions.xls"

What I'd like to do is add the current date to the file name each day and have it replace the exhisting file. I have tried using (Path name).......\="admissions"&DateValue(Now()).xls and other formula variations without success.
Is there a simple way to do this?

Thanks in advance for any help.

 

[luceze]

I like using this code. It creates the C:\ExcelDocs folder and exports the data to this folder with a date time stamp in the file name. Just replace "YourQueryOrTable" with the name of one of your queries or tables.

Private Sub ExportData_click()

On Error Resume Next
Set fso1 = CreateObject("Scripting.FileSystemObject&quot
fso1.CreateFolder ("C:\ExcelDocs\&quot
FileNameX = "Export" & Month(Now()) & "-" & Day(Now()) & "-" & Year(Now()) & "_" & Hour(Now()) & "_" & Minute(Now()) & "_" & Second(Now())

FileX = "C:\ExcelDocs\" & FileNameX & ".xls"
FileName = "C:\ExcelDocs\" & FileNameX & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YourQueryOrTable", FileX

Dim stAppName As String

stAppName = "C:\Program Files\Microsoft Office\Office10\excel.exe " & FileName
Call Shell(stAppName, 1)
End Sub

HTH
Eric

 

[Clandon]

Eric,

Thanks so much for the code. Just tried it and it worked like a charm!

Thanks again!

Cheryl

 

[Clandon]

Eric,

Thanks so much for the code. Just tried it and it worked like a charm!

Thanks again!

Cheryl