Add filename's Last Modified Date into a exisiting field for all records when importing (1 Viewer)

[RBL]

I need help in adding the imported files date into an existing field [Import_Date] while appending files to the table. currently I can append dta from all the files in a folder. But I would also need for each record appended the actual file name and its last modifed dateand time stamp also added.

My current code for appending data from the files is:

Sub XMLImport()
Dim fs
Dim fsFolder
Dim fsFile

Set fs = CreateObject("scripting.filesystemobject")
Set fsFolder = fs.getfolder("C:\Users\XMLFiles")
DoCmd.SetWarnings False

For Each fsFile In fsFolder.files

Debug.Print fsFile.Name
Application.ImportXML "C:\Users\XMLFiles" & fsFile.Name, acAppendData

Next fsFile
DoCmd.SetWarnings True
End Sub

Thanks

 

[theDBguy]

Hi. Welcome to AWF!

Probably the simplest approach is to run an UPDATE query after you have appended the file. You can update the ones with the missing data (i.e. Field Is Null). Just a thought...

 

[Isaac]

within the file system object, you are already looping through File objects. Each fsFile object has a .name property and also a .datelastmodified property (as well as a .path property).

based on the code you've posted so far, my approach would probably be to declare two variables..one to hold the file name, and one to hold the date last modified. assign those variables values based on the file properties I've listed above. then run an Update statement immediately after your Application.ImportXML line of code to update where those fields are null. You'd have to think this approach out carefully, to make sure that no record ever left those values null other than the immediate one being imported.