Import Excel with variable filename 2

[jmcg]

I have to import an Excel file with name Closure ddmmyy.xls
The date part of the file name is variable and outwith my control (ie it does not run on a regular schedule).

What I am looking to do it look for a file begining closure and either rename it to something generic or import whatever it is called.

Need to automate as there are a dozen or so of these file so renaming manually is not an option.

Is this possible within Access?

 

[PWise]

Dim FileName As String
FileName = Dir("c:\Closure*.xls")
do while finename>""
docmd.TransferSpreadsheet acImport,,tablename,filename

FileName = Dir
loop

 

[djj55]

Hello pwise,
Perfect timing as I had a similar problem (219 files).
Thanks
djj

 

[jmcg]

excellent thanks

 

[JerryKlmns]

Under XP, if you select many files and rename one of the selection (i.e myFile.xls) then the renamed file keeps the name you gave it and the rest take this myFile(i).xls where i is a counter incremented by 1. So manylly doing it is in some cases easy!