Get specific data from Access into Excel

[Guest]

I am looking for a way to build my parts list for multiple vendors in Access,
and then from an Excel spreadsheet perform a lookup of a piece of that data.
Basically in Excel I want to type in a part number, and have the next 4
columns return description, vendor, list price and my cost.

I need to use Access for the parts list, because one vendor has 75,000 part
numbers, and I have roughly 20 vendors info I want to be able to perform the
lookup on. Currently I use VLOOKUP in Excel and look the data up in another
worksheet. But i need to have about 25 worksheets for the different lists.

I'd like to make one sheet which will allow lookups of all the products,
then I can just copy and paste special values into my quoting form.

 

[John Nurick]

Hi Chris,

Put all your parts in a single table in an Access mdb file, e.g.
tblParts
PartNumber (Primary key)
Description
Vendor
ListPrice
CostPrice
Then you can look up the data for a part number either by using Excel's
SQL.REQUEST() worksheet function or by using code like this, which
assumes the part number is in the selected cell on the worksheet and
puts the other data in the cells to the right of it.


Sub GetPartData()
Dim dbE As DAO.DBEngine
Dim dbD As DAO.Database
Dim rstR As DAO.Recordset
Dim strS As String
Dim raR As Excel.Range
Dim j As Long

'Open database
Set dbE = CreateObject("DAO.DBEngine.36")
Set dbD = dbE.OpenDatabase("C:\Folder\file.mdb")
Set raR = Selection.Cells(1)

'Open recordset filtered on part number
strS = "SELECT Description, Vendor, ListPrice, CostPrice " _
& "FROM tblParts WHERE PartNumber = '" _
& CStr(raR.Value) & "';"
Set rstR = dbD.OpenRecordset(strS, dbOpenSnapshot)

'Put data in adjacent cells
For j = 0 To rstR.Fields.Count - 1
ActiveSheet.Cells(raR.Row, raR.Column + j + 1).Formula = _
rstR.Fields(j).Value
Next

'Tidy up
rstR.Close
Set rstR = Nothing
dbD.Close
Set dbD = Nothing
Set dbE = Nothing
End Sub

 

[Guest]

I'm not really familiar with the code you mention. Where would I input the
code on my Excel sheet? And how would I trigger it to happen when a value is
entered in the lookup cell? I can send you a copy of a file showing how I
currently do the lookup with VLOOKUP in Excel. I also tried to code an
SQL.REQUEST statement and it doesn't seem to recognize the command. I
checked and I do have the ODBC driver selection installed in Excel.

 

[John Nurick]

This is up to you. The procedure I posted could be called from a button
on the worksheet, a toolbar button, or a menu command. In that case
you'd paste it into a standard module (which must not have the same name
as the procedure) and then call it by name.

Alternatively the code could be modified slightly and included in the
worksheet's On Change event procedure. I've never used that myself but
the general idea would be something like this (pseudocode)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dbE As DAO.DBEngine
Dim dbD As DAO.Database
Dim rstR As DAO.Recordset
Dim raR as Excel.Range
Dim strS As String
Dim j As Long


If Target.Cells(1).Column = part number column Then
'include the body of the procedure here,
'replacing
' Selection.Cells(1)
'with
' Target.Cells(1)
End If
End Sub

If you need help on this, it would make sense to ask in an Excel group
where people do this sort of thing every day.