import last file created to Access

[pi6]

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?

Thank you.

 

[gemma-the-husky]

research these

use DIR to search through the files

use FILEDATETIME to check the datetimestamp of the file

use dcomd.transfertext to import the file to a table in your database

 

[pi6]

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.

 

[gemma-the-husky]

the usage isn't quite right


try this

'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.

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.

 

[pi6]

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

 

[gemma-the-husky]

just to check this is what you wanted.

- 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.