lookup from Excel into Access

[acash85]

Hi all, I need some professional advice. I'm new to VBA and have very limited knowledge. But I need to create a macro that will automate some information me. See below for my problem/situation.

I have multiple accounts that have different product codes on there such as…

Act 1.
Prod Code MFR Unit of Measure List Price
098765 XXXX Each $10.00
123456 YYYY Pack $50.00

And I have a large database in Access which has over 12 million product codes, too much for Excel to handle.

Prod Code List Price MFR Unit of Measure
098765 $10.00 XXXX Each
123456 $50.00 YYYY Pack
165786 …. …… ………
987654 …. …… ………

I need to create a macro in Excel via VBA that will go into my access database and pull information for specific product codes only.
For example, if all I have is the prod code number I need to run an automated macro via VBA that will go into access and do vlookup (or DBVlookup) to fill in the rest of the blanks….

Act 1.
Prod Code MFR Unit of Measure List Price
098765 ??? ???? ?????
123456 ??? ???? ?????

Is there a way to create a macro that will pull data for a specific cell only. Its basically a Vlookup formula, but instead of pulling data into Excel from Excel, it pulls data into Excel from Access.

I appreciate your help.
Thanks

 

[BrianB]

Something like this.
I would be grateful if you would let me know if the code works best (faster) with or without the "POPULATE TABLE" section. It will happen automatically without the code, but I wondered if there might be more of a delay with a big table one way or the other. Make sure your lookup field is indexed - preferable "no duplicates" assuming this is the case.

'===============================================================================
'- LOOK UP ACCESS DATA TO EXCEL WORKSHEET
'- Lookup data in column A. Returns Access fields to columns
'- To speed this up make sure that the Access lookup field is indexed & "no duplicates"
'- Needs Tools\Reference to Microsoft Access xx Library & Microsoft DAO Object Library
'- Brian Baulsom October 2010
'===============================================================================
Sub ACCESS_LOOKUP()
    Dim ws As Worksheet
    Dim MyRow As Long
    Dim LastRow As Long
    Dim MyValue As String
    '---------------------------------------------------------------------------
    Dim MyDB As DAO.Database          ' Access .mdb file
    Dim MyTable As DAO.Recordset     ' Access Table
    '---------------------------------------------------------------------------
    '- OPEN THE ACCESS TABLE
    '---------------------------------------------------------------------------
    Application.Calculation = xlCalculationManual
    Set MyDB = Workspaces(0).OpenDatabase("F:\XL_MACROS\Access\Lookup.mdb")
    Set MyTable = MyDB.OpenRecordset("AccessLookupTable", dbOpenDynaset)
    '---------------------------------------------------------------------------
    '- POPULATE THE TABLE
    '- ** nb. Test with & without this code to check speed of operation
    With MyTable
        .MoveLast
        .MoveFirst
    End With
    '---------------------------------------------------------------------------
    '- WORKSHEET LOOP
    '---------------------------------------------------------------------------
    Set ws = ActiveSheet
    LastRow = ws.Range("A65536").End(xlUp).Row
    For MyRow = 2 To LastRow
        Application.StatusBar = " Row " & MyRow & "\" & LastRow
        MyValue = CStr(ws.Cells(MyRow, "A").Value)
        '-----------------------------------------------------------------------
        '- look up "PRODUCT" FIELD IN THE TABLE. RETURN "Field1" .. etc.
        With MyTable
            MyTable.FindFirst "PRODUCT='" & MyValue & "'"
            If .NoMatch Then
                ws.Cells(MyRow, 2).Value = "no match"
            Else
                ws.Cells(MyRow, 2).Value = .Fields("Field2").Value
                ws.Cells(MyRow, 3).Value = .Fields("Field3").Value
                ws.Cells(MyRow, 4).Value = .Fields("Field4").Value
            End If
        End With
        '-----------------------------------------------------------------------
    Next
    '---------------------------------------------------------------------------
    '- FINISH
    MsgBox ("done")
    Application.Calculation = xlCalculationAutomatic
    Application.StatusBar = False
    Set MyTable = Nothing
    Set MyDB = Nothing
End Sub
'--------------------------------------------------------------------------------