Copy Files using VBA

[Guest]

Hi,

I currently run an access 2000 database which imports files which are called
fullmonthnameDay.txt ie: October01.txt. I have macros which does this fine
however
what I was wandering was instead of numerous imports's in macros is there
any VBA code which I could use to do this job?

so the VBA code would look through the relevant directory depending on the
month,
and import the newest file only.

any pointers or code snipits would be appreciated...

Cheers

 

[Douglas J. Steele]

You can loop through a folder using the Dir function. You can get the Last
Modified date of a file using the FileDateTime function.

Dim dtmCurrFile As Date
Dim dtmMostRecent As Date
Dim strFolder As String
Dim strFile As String
Dim strMostRecent As String

dtmMostRecent = #1/1/1970#
strFolder = "C:\MyFolder\" ' Note that the final slash is critical!
strFile = Dir$(strFolder & "*.txt")
Do While Len(strFile) > 0
' strFolder & strFile is a file in the folder
dtmCurrFile = FileDateTime(strFolder & strFile)
If dtmCurrFile > dtmMostRecent Then
dtmMostRecent = dtmCurrFile
strMostRecent = strFolder & strFile
End If
strFile = Dir$()
Loop

MsgBox "The most recent file in " & strFolder & _
" is " & strMostRecent & vbCrLf & _
"It has a date of " & dtmMostRecent

 

[Guest]

Hi Douglass

Thanks V Much for getting back to me the code has helped, now one last
question how can I Import that specific file?

Cheers

 

[Douglas J Steele]

Look up the TransferText method in the Help file.

 

[Guest]

Hi Douglas,

yep that did the trick, the only snag now is that it seems to be importing
the same file 3 times.....

any tips?

I have placed the doCmd transfertext outside the while statement not sure if
this is causing probs also I am taking the directory and file to look at from
the strMostRecent string.......

 

[Douglas J Steele]

You'll have to show the actual code you're using.

 

[Guest]

Thanks for the assist Douglas....

managed to get it going fine!!!!

Thanks again