Lookup

blooneptune

Board Regular
Joined
Nov 21, 2005
Messages
79
Hello all.

I am trying to find out if there is a vLookup type formula to retieve data from an access database (91000 entries). I want to enter a product number in excel, and for it to return description and location in warehouse from Access.

Thanks in advance.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi

You can only do this via a user-defined formula that will link to the database. The code is not overly complicated - here's one I wrote for myself:

Code:
Function GetQl(ByVal GLcode As String, GLfield As String)
Dim dbLedger As DAO.Database
Dim rsData As DAO.Recordset
'Need to set a reference to Microsoft DAO object library
Set dbLedger = OpenDatabase("G:\Accounts\z_Financial Reporting\" & _
    "2006\QL Data\FSA-MGMTaccts.mdb", False, True, ";pwd=finance")
    
Set rsData = dbLedger.OpenRecordset("SELECT * FROM tblCategorisedQLDATA " _
    & "WHERE Ledger_Key like'" & GLcode & "';", dbOpenSnapshot)
 
GetQl = rsData.Fields(GLfield)
End Function

Hope this helps!

Richard
 
Upvote 0
Thanks for this. However my VBA skills are nil. I know that parts of this code need to be edited to suit my own database - just not sure which.. :confused:
 
Upvote 0
OK

What's the location of your Access Db? eg C:\Database\Access.mdb

What is the name of the Table that you want to Lookup a value in, and what field has the unique lookup value (like a productID) and what field do you want to return (what's the name of it in the table)?

Does your access db require a password?

Richard
 
Upvote 0

Forum statistics

Threads
1,211,880
Messages
6,104,547
Members
447,915
Latest member
jpearson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top