Importing an Excel form into an Access database

[fortmill756]

Well folks i have a small problem. I need to find a method of importing an excel form, made in a spreadsheet (obviously), into an access table. I'll go through the short steps of what happens prior to Access:

1. I receive a completed Excel form in my email.
2. I need to take the Excel attachment and populate a record in an Access table with some of the info. The problem is this....the excel form is not set up linearly across the top like Access is, so it doesn't make a perfect match. I have to find a way to basically link cells together so that Access knows to take data from a specific cell.

I apologize if my explanation isn't the best, but if you think you can help me out and you have any questions then please, by all means, ask away. I thank you for reading this post and welcome any suggestions you may have. thank you.

 

[jdegeorge]

Can't do it. The excel file must be columnar/linear for it to work. The only other option is to import certain ranges at a time, but they also have to be columnar/linear.

Jim DeGeorge

 

[jdegeorge]

Hit SEND to soon. Also, you might want to consider getting rid of the non-conforming rows before you try to import. I have an excel file that's a mainframe dump with page numbering. I manually sort the file on the appropriate fields, and that groups the page number rows together, which I just delete. Then importing is easy. Not 100% automated, but it works.

Jim DeGeorge

 

[DrSimon]

You don't describe the structure of the emailed spreadsheet, however a few comments:
a) Would it be possible for you to dictate the structure of the spreadsheets sent to you? It's an obvious point but I often find that people forget the obvious! Forgive me if you'd already considered that.
b) If the received spreadsheet has a constant regular structure, you could create another spreadsheet that is formatted correctly and link that to the emailed spreadsheet. Then you could link the formatted spreadsheet to Access
c) An alternative to b) would be VB code that rewrites the spreadsheet as needed.