Excel 2007 import

[kimsMTC]

I have 2 separate Excel 2007 worksheets that need imported into 1 table in Access 2007. Since I can only import 1 worksheet/import, how can I get the 2nd worksheet into the same table, matching up values from existing records? In other words, an append will not work. I need to match records based on one of the columns (Account #).

Thanks!

 

[dhookom]

It isn't clear what you have and what you want. If you have 20 records in one Excel file and 20 records in another, do you want to end up with 20 records or 40? If 20, is there a primary key that would allow them to be joined?

I generally import into a "temporary" holding table for processing into a permanent table.

Duane
Hook'D on Access
MS Access MVP

 

[kimsMTC]

If I have 20 in each Excel file, I want 20 in the Access table IF the 20 PKeys match up. If only 18 match up, then 2 new records should be created for a total of 22. Make sense? I'm thinking I have to do what you eluded to, import each into temp tables, then use SQL script to join together and insert or update the existing records in the final table. Correct?

Thanks

 

[dhookom]

I would create a solution as you have outlined.

The other possible solution would be using Excel Automation to open the workbooks with code and append or update records. This would be fairly code intensive but might be fun to try.

Duane
Hook'D on Access
MS Access MVP

 

[kimsMTC]

Thank you for your help. Not sure I want to venture down the Excel Automation route, but it is a thought. While waiting for your reply, I've created the script and tested it and it works great that way. I can attach to a button and they can run it whenever they need to.

Thanks again.