Can somebody please help me? I have a folder were csv files automaticaly downloaded from host system, and file name is assigning by the system. How can I import the last file that was created to the access database?
Dave, thanks a lot for your advice.
I tried: MyPass = "T:\Admin\PT\Billing\Main\2020\Mcare\" FileName = Dir(MyPass, vbNormal) FileDate = FileDateTime(FileName)
I am getting first file in the folder, but on the line 'FileDate = FileDateTime(FileName) I am getting run-time error 53, file not found. Any suggestions, I appreciate your help.
'first define the folder, as you have done
MyPass = "T:\Admin\PT\Billing\Main\2020\Mcare\"
'now search for pdf files in that folder
'note - use *.* to search all files in the folder
FileName = Dir(MyPass &[COLOR="Red"] "*.pdf", [/COLOR]vbNormal)
'if we found one, then get the date
if len(filename) > 0 then
FileDate = FileDateTime(mypass & FileName)
msgbox("File: " & fielname & " Dated: " & filedate)
else
msgbox("No file found with extension *.pdf")
end if
so then slightly more advanced is this, which checks all the files in the folder - and you can use this idea to find the file with the latest date.
Code:
FileName = Dir(MyPass & "*.pdf", vbNormal)
while len(filename)>0
filedate = filedatetime(mypath & filename)
filename = dir 'get the next file meeting the orginial spec - ie .pdf extension
wend
one last note - Dir() is not recursive - so you can only examine a single folder - you cannot search a folder and its sub-folders in the same process.
Thank you very much Dave for your help.
It works now.
MyPass = "T:\Admin\PT\Billing\Main\2020\Mcare\"
FileName = Dir(MyPass & "*.csv", vbNormal) While Len (FileName)>0 CnvDate = Format(FileDate,mm/dd/yyyy) If CnvDate=Me.txtWeek6.Value Then DoCmd.TransferText acImportDelim, "2020M", tb2020", MyPass & FleName End If FileName=Dir Wend
- if you have more than 1 file dated equal to your search date, they will all be imported, but will successively overwrite, so only the last one actually gets imported.