export (dynamic file names)

[eggwater]

hi

does anyone know if it's possible to change an export file name automatically....

to fill you in with my export function this is the code I'm using:

Private Sub Command90_click() 'export new records to SAP function
Dim rs As New ADODB.Recordset
Dim sql As String

On Error GoTo SAP_Export1_Err
DoCmd.RunCommand acCmdRefresh

sql = "select * from select_updates_qry_1"
rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
If MsgBox("You are about to export " & rs.recordcount & " Record(s)." & vbCrLf & "This will update the current data base." & vbCrLf & "Are you sure you wish to continue?", vbYesNo) = vbYes Then
DoCmd.TransferText acExportDelim, "Accom_sap_export_spec", "select_updates_qry_1", "G:\NewDatabaseTest\DataTables\Accom_New_sap.csv", False, ""
CurrentProject.Connection.execute "Update_export_field_qry_1"

End If
Else
'no records to export
End If
rs.Close
Set rs = Nothing

SAP_Export1_Exit:
Exit Sub
SAP_Export1_Err:
MsgBox Error$
Resume SAP_Export1_Exit
End Sub

as you can see the filename will be the same everytime and therefore overwrites the previous export file (this is okay if the export file is picked up quickly and imported away) - if i could add a date/time automatically to the file name this would be very useful...

any ideas?

 

[Fizzio]

To add the Date & time to the filename.

DoCmd.TransferText acExportDelim, "Accom_sap_export_spec", "select_updates_qry_1", "G:\NewDatabaseTest\DataTables\Accom_New_sap_" & Format(Now(),"ddmmyyhhnn")& ".csv", False, ""

 

[eggwater]

that looks the business

thanks - will check ASAP

I knew it was possible but there's never ny documentation on that sorta thing