OutputTo help

[iisjman07]

Solved: OutputTo help

Hi, I'm trying to make a little vba script that will export 'Repairs_tbl' to an excel file elsewhere in case the database decides to explode. My working code is featured here:

Private Sub Command0_Click()
BackupDir = InputBox("Enter the directory for the backup to be stored. A name will be automatically generated. Please ensure this location exists befoe continuing.", "Enter Location", "C:\Backups")
DoCmd.OutputTo acOutputTable, "Repairs_tbl", _
    acFormatXLS, BackupDir & "\Repairs_tbl_backup.xls"
End Sub

This works and exports to the excel document fine, however this can obviously only be done once before it starts over writing itself because the file name doesn't change. I attempted to add a date into the file name, however it's not working:

Private Sub Command0_Click()
DateString = Date
BackupDir = InputBox("Enter the directory for the backup to be stored. A name will be automatically generated. Please ensure this location exists befoe continuing.", "Enter Location", "C:\Backups")
DoCmd.OutputTo acOutputTable, "Repairs_tbl", _
    acFormatXLS, BackupDir & "\Repairs_tbl_backup_" & DateString & ".xls"
End Sub

It gives me a message stating 'Run-time error 2303' and 'can't save the output data to the file you've selected'. Can anyone tell me what's wrong with that code?

 

[JANR]

Your variable DateString i passing illegally characters like . or / , try and use format function to pass a string.

DateString = Format(Date, "mmddyyyy")

JR

 

[PeterF]

I asume the datestring is not a string the way jou set the variable, try:

DatesString = format(date(),"yyyy-mm-dd")

 

[iisjman07]

Ah that makes sense, thanks to both of you for the help; it's working now!