Exporting file with a date stamp

[Smudger Smith]

Hi Anyone,
I have built a simple DB that churns data and kicks out a Spreadsheet.
I want the spreadsheet to have a date stamp on it ie All_Emps_01012010.xls
is this possible and if so, how?

Currently i have a macro that does 'TransferSpreadsheet'

Thanks for any help

 

[vbaInet]

Investigate the Date() and Format() functions. Just what you need.

 

[ghudson]

Hi Anyone,
I have built a simple DB that churns data and kicks out a Spreadsheet.
I want the spreadsheet to have a date stamp on it ie All_Emps_01012010.xls
is this possible and if so, how?

Currently i have a macro that does 'TransferSpreadsheet'

Thanks for any help

Did you try searching the forum before posting?

This is how I do it...

    Dim sPath As String
    Dim sFile As String
    Dim sDate As String
    Dim sExtension As String
    Dim sOutputFile As String
    
    sPath = "\\Server\Directory\OutputFiles\"
    sFile = "Filename_"
    sDate = Format(Now(), "yyyymmdd")
    sExtension = ".xls"
    sOutputFile = sPath & sFile & sDate & sExtension

    If Dir(sOutputFile) <> "" Then
        Kill sOutputFile
    End If

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery", sOutputFile, True

 

[Smudger Smith]

I have been searching but not getting very far i'm afraid. I promise ghudson that i always search first! - I am just an ego covered in skin so asking for helps REALLY hurts!