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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

blooneptune

Board Regular
Joined
Nov 21, 2005
Messages
79
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:
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Forum statistics

Threads
1,137,298
Messages
5,680,684
Members
419,924
Latest member
Dhamodharan992

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
Top