Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read / compare MS Access data in excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read / compare MS Access data in excel
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 "Bob" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read / compare MS Access data in excel
PS, I meant to also say that the index is for the "External" Field in the
"PartNumbers" table. "shockley" wrote in message ... 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 "Bob" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query from Access into Excel cause Access to go to read only | Excel Discussion (Misc queries) | |||
When opening Excel - get Access (Read-Only) file and error | Setting up and Configuration of Excel | |||
In EXCEL, access denied when deleting read only | Excel Discussion (Misc queries) | |||
i cannot open excel file error Cannot access read-only document me | Excel Worksheet Functions | |||
Sumproduct in Excel Spreadsheet to read Access db table | Excel Worksheet Functions |