Import only Files not already imported

[Rhino373]

To state my overall goal, I have a directory with Excel 2007 files. These files come in daily. They are imported to an Access 2003 database during various times throughout the month. Currently a user has to remember the last file that was imported so they do not import it again.



Here is what I am trying to do which will result in automatic import of files so users don't have to worry who imported last and where they left off.

1. Open all .xlsx files in the given directory, save them as .csv. (This part I have done)
2. Obtain a table with files that have already been imported (done)
3. Have Access compare only the .csv filenames found in the given location to the table and import using the TransferSpreadSheet method only the new files then add the filename to the table. (This is where I need help. I can import all .csv files but need help comparing them to filenames in an existing table.
4. I'll then delete all .csv files in this location so it appears nothing has changed and write the macro so all the steps occur everytime the database is opened (this I'll work on myself)

Can anyone clue me in how to compare a filename to an existing table?

Thanks

Ryan

[ajetrumpet]

check this out: http://allenbrowne.com/ser-59.html

that's used all over the world, I'm sure. That's an awesome piece of code. Might be useful here.

You probably should store the file names in a table, once they've been imported. that, IMO, would be the easiest way to check whether or not it's been imported already or not. with that method, it's simply a DCOUNT() or DLOOKUP() in every iteration of the loop when you run it to import file after file.

actually, you should be doing things like this anyway, because it's a good audit technique. Running functions and having people participate in import operations and other things without making records of what they're doing 'can' be dangerous. And of course, unproductive at times.