Hi All

I am inporting a txt file daily, using the vba transfertxt command with a saved specification name, this is all working fine.
However i now need to get the date created/modified date from the txt file and store that date in a table (can be either same import table or a new table) can anyone help with how the get file attributes works.

Regards

Darren

Dim objFSO As Variant
Dim objFile As Variant
Dim sFile As String
Dim dtCreated As Date
Dim dtModified As Date

sFile = "C:\Path\FileName.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(sFile)
   dtCreated = objFile.DateCreated
   dtModified = objFile.DateLastModified

This could be added to the table that is storing the data in two fields, which will be the same for each record retrieved. If there is an autonumber PK, take the MAX() of that field before you bring in the new data, then update the table in those two fields for each record from the MAX + 1 to the end after the data is moved in, with the dtCreated and dtModified values. You could do that like this:

'Put this in a Module
Public iMaxPK As Integer

'Then take the value. Let me know if you need help with that.

'Add this code with you transfer text code, which should run the previous code before the import, or after, to get the dates:
Dim rs As ADODB.Recordset
Dim sSQL As String

sSQL = "SELECT * FROM tblYourTextDataTable WHERE [PKFieldName] > " & iMaxPK

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rs.MoveFirst
Do Until rs.EOF
   rs("DateCreated") = dtCreated
   rs("DateModified") = dtModified
rs.MoveNext
Loop
rs.UpdateBatch

rs.Close

Did that help?



mmcdonal

Thank you for your help

Worked like a charm, i had to modify a little
But as usual your suggestion solved my problem

Thanks Again
Darren