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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-02-2016, 10:15 AM   #1
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 80
Thanks: 37
Thanked 1 Time in 1 Post
Punice is on a distinguished road
Thumbs up Need to import specific xlsx cells to Access 2007 table

Hit another wall...researched the World, but didn't find what I could get to work. I have an Excel 2007 spreadsheet with cells 'D4', 'I2' & 'N4', set as
type 'text'. I have a table with fields named: 'Area', 'Cust_No' & 'L_Name'.
I want to read the excel data into the table when I push a button. If somebody can show me the vba to do that for one xlsx file, I think I'll be able to expand the sub-routine to loop through additional xlsx files and do the transferring to the table.

Punice is offline   Reply With Quote
Old 09-02-2016, 02:44 PM   #2
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 975 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Need to import specific xlsx cells to Access 2007 table

Here's some code that will insert a record into a table with those values. You need to change the values in blue to fit your situation. Worksheets(1) is just the first worksheet.


Code:
Private Sub AddExcelData()
    Dim my_xl_app As Object
    Dim my_xl_worksheet As Object
    Dim my_xl_workbook As Object
    Set my_xl_app = CreateObject("Excel.Application")
    Dim strArea As String
    Dim strCustNo As String
    Dim strLName As String
       
    'change path as required
    Set my_xl_workbook = my_xl_app.Workbooks.Open("C:\Users\sneuberg\Desktop\Book1.xlsx")
    Set my_xl_worksheet = my_xl_workbook.Worksheets(1)
    strArea = my_xl_workbook.Sheets(1).Range("D4")
    strLName = my_xl_workbook.Sheets(1).Range("I2")
    strCustNo = my_xl_workbook.Sheets(1).Range("N4")
    CurrentDb.Execute "INSERT INTO [The Table Name] ( Area, Cust_No, L_Name) VALUES('" & strArea & "', '" & strCustNo & "', '" & strLName & "')"
    my_xl_workbook.Close
    Set my_xl_app = Nothing
End Sub
This code could raise errors which need to be dealt with, e.g., wrong path to spreadsheet, cell out of range, etc.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve

Last edited by sneuberg; 09-02-2016 at 02:48 PM. Reason: Added needs error checking
sneuberg is offline   Reply With Quote
The Following 2 Users Say Thank You to sneuberg For This Useful Post:
Leo_Polla_Psemata (09-03-2016), Punice (09-03-2016)
Old 09-03-2016, 08:31 AM   #3
Punice
Newly Registered User
 
Join Date: May 2010
Location: Middletown, VA
Posts: 80
Thanks: 37
Thanked 1 Time in 1 Post
Punice is on a distinguished road
Re: Need to import specific xlsx cells to Access 2007 table

sneuberg's code worked perfectly. Now, I don't have to dream about trying to solve my problem. Once again, this forum came to my rescue. Thanks for being here. Punice

Punice is offline   Reply With Quote
Reply

Tags
import , table , xlsx data

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using VBA to select specific ranges of cells to import to Access table. Klimek Modules & VBA 0 08-08-2013 07:59 AM
[Help]VBA Macro To Import Specific Cells from Excel to Access mythandier Modules & VBA 0 08-07-2012 05:11 PM
Import Specific Cells from variable sheets into Access Tables. travisdh Modules & VBA 2 01-06-2011 08:39 PM
Import Specific Cells from Excel to Access evocube Modules & VBA 3 11-06-2009 09:05 AM
Exel to access import with specific cells RandRob Macros 3 02-22-2009 02:29 PM




All times are GMT -8. The time now is 03:11 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