import last file created to Access

pi6

Registered User.
Local time
Yesterday, 23:01
Joined
May 13, 2011
Messages
17
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.
 
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
 
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.

 
the usage isn't quite right


try this

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

Users who are viewing this thread

Back
Top Bottom