import specific cell from excel to access

[maneuk]

Hi all,



I need to create a vba code to import from an excel spreadsheet 1 specific cell 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

[Rod]

I offer you two solutions:

1. Use the supplied Access functionality to import the spreadsheet into a table - yes, all 60 columns. Use VBA to loop through your customer table and search for matches in the imported data table. Do whatever with the matched data. Throw away the table you first thought of, i.e. the imported data table. This is a hands-on solution but may be faster than the next solution. This solution is appropriate for occasional use by yourself. It is not an automated regular-use solution for you or a proficient user.
2. Programatically do what you described in your post. This solution may take more time to develop and may run slightly slower than (1) above. (No justification for saying this, just gut feel.) I would not consider this solution unless the program was to be run on a regular basis.

I don't know your expertise but I have warned another poster that to accomplish such as solution 2 you need rudimentary understanding of:

• Objects and Object Oriented Programming;
• Component Object Models (COM) and in particular the COM for Excel;
• VBA programming and the VBA Coding Window;
• Use of the File Scripting Object that is found in Microsoft Scripting Runtime.

Post back with your thoughts, comments and intentions.

[maneuk]

Hi Rod,

First of all thanks for your response.

I need to leave the functionality automated for my employee once I finish my contract next month. They are going to be running it every week so definitely need to be solution 2.

About the knowledge not so sure why would I need knowledge in all the items you mentioned but anyway, if you have an example on the vba code on things like how to point and get the information from an specific cell in excel from access, I really would appreciate it.

Many Thanks

[Rod]

Hi again,

It's getting late for me and I don't want to post any ill considered advice. So if you can wait for ten hours or so I will sleep and then post.

Meanwhile some things to think about. The Excel worksheet has to be in a predictably known form. Are customer ids always in the same column? If not how do we identify that column? When scanning down the customer ids, how do we know when the end of list is reached? What if someone has inserted a blank row in the spreadsheet? Is an instance of Excel already running? Do we use that instance or start another? What if the spreadsheet is already in use?

If you have time I would be interested to know which of the skills/knowledge I listed you feel is not necessary - oh, and why, of course.

Since you are a contractor I may assume an advanced skill/knowledge level?

[maneuk]

Hi Rod,

Answering your questions:

* Are customer ids always in the same column? If not how do we identify that column? Yes, always in the same column


When scanning down the customer ids, how do we know when the end of list is reached? What if someone has inserted a blank row in the spreadsheet? The excel spreadsheet is created on the back of a main server query against customers with registered ID (mandatory field in the main server application). As I am going to use the extract direct from there without anyone handling it they won't be able to add any blank row.

Is an instance of Excel already running? Do we use that instance or start another? What if the spreadsheet is already in use?
We can work with all possibilities here, we can either ask to have an instance running or start a new one. As mentioned above, no one will be using this spreadsheet.

If you have time I would be interested to know which of the skills/knowledge I listed you feel is not necessary - oh, and why, of course. Based that normally the answers in this forums are examples of code I can't really see why someone would need to know it all, if the case would be only to get the copy code, paste it in your database and change it accordingly.

Since you are a contractor I may assume an advanced skill/knowledge level? Yes, but not enough skills to import an specific cell from excel into access database.

I have been trying the following line of thoughts but it is not working as not recognising my variable declaration:

    Dim oExcel As New Excel.Application
    Dim wbFDU As Excel.Worksheet
    Dim db As Database
    Dim rstCustomer As Recordset
    Dim columnW As String
    Dim A As String
    Dim C 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
            'initialise variables
            i = 1
            A = "A" & i
            C = "C" & i
        
            ' open the source workbook, read only
            Do Until wbFDU.Worksheets("Sheet1").Range(A).Formula = ""
                    
                If rstCustomer!customerID = wbFDU.Worksheets("Sheet1").Range(A).Formula Then
                
                    columnW = wbFDU.Worksheets("Sheet1").Range(W).Formula
                    
                    rstCustomer .Edit
                    rstCustomer !premiumFromFDU = columnW
                    rstCustomer .Update
                End If
                
                i = i + 1
                A = "A" & i
                C = "C" & i
            Loop
        
            rstCustomer.MoveNext
        Loop
        
    End If

Thanks for your help

Regards

[Rod]

Not recognising your variable declarations? I suppose you have included the Excel dll in your references?

That was my first off-the-top-of-my-head thought. I shall now have a look at your code.

[Rod]

Some observations:

• The variable name for the Excel application reference is different - oExcel and objExcelApp
• Set objExcelApp = New Excel.Application Don't think this will work, I've never tried it. The way to create a reference to another application is by using the CreateObject or GetObject functions. For example:

 Set objExcelApp = CreateObject("Excel.Application")

• You use the Formula property. Do you mean that or should you be using the Value property?

That's all I can see at the moment. I suggest you read up on CreateObject and GetObject - they do more or less what their names suggest although there is a degree of overlap between them.

[maneuk]

Had a look on both - CreateObject and GetObject - but can't see how to link it to the excel file. But came accross a example of exporting from excel to access using ADO so I am assuming that I might be able to also use ADO to import Excel into my Access... has anyone done it before? Any code examples that I can use as base?

Thanks

[Rod]

Hi again,

Private Sub example()
 
Dim objExcel As Excel.Application
Dim wks As Excel.Worksheet
 
Set objExcel = GetObject("C:\FDU\BIR_FDU.XLS", "Excel.Application")
Set wks = objExcel.Worksheets("Sheet1")
 
End Sub

Link to the Excel file is made by the GetObject function. The function:

• returns a reference to the file if it is open;
• opens it if not and returns the reference;
• starts a hidden Excel session if necessary.

If .xls extension is associated with Excel on your m/c then the second argument in the GetObject function is redundant.

(BTW Set objExcel = New Excel.Application does start a new Excel session. I never knew that, I live and learn. )

ADO (ActiveX Data Objects) is an alternate data access method. DAO is the native object model for accessing JET databases. It can access other databases via ODBC but needs to work through a second ODBC workspace. ADO on the other hand is built to be a generic any-compliant-database access object model (including JET). However ADO does not have the 'richness' of DAO when accessing JET dbs.

I don't see how ADO helps you.

Has anyone done it before? Yes I have, many times, but not for a few years and usually the other way around. I'm a little rusty but it's coming back!

[maneuk]

Hey Rod,

I was re-reading the thread today and decided to check about the excel.dll as I don't know why I had assumed that it was there but then just found out that it wasn't therefore the problems I was having!! doh!!

Anyway, the code below is what I am using and it is working:

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 your help mate!!! Really appreciate it!!

[Rod]

Sorry couldn't help giggling - well more of a smirk really. Fabulous, so pleased it's working for you.