Export File with Date/Time in Name

[Lrn2Code]

Hello Everyone,

Have been searching the site trying to find an answer to my problem and have tried some of the things I've found but so far - no luck.

I've got files in an Access database that are to be saved into a folder in a directory and each file needs to have the date/time of import on them. There are 6 different files with different names. I'll paste the code I'm trying to use below.

The line where I get a data type mismatch error is in red. It blows up on the first file.

'create district folder
strDISTFldr = strSUFldr & "\" & strID

MkDir strDISTFldr

'copy tables to district folder
Dim sql As String
Dim rs As DAO.Recordset
Dim strDate As Date
Dim db As DAO.Database

Dim destinationfolder As String

destinationfolder = "C:\Educator\edcensus\FY09\BackendData\RawData" & strID & ""

sourcefile = "C:\EdCensus09Export\"

strDate = Str(Now())

Dim strCurrFile As Variant

' strCurrFile = strCurrFile & strDate
' strCurrFile = strCurrFile & "_" & ".xls"
' Debug.Print (strCurrFile)

slowdown

'copy tables to district folder

strCurrFile = strDISTFldr & "\exp_tblposrole" & "_"
strCurrFile = strCurrFile & strDate & ".xls"
' strCurrFile = strCurrFile & ".xls"
Debug.Print (strCurrFile)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8 & strCurrFile


When the code did run it was putting the files into a directory with the file names minus the date/time so that part worked.

I also need to figure out how to check and see if that folder name already exists so I won't get an error on the MkDir line if the folder's already present. Often files have to be reloaded if new data is received.


Thanks for any guidance you can provide.

 

[Rabbie]

I also need to figure out how to check and see if that folder name already exists so I won't get an error on the MkDir line if the folder's already present. Often files have to be reloaded if new data is received.

Just use the
"On error" to trap that error and then

Resume Next to continue after the error.

 

[Lrn2Code]

Thanks so much!