I need values from a table using data from an excel sheet.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table in access with all part numbers and prices. I have a separate
excel sheet that lists part numbers that need to be purchased that day. I
need a quick way to get all the prices transfered from the access table to
the excel sheet in one shot. Right now I have to go through the table and do
a search for each and every part number.

Thanks for the help.
 
you can search/query the access table - with the excel list as the criteria
of the query (a long 'OR' list in the criteria field of the query design
matrix)

setting that up is a bit manual intensive - but once you get it going it
should work.

I would probably insert the current excel into a standard named re-useable
table i.e. TodayListTable

then in the query I would define the criteria as the fields of the
TodayListTable.

each time you run the query it will then search based on whatever is in that
TodayListTable...

one problem may be if the todayList varies in length - - as then you will
have the query looking for fields that are nulls (because not existing in the
TodayListTable) - you might have to experiment with a work-around like adding
dummy data....just off the cuff thinking here....
 
I'd probably use an Excel macro to look up each value in the database.
Here's one I've used for this job: it's worked for me but it definitely
isn't bombproof.


Sub GetData34(Keys As Range, Values As Range, _
DatabaseName As String, Table As String, _
KeyField As String, ValueField As String, _
KeyFieldType As String)

'Works through all the cells in Keys, looking up
'each value in Table.KeyFieldName
'grabbing the value of ValueField in the same record,
'and placing it in the
'corresponding cell in Values.

'Warning: not yet thoroughly tested. As it stands
'will fail on text keys that contain apostrophes.

'Some of this (but not the buggy bits) is based on
'Allen Browne's ELookup() function.

Dim dbEngine As Object 'DAO.dbEngine
Dim db As Object 'DAO.Database
Dim rs As Object 'DAO.Recordset
Dim strSql As String
Dim strKeyValue As String
Dim j As Long

'Open database
Set dbEngine = CreateObject("DAO.DBEngine.36")
Set db = dbEngine.OpenDatabase(DatabaseName)

For j = 1 To Keys.Cells.Count
'Build the SQL string.
strSql = "SELECT TOP 1 [" & ValueField & "] FROM [" _
& Table & "] WHERE [" & KeyField & "] = "
Select Case LCase(KeyFieldType)
Case "string", "text", "memo"
strKeyValue = "'" & Keys.Cells(j).Value & "'"
Case "date", "time", "date/time"
strKeyValue = "#" & Format(Keys.Cells(j).Value, _
"mm/dd/yyyy") & "#"
Case Else
strKeyValue = CStr(Keys.Cells(j).Value)
End Select
strSql = strSql & strKeyValue & ";"
Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
Values.Cells(j).Formula = ""
Else
Values.Cells(j).Formula = rs.Fields(0).Value
End If
rs.Close
Next j

Exit_GetData34:
Set rs = Nothing
db.Close
Set db = Nothing
Set dbEngine = Nothing
Exit Sub

Err_GetData34:
MsgBox "Error in GetData34 at row " & j & ". " & vbCrLf _
& "Error " & Err.Number & ": " & Err.Description, _
vbOKOnly + vbExclamation, "Database lookup"
Resume Exit_GetData34
End Sub
 
Back
Top