Import excel data to access cell by cell (1 Viewer)

russmann2000

Registered User.
Local time
Today, 09:16
Joined
Mar 25, 2018
Messages
13
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.
 

russmann2000

Registered User.
Local time
Today, 09:16
Joined
Mar 25, 2018
Messages
13
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

Wino Moderator
Staff member
Local time
Today, 07:16
Joined
Aug 30, 2003
Messages
36,118
You grab cell values with that and import using the AddNew method of a recordset or executing SQL from code.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:16
Joined
Feb 19, 2002
Messages
42,970
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

Registered User.
Local time
Today, 09:16
Joined
Mar 25, 2018
Messages
13
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

Have been here a while
Local time
Today, 15:16
Joined
Jun 17, 2012
Messages
7,732
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

..forever waiting... waiting for jellybean!
Local time
Today, 22:16
Joined
May 7, 2009
Messages
19,169
Here is an in efficient sample.
 

Attachments

  • ImportCells.zip
    40 KB · Views: 95

Users who are viewing this thread

Top Bottom