We help IT Professionals succeed at work.

Importing data from certain cells to a table in access

High Priority
54 Views
Last Modified: 2020-01-21
Wanted to import certain cells from an excel worksheet to a table in access:

On Capacity Exel attachment the cells highligthed in orange are the ones I would like imported to the a table called "tbl_SUPPLIER_CAPACITY".  I've manually entered the data from the excel sheet to the table to illustrate what I was hoping for.

See both attachments fo Excel and sample database.
C--Users-lfreund-Documents-EE-Capac.xlsm
C--Users-lfreund-Documents-Capacit.accdb
Comment
Watch Question

President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
Do you code in VBA or no?

There are three basic approaches to getting data to/from Excel:

1. TransferSpreadsheet action
2. Linking as a table.
3. Using OLE automation (open an instance of Excel, and read/write the spreadsheet by controlling Excel).

 The first can be done with a macro, but gives you the least amount of control over the process.

 Looking at your form, #3 is probably going to be the best, but you need to be able to code in VBA.   If you can/do, I will post an example.

Jim.

Author

Commented:
Good morning Jim...

thank you for the response.  I don't code in VBA.  Any help would be greatly appreciated.   Thanks!
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
um....might be a bit of a tough row then.   Code is straight forward enough and we can probably work through it though.

But why don't you try the TransferSpreadsheet action in a macro and see if it gets you the data you need (easy enough to check).  If not, you'll need to learn some VBA coding.

Jim.

Author

Commented:
I'll give that a try....thank you for the help

Explore More ContentExplore courses, solutions, and other research materials related to this topic.