When I open a form, I want it to automatically check to see if there is are any updated files. I already have imported files into Access, but want to automatically check to see weather there have been any updates to the files.

Example

Form opens,
Checks the “C:\MyDouments” for any updated Excel Spreadsheets (This could be done by the modify date and time),
Imports the updated Excel Spreadsheet into Access

End

If the code checks the Modify date and time of a excel file, how can I import these values into Access i.e. I could have some query that would check the date and time of an excel file against the date and time in the access table – and if different import the latest version.

Also check for any new files.


Any ideas or help would be much appreciated

Do just what you said.

You will want to use WScript.FileSystemObject.

I would do this:

Dim objFSO
Dim objFile
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim sFile As String
Dim dtLastMod As Date

Set objFSO = CreateObject("Scripting.FileSystemObject")

sSQL = "SELECT * FROM MyLocalTableWithSpreadSheetNamesAndAttributes"

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

rs.MoveFirst
Do Until rs.EOF
   sFile = rs("FileNameColumn") 'ex "C:\MyDouments\MySpreadSheet.xls"
   dtLastMod = rs("LastModDateColumn")
      Set objFile = objFSO.GetFile(sFile)
         If objFile.DateLastModified > dtLastMod Then
             'do data transfer method here
             rs("LastModDateColumn") = objFile.DateLastModified
             rs.Update
         End If
rs.MoveNext
Loop
rs.Close


This will loop through all the filenames in your file name and attribute table, use the filename to go get the file attributes (you need to enter a file name with the path OR add the path to the string value like this: sFile = "C:\MyDocuments\" & rs("FileNameColumn") ) and then take the date last mod field and compare it to the date last mod field of the file. If the file has been modified more recently than the date entered in the table, then do your data import routine. That routine may not be able to update the table since it is already opened, so I added it to this routine.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Also, I would do this routine on the On Click event button that opens your form, BEFORE you open the form. That way it opens with the most current data already loaded. Otherwise you have to open the form, run the routine, then refresh the form anyway.

HTH

mmcdonal

Look it up at: http://wrox.books24x7.com

OK thanks for your help but im new to programming, what will i have to put for:

"MyLocalTableWithSpreadSheetNamesAndAttributes "

The name of the local table you are using to store the filenames and modified dates.

Then you have to reference the actual names of the columns that are holding the filenames and last mod dates.

Unless you provide the actual names of these items when you post, the poster has to insert names that indicate which objects go where, so it is always a good idea to post the actual before and after names you want for tables, columns, forms, reports, etc so the reply already has them in the code.


mmcdonal

Look it up at: http://wrox.books24x7.com