Complex Database Issue

5 pts.
Tags:
Access Database
Database
FTP
Microsoft Access
Microsoft Access 2003
Microsoft Excel
Sorting
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following