Export with todays date as filename

[clive2002]

I want to export several forms into seperate sheets in the same Excel spreadsheet.

I want the file to be saved in the same location but with the current time and date as the file name.

I cant work out how to do the last bit, but as always im convinced its possible.

Any ideas

 

[ghudson]

Here is a sub I use to export a query to a specific location and the file name is the current
date and time merged and it looks like this "10102002221558.xls" (i.e. 10/10/2002 22:15:58.xls).
Since you said you were only having a problem with the last part, my sub should show you how
to save the Excel file with a name consisting of the date and time.

Private Sub bExportCurrentRecord_Click()
On Error GoTo Err_bExportCurrentRecord_Click
    
    Dim sRecordID As String
    Dim sLocation As String
    Dim sFileName As String
    
    sRecordID = Me.tbPositionID.Value
    sLocation = "C:\"
    sFileName = Format(Now(), "mmddyyyyhhmmss") & ".xls"
'    MsgBox sFileName 'used for testing
    If Dir(sLocation & sFileName) <> "" Then
        'MsgBox "The " & sLocation & sFileName & "already exists!"
        Kill sLocation & sFileName
    Else
        'MsgBox "file does not exists"
    End If
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qCurrentDataRecord", sLocation & sFileName, True, ""
    Beep
    MsgBox "The current Position ID " & sRecordID & " and all related data was exported to your computer." & vbCrLf & vbLf & _
            "The name of the file is '" & sFileName & "' and the file is located in the root of your C:\ drive.", vbInformation, "Exported >>> " & sLocation & sFileName
    
Exit_bExportCurrentRecord_Click:
    Exit Sub
    
Err_bExportCurrentRecord_Click:
    If Err = 75 Or Err = 3010 Then
        Beep
        MsgBox "The '" & sFileName & "'file is open." & vbCrLf & vbLf & "Please close the '" & sFileName & "' file before trying to export the data for current Position ID " & sRecordID & ".", vbCritical, "Export Error >>> " & sLocation & sFileName
        Exit Sub
    Else
        MsgBox Err.Number, Err.Description
        Resume Exit_bExportCurrentRecord_Click
    End If
        
End Sub

HTH

 

[underc]

Hello ghudson

Can you do that but instead of the date filename i need a field value from my query as filename.

Could you hlep me?

Thanks