Importing files each day with a different name 2

[St0rm]

I have a problem that, I’m trying to work out, I have a database that imports a file everyday and appends the data to a table, all nice and easy but we are in the process of changing a few things at work and the report that I import will soon change names every day to the day before date. In the format YYMMDD this is a major problem for me. the only way that I can see getting it work is edit the macro every day with the new name.

 

[PaulF]

shouldn't be... try this

Dim strFile As String
strFile = Format(DateAdd("d", -1, Date), "yymmdd&quot & "File.txt"
MsgBox strFile

Then create a DoCmd.TransferText statement which uses the variable as part of the filename.

(i.e. "C:\MyDocuments\" & strfile )

PaulF

 

[St0rm]

thanks for the help, it ended up looking like this

Dim strFile As String
Dim mydate
mydate = Date - 1
strFile = Format(mydate, "yymmdd&quot
DoCmd.TransferSpreadsheet acImport, 8, "Totals", "p:/cmb/Commercial/" & strFile & ".xls", False, "A3:N3"
End Sub

 

[dschomburg]

This is great I will be running into a situation likewise.
Do you run your macro manualy or do you have a timer set?
If it launches automaticaly, What do you do about the weekends if something goes wrong. Say (and I know this never happens) the network goes down.