Importing latest file from a list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an application that retrieves files from one system for use in
another and I need to import the latest file every morning to run some
queries and reports.

File name would be, e.g, customer.xxx.txt where xxx is a sequential sequence
number.

How can I import only the latest file rather than looking for a fixed
filename?

Thanks

Mark
 
Since xxx is a sequential number, may I assume that the largest xxx for that
customer name is the file you're looking for? If so, try

Dim strFileName As String, strMaxFileName As String
Dim intMaxNumber As Integer, intTempNumber As Integer

intMaxNumber = 0
strFileName = Dir("C:\Folder\" & txtCustomer & ".???.txt")
Do While strFileName <> ""
intTempNumber = Mid(strFileName, InStr(strFileName, ".") + 1, 3)
If intTempNumber > intMaxNumber Then
intMaxNumber = intTempNumber
strMaxFileName = strFileName
End If
strFileName = Dir
Loop

This will allow you to concatenate in the customer's name (from txtCustomer)
if you need to. You could also just "hard code" the name in if that's all
you need.
 
Thanks Wayne

That worked like a charm - apart from the fact the Access can't seem to
import the file - full name ends up as customer.062.txt - I think its
something to do with the 2 full stops - is Access is getting confused as to
the file type?
 
I don't know, what are you doing to "import" the file? Is this a delimited
data file?
 
Yes - I'm importing a delimited text file.

Have got around it by using filecopy to copy the latest file without the
sequence number.
 
Back
Top