Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-25-2018, 02:02 PM   #1
russmann2000
Newly Registered User
 
Join Date: Mar 2018
Posts: 10
Thanks: 2
Thanked 0 Times in 0 Posts
russmann2000 is on a distinguished road
Import excel data to access cell by cell

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 is offline   Reply With Quote
Old 03-25-2018, 03:08 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,280
Thanks: 13
Thanked 4,111 Times in 4,043 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Import excel data to access cell by cell

Research Excel automation. Here's one link:

http://www.theaccessweb.com/modules/mdl0006.htm
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 03-25-2018, 04:59 PM   #3
russmann2000
Newly Registered User
 
Join Date: Mar 2018
Posts: 10
Thanks: 2
Thanked 0 Times in 0 Posts
russmann2000 is on a distinguished road
Re: Import excel data to access cell by cell

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.

russmann2000 is offline   Reply With Quote
Old 03-25-2018, 05:32 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,280
Thanks: 13
Thanked 4,111 Times in 4,043 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Import excel data to access cell by cell

You grab cell values with that and import using the AddNew method of a recordset or executing SQL from code.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 03-25-2018, 06:32 PM   #5
russmann2000
Newly Registered User
 
Join Date: Mar 2018
Posts: 10
Thanks: 2
Thanked 0 Times in 0 Posts
russmann2000 is on a distinguished road
Re: Import excel data to access cell by cell

It's not making any sense to me at all. Thanks anyway.
russmann2000 is offline   Reply With Quote
Old 03-25-2018, 08:19 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Import excel data to access cell by cell

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 03-25-2018, 08:25 PM   #7
russmann2000
Newly Registered User
 
Join Date: Mar 2018
Posts: 10
Thanks: 2
Thanked 0 Times in 0 Posts
russmann2000 is on a distinguished road
Re: Import excel data to access cell by cell

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.

russmann2000 is offline   Reply With Quote
Old 03-25-2018, 10:03 PM   #8
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Import excel data to access cell by cell

Quote:
Originally Posted by russmann2000 View Post
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?
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 03-26-2018, 02:12 AM   #9
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Import excel data to access cell by cell

Here is an in efficient sample.
Attached Files
File Type: zip ImportCells.zip (40.0 KB, 35 views)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
access , excel , import

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
import specific cell from excel to access mane_uk Modules & VBA 12 07-01-2011 02:31 AM
Import selected Excel cell contents into Access SadSadChicken Modules & VBA 1 12-28-2007 06:54 AM
Access Import destroying Excel Table/Cell format.. rsmonkey Excel 1 09-18-2007 12:53 AM
Import / Link Excel cell into Access field? CraigBFG Modules & VBA 0 10-19-2004 07:12 AM
Import from Excel - cell by cell ?? RTC General 12 11-17-2003 02:05 AM




All times are GMT -8. The time now is 02:22 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World