Read / compare MS Access data in excel

[Bob]

I have a spreadsheet that contains a part number,
description, and price for parts. Essentially it's a
customer quote.

In an MS Access database ( which has 78K rows, too large
to import into excel ) resides the same information, but
along with it an "internal" part number.

What I need to do is this : read the list of part numbers
in excel, look them up in the database, and return me
the "internal" part number, placing it in a column next to
the "external" part number in the spreadsheet.

Something like a vlookup command but from a Access
database instead of another spreadsheet. Any ideas ?

Bob

 

[shockley]

Bob,

This worked for me in Win2K/sp3/Excel97/sp2

Assumes your MSAccess Database is named "PartNumbers.mdb"
The table with the data is named "PartNumbers"
You have fields "External" and "Internal" in the table containing the
external part numbers and you have named the index
"PartNumbers_External"

Sub GetInternalPartNo()

'Get external part number from spreadsheet _
and assign to variable "ExtPart"

ChDrive "C"
ChDir "C:\Documents and Settings\Shockley\Desktop"

Set dbs = OpenDatabase("PartNumbers.mdb")
Set rcs = dbs.OpenRecordset("PartNumbers")
rcs.Index = "PartNumbers_External"

rcs.Seek "=", ExtPart
IntPart = rcs!Internal

rcs.Close
dbs.Close

'Insert IntPart next to ExtPart in spreadsheet

End Sub

HTH
Regards,
Shockley

 

[shockley]

PS, I meant to also say that the index is for the "External" Field in the
"PartNumbers" table.