Need to import specific xlsx cells to Access 2007 table (1 Viewer)

Punice

Registered User
Joined
May 10, 2010
Messages
116
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.
 

sneuberg

AWF VIP
Joined
Oct 17, 2014
Messages
3,506
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("[COLOR="Blue"]C:\Users\sneuberg\Desktop\Book1.xlsx[/COLOR]")
    Set my_xl_worksheet = my_xl_workbook.[COLOR="Blue"]Worksheets(1)[/COLOR]
    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 [[COLOR="blue"]The Table Name[/COLOR]] ( 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.
 
Last edited:

Punice

Registered User
Joined
May 10, 2010
Messages
116
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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom