Import excel data to access cell by cell

[russmann2000]

I have an excel file that's been designed like a form with data in cells that need to be imported to an access table. The cells are all over the place and are in no particular pattern. I know how to do imports on simple spreadsheets, but this has me left scratching my head.

Is there a way to name the cells I need to import to the access table? I'm completely lost, and I can find nothing on-line to that specifies how to do it cell by cell.

Your help is greatly appreciated.

 

[pbaldy]

Research Excel automation. Here's one link:

http://www.theaccessweb.com/modules/mdl0006.htm

 

[russmann2000]

I'm not understanding how that pertains to my original post of wanting to select specific cells in an excel spreadsheet, and importing that data to a table.

 

[pbaldy]

You grab cell values with that and import using the AddNew method of a recordset or executing SQL from code.

 

[russmann2000]

It's not making any sense to me at all. Thanks anyway.

 

[Pat Hartman]

No one said it was easy. OLE automation is pretty complex because you are working with two separate object models. You are getting data from Excel using the Excel object model and they you are going to save it in a table using DAO in Access. The example Paul suggested is too simplistic and it goes in the other direction. It takes a hard-coded value from Access and places it in a cell in Excel. What you need to glean from the example is how to automate excel to instantiate the Excel object and open a workbook and then reference a cell.

Sometimes, it is easier to add a new sheet to the workbook and make the sheet look like a table with column headings and identical rows. Use formulas in the cells of the new sheet to pull data from specific cells on the other sheet. Then when you import the Spreadsheet, you can use TransFerspreadsheet (which is a single line of code) to import just that sheet.

 

[russmann2000]

I have hundreds of spreadsheets that I will end up importing. All have the same cells I will be pulling, so adding a second sheet to each of those wouldn’t be feasible.

 

[JHB]

I have an excel file that's been designed like a form with data in cells that need to be imported to an access table. The cells are all over the place and are in no particular pattern.

Is the form a real user form in Excel, or does it only look like a form?
How do/would you determine which cell to import the data from?
To help you further I need to know more about how the Excel sheets looks, could you post some printscreen from 2 quite different Excel sheets?

 

[arnelgp]

Here is an in efficient sample.