import specific cell from excel to access (1 Viewer)

mane_uk · Jun 29, 2011

Hi all,

I need to create a vba code to import 1 specific cell from an excel spreadsheet containing some currency data for each of my registered customers.

The excel spreadsheet contain more than 60 columns so I am not willing to upload the whole spreadsheet.

Having in mind that in the excel spreadsheet the column A have the customer ID and column W have the currency I need to upload, my idea was to loop through my existent customers in the database and using its ID number search for it in the excel spreadsheet column A and upload the data in column W when customerID matches.

My question is: how can I, from access, create a loop through the cells in excel to compare with my database information? Or how can I upload only my columns A and W without knowing exactly the number of lines that it contains?

I hope it makes sense,

Thanks for your help

Trevor G · Jun 29, 2011

I would suggest have a temp table in Access and import/upload the 2 columns into that table then run an update query to update where the id fields match.

You can use some ADO code or DAO code to do this.

An extract is shown below, but I haven't tested just these parts as I have around 11 pages of code that does various things in Access then Excel then back to Access.

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

mane_uk · Jun 30, 2011

Hi Trevor,

Thanks for the response but your code "exports data from the active worksheet to a table in an Access database" but I actually need a code to use in my Access database to IMPORT information from Excel. Would you have it as well?

Thanks

spikepl · Jun 30, 2011

Why not just link to the thing using the extrenal data wizard? Then you have a linked table you can process like any other record set and extract what you need using queries or vba

mane_uk · Jun 30, 2011

Hi Spikepl,

For a bunch of reasons I can't use link tables in this project so, unfortunately, your suggestion is not a possible option in this case!! Any other thoughts please?

Thanks

Trevor G · Jun 30, 2011

mane_uk said:
Hi Trevor,

Thanks for the response but your code "exports data from the active worksheet to a table in an Access database" but I actually need a code to use in my Access database to IMPORT information from Excel. Would you have it as well?

Thanks

Can you not adapt the code to open a workbook and then get the data?

Have you looked at using a MACRO in your database to TransferSpreadsheet and then Import.!

spikepl · Jun 30, 2011

Ok, so why not import the whole thing into a table? You mentioned "The excel spreadsheet contain more than 60 columns so I am not willing to upload the whole spreadsheet." but I do not quite see why the number of columns is a factor - can you explain?

spikepl · Jun 30, 2011

Besides - is this a one-off excercise? If so, then just copy the columns in question to another sheet in the workbook and import that

mane_uk · Jun 30, 2011

Trevor G said:
Can you not adapt the code to open a workbook and then get the data?

Have you looked at using a MACRO in your database to TransferSpreadsheet and then Import.!

Hi Trevor, I don't know how can I open the excel workbook and then point it into specific cells so I wouldn't know how to change your code to do it. And the TransferSpreadsheet would copy the whole spreadsheet which is not exactly what I want (see the reasons bellow).

Ok, so why not import the whole thing into a table? You mentioned "The excel spreadsheet contain more than 60 columns so I am not willing to upload the whole spreadsheet." but I do not quite see why the number of columns is a factor - can you explain?

Hi spikepl,
Some of reasons why I don't want to import the whole spreadsheet:
* If I import the whole spreadsheet I would be talking about copying over more than 60000 items of data (more than 60 columns multipled by more than 1000 lines of data) while I can't simplify and get only 1 column with the 1000 lines.
* this functionality will be run by the sales team on really really old laptops and most of the time they will run it while they are working from home connecting to the work's network so the time spent would be significantly increased if I need to import the whole spreadsheet and then make the comparison.
* the access database would "inflate" a lot when importing all the data and then deleting it. The users wouldn't know how to compact and repair. And the option of leaving the automatic compact and repair on when they close it has proven to be a mess as it takes forever on the laptops mentioned above making the users just switch off the computer without waiting it to finish which, most of the time, corrupts the database.

Besides - is this a one-off excercise? If so, then just copy the columns in question to another sheet in the workbook and import that

Unfortunately, It is not an one-off exercise. it is a functionality that I need to leave for the users to do once I implement the database.

Thanks guys for your help so far!!!

Anymore suggestions please!!

spikepl · Jun 30, 2011

OK interesting. So you mean you intend to have the spreadsheet located on the server, and without dragging all of its data across the network, the remote users' Access app is to extract the necessary data? I am not sure what Access does in this case. I have a suspicion a dynamically linked table might be a better way. and then running a query on it to get what you want. But again not sure. Some of the heavweights may have an answer, so just hang in here until one of them chimes in, before doing any coding.

spikepl · Jun 30, 2011

One idea is to automate the spreadsheet, so that the columns in question each is a specific Named range http://spreadsheets.about.com/od/exceltips/qt/named_range.htm

The TransferSpreadsheet method allows you to import data by specifying a named range, so in this way you could import each of the columns. But again, I am not sure what the thing drags across the network.

Update:

Actually, you may even import a range of cells using TransferSpreadsheet without defining a Nemd range. If you have A2007, then there is a little bug that requires fiddling with the range, because Acces screws the range definition up, when it executes the TransferSpreadsheet

spikepl · Jun 30, 2011

Bump - to bring this to the attention of all the brain-power present

mane_uk · Jul 1, 2011

Hey Spikepl/all,

Sorted. The code below is working:

Code:

Public Sub importFDU()
On Error GoTo Err_Handler
    Dim wbFDU As Workbook
    Dim objExcelApp As Excel.Application
    Dim db As Database
    Dim rstCustomer As Recordset
    Dim columnW As String
    Dim searchInA As String
    Dim A As String
    Dim W As String
    Dim iCounter As Integer
 
    Set db = openDatabase(sourceDb, False, False, passwordDB)
    Set rstCustomer = db.OpenRecordset("Select * from tblCustomer ")
 
    '--create Excel object:
    Set objExcelApp = New Excel.Application
    objExcelApp.Workbooks.Open ("C:\FDU\BIR_FDU.XLS")
    Set wbFDU = objExcelApp.Workbooks(1)
 
    If rstCustomer.EOF = False Then
 
        rstCustomer.MoveFirst
        Do While rstCustomer.EOF = False
 
            iCounter = 1
            A = "A" & iCounter
            W = "W" & iCounter
 
            ' open the source workbook, read only
            Do Until wbFDU.Worksheets("Sheet1").Range(A).Formula = ""
 
                searchInA = wbFDU.Worksheets("Sheet1").Range(A).Formula
 
                If rstCustomer!clientID = searchInA Then
 
                   columnW = wbFDU.Worksheets("Sheet1").Range(W).Formula
 
                    rstCustomer.Edit
                    rstCustomer.Fields("premiumFromFDU") = columnW
                    rstCustomer.Update
 
                End If
 
                iCounter = iCounter + 1
                A = "A" & iCounter
                W = "W" & iCounter
            Loop
 
            rstCustomer.MoveNext
        Loop
 
    End If
 
    wbFDU.Close False ' close the source workbook without saving any changes
    Set wbFDU = Nothing ' free memory
 
    rstCustomer.Close
    Set rstCustomer= Nothing
    db.Close
    Set db = Nothing
 
    Exit Sub
 
Exit_Program:
    DoCmd.Quit
Err_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
        "Error Number " & err.Number & vbCrLf & _
        "Error Description" & err.Description & vbCrLf & _
        "Your application will close!", _
        vbCritical, "An Error has Occured"
    Resume Exit_Program
End Sub

Thanks for all your help guys!!! Really appreciate it!!

Sakala · Jan 22, 2024

mane_uk said:

Hey Spikepl/all,

Sorted. The code below is working:

Code:

Public Sub importFDU()
On Error GoTo Err_Handler
    Dim wbFDU As Workbook
    Dim objExcelApp As Excel.Application
    Dim db As Database
    Dim rstCustomer As Recordset
    Dim columnW As String
    Dim searchInA As String
    Dim A As String
    Dim W As String
    Dim iCounter As Integer

    Set db = openDatabase(sourceDb, False, False, passwordDB)
    Set rstCustomer = db.OpenRecordset("Select * from tblCustomer ")

    '--create Excel object:
    Set objExcelApp = New Excel.Application
    objExcelApp.Workbooks.Open ("C:\FDU\BIR_FDU.XLS")
    Set wbFDU = objExcelApp.Workbooks(1)

    If rstCustomer.EOF = False Then

        rstCustomer.MoveFirst
        Do While rstCustomer.EOF = False

            iCounter = 1
            A = "A" & iCounter
            W = "W" & iCounter

            ' open the source workbook, read only
            Do Until wbFDU.Worksheets("Sheet1").Range(A).Formula = ""

                searchInA = wbFDU.Worksheets("Sheet1").Range(A).Formula

                If rstCustomer!clientID = searchInA Then

                   columnW = wbFDU.Worksheets("Sheet1").Range(W).Formula

                    rstCustomer.Edit
                    rstCustomer.Fields("premiumFromFDU") = columnW
                    rstCustomer.Update

                End If

                iCounter = iCounter + 1
                A = "A" & iCounter
                W = "W" & iCounter
            Loop

            rstCustomer.MoveNext
        Loop

    End If

    wbFDU.Close False ' close the source workbook without saving any changes
    Set wbFDU = Nothing ' free memory

    rstCustomer.Close
    Set rstCustomer= Nothing
    db.Close
    Set db = Nothing

    Exit Sub

Exit_Program:
    DoCmd.Quit
Err_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
        "Error Number " & err.Number & vbCrLf & _
        "Error Description" & err.Description & vbCrLf & _
        "Your application will close!", _
        vbCritical, "An Error has Occured"
    Resume Exit_Program
End Sub

Thanks for all your help guys!!! Really appreciate it!!

Good Day,

Sakala · Jan 22, 2024

mane_uk said:

Hey Spikepl/all,

Sorted. The code below is working:

Code:

Public Sub importFDU()
On Error GoTo Err_Handler
    Dim wbFDU As Workbook
    Dim objExcelApp As Excel.Application
    Dim db As Database
    Dim rstCustomer As Recordset
    Dim columnW As String
    Dim searchInA As String
    Dim A As String
    Dim W As String
    Dim iCounter As Integer

    Set db = openDatabase(sourceDb, False, False, passwordDB)
    Set rstCustomer = db.OpenRecordset("Select * from tblCustomer ")

    '--create Excel object:
    Set objExcelApp = New Excel.Application
    objExcelApp.Workbooks.Open ("C:\FDU\BIR_FDU.XLS")
    Set wbFDU = objExcelApp.Workbooks(1)

    If rstCustomer.EOF = False Then

        rstCustomer.MoveFirst
        Do While rstCustomer.EOF = False

            iCounter = 1
            A = "A" & iCounter
            W = "W" & iCounter

            ' open the source workbook, read only
            Do Until wbFDU.Worksheets("Sheet1").Range(A).Formula = ""

                searchInA = wbFDU.Worksheets("Sheet1").Range(A).Formula

                If rstCustomer!clientID = searchInA Then

                   columnW = wbFDU.Worksheets("Sheet1").Range(W).Formula

                    rstCustomer.Edit
                    rstCustomer.Fields("premiumFromFDU") = columnW
                    rstCustomer.Update

                End If

                iCounter = iCounter + 1
                A = "A" & iCounter
                W = "W" & iCounter
            Loop

            rstCustomer.MoveNext
        Loop

    End If

    wbFDU.Close False ' close the source workbook without saving any changes
    Set wbFDU = Nothing ' free memory

    rstCustomer.Close
    Set rstCustomer= Nothing
    db.Close
    Set db = Nothing

    Exit Sub

Exit_Program:
    DoCmd.Quit
Err_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
        "Error Number " & err.Number & vbCrLf & _
        "Error Description" & err.Description & vbCrLf & _
        "Your application will close!", _
        vbCritical, "An Error has Occured"
    Resume Exit_Program
End Sub

Thanks for all your help guys!!! Really appreciate it!!

Good Day Mane_uk,
I like this code you mentioned that it works .I have the same situation with you.

I need to create a vba code to import 2 to 3 specific cells from an excel spreadsheet data for each customers into an Microsoft Access Database table .
After reading your threads I realized that your code could just work fine for my situation.My question is this: How can I implement this on an Excel User Form.?

Thanks in advance.

Sakala · Jan 22, 2024

Sakala said:
Good Day Mane_uk,
I like this code you mentioned that it works .I have the same situation with you.

I need to create a vba code to import 2 to 3 specific cells from an excel spreadsheet data for each customers into an Microsoft Access Database table .
After reading your threads I realized that your code could just work fine for my situation.My question is this: How can I implement this on an Excel User Form.?

Thanks in advance.

cheekybuddha · Jan 22, 2024

Hi @Sakala, welcome to the forum.

You have joined on to a thread that is over 12 years old - I don't think user mane_uk has posted here since 2011.

I suggest starting a fresh thread detailing your requirements, and put a link to this thread if you can.

Sakala · Jan 24, 2024

cheekybuddha said:
Hi @Sakala, welcome to the forum.

You have joined on to a thread that is over 12 years old - I don't think user mane_uk has posted here since 2011.

I suggest starting a fresh thread detailing your requirements, and put a link to this thread if you can.

Ok , thank you for the suggestion.

import specific cell from excel to access (1 Viewer)

mane_uk

Registered User.

Trevor G

Registered User.

mane_uk

Registered User.

spikepl

Eledittingent Beliped

mane_uk

Registered User.

Trevor G

Registered User.

spikepl

Eledittingent Beliped

spikepl

Eledittingent Beliped

mane_uk

Registered User.

spikepl

Eledittingent Beliped

spikepl

Eledittingent Beliped

spikepl

Eledittingent Beliped

mane_uk

Registered User.

Sakala

New member

Sakala

New member

Sakala

New member

cheekybuddha

AWF VIP

Sakala

New member

Similar threads

Users who are viewing this thread