Question

Complex Database Issue

Database, Microsoft Access, FTP, Microsoft Excel, Sorting, Access 2003, Access Database

I'll try to be brief:
I have excel files that are downloaded via ftp to a folder where I have my DB. each of the excel files is "roughly" the same headers, I want to import all of these files, filter out all but 3 columns and then filter out certain rows in each table (at minimum, 25 tables)
can this be automated and if so, what is the best way to do it, or where can I find a good reference / template to start with.

Yes this can be automated, but you'll need to deal with the "roughly" part. Roughly isn't good enough for VBA.

You'll also need to explain what you mean by "import" -- aggregate all the files into one super-file? What about the headers?

One way is to loop through the files in a folder and open only the Excel files (or I'm sure you could build some kind of file filter with the FileSystemObject).

For a code sample, check out the fourth post in this thread: http://www.forumtopics.com/busobj/viewtopic.php?p=150279

Then you would apply your filter using the AutoFilter Method (see http://msdn.microsoft.com/en-us/library/aa221844(office.11).aspx), and last, you would copy only those columns you wanted over to the new workbook (see http://exceltip.com/st/Copy_a_Column_or_Columns_to_a_Database_sheet_using_VBA_in_Microsoft_Excel/546.html)


HTH