Importing the current file

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

Guest

Hi:

There is one folder that has files with current day within the name of the
file:
So:9/3/2006_A.csv, 10/3/2006_A.csv, 11/3/2006_A.csv; every day will be the
'same file name, A' but having the current day/different date.

How can I import always the latest one?

Thanks,

Dan
 
Show us the actual filenames you have. The ones you quote below are not
valid Window filenames.
 
I think you need to think more carefully. If you use 932006 to represent
9 March 2003, how do you tell the difference between 1112006 (January)
and 1112006 (November).

It would be better to store dates in an unambiguous and sortable form
such as
20060101, 20060102 ... 20061231
and import the file that comes last in an ordinary alphanumeric sort. A
function like this should give you the name, which you can then pass to
DoCmd.TransferText:

Public Function LastFileInFolder(Folder As String, Wildcard As String)
Dim FileName As String
Dim Newest As String

FileName = Dir(Folder & "\" & Wildcard)
Do While Len(FileName) > 0
If StrComp(FileName, Newest, vbTextCompare) = 1 Then
Newest = FileName
End If
FileName = Dir()
Loop
LastFileInFolder = Newest
End Function


You'd call it with something like this:

Dim FileToImport As String
Const IMPORT_FOLDER = "C:\Folder\Subfolder"

FileToImport = LastFileInFolder(IMPORT_FOLDER, "*.csv")
FileToImport = IMPORT_FOLDER & "\" & FileToImport
DoCmd.TransferTxt acImportDelim, blah blah
 
Not quite. Insert a new module, add
Option Explicit
on the first line, and paste in the LastFileInFolder() function from my
previous post. (That's everything from "Public Function" to "End
Function".) Then save the module. I'd name it something like "vbMisc",
but you can use any name you like _except_ "LastFileInFolder".

Then put the actual importing code (i.e. starting with
Dim FileToImport As String
and continuing to the DoCmd.TransferText line) in the Click event
procedure of a suitable button.
 
Thanks a lot, John!

Dan

John Nurick said:
Not quite. Insert a new module, add
Option Explicit
on the first line, and paste in the LastFileInFolder() function from my
previous post. (That's everything from "Public Function" to "End
Function".) Then save the module. I'd name it something like "vbMisc",
but you can use any name you like _except_ "LastFileInFolder".

Then put the actual importing code (i.e. starting with
Dim FileToImport As String
and continuing to the DoCmd.TransferText line) in the Click event
procedure of a suitable button.
Click to expand...
 
Back
Top