Look up data in an Access table using value in Excel column

[LenJr]

I have an Excel workbook that I would like to have a macro run to update a
description field. This macro would link to an Access table and look up a
value and write it to a column in the open workbook. For example:
in my workbook I have column B that have project number in it (Range B2....)
and column C that I would like to populate with the project description. I
have an access database table tblProjects that has the Project number and
Description. I would like to run a macro that would connect to the access
database and use the value in Column B from the open Excel workbook and look
up that value on the tblProjects and write the Description in Column C of the
excel workbook. Some of the values in Column B will not be found on the
tblProject and I would like to skip those(not write any value in column C).

Thanks for any input.

 

[Joel]

try recording a macro while manually importing the data

From excel worksheet
1) Sttart recording a macro from the Tools - Macro menu
2) Import data from access by going to Data - Import external data - New
database Query
3) select MS Access Database
4) select Browse and locate your database
5) select data you want to import.
6) When finish stop recording macro from Tools - Macro menu

 

[LenJr]

Thanks for the quick response....but I am looking to import the data based on
the Project number listed in the B column. So just importing the table would
not be what I am looking for. I think I need to write some VBA doing a
connection to that database and using the values from B2, B3..... to look up
the description from the record set of the tblProjects....the values in
column B would be the Key to the tblProjects. At that point I need to
import(write out) the Description from the table lookup to Column C in excel.
Exmple:
Workbook before run the macro
A B
1 project# Description
2 123
3 456
4 789

Workbook After run the macro
A B
1 project# Description
2 123 Project name1
3 456 Project name2
4 789 Project name3

 

[Mike]

This will do it
Sub getProjectDescFromAccess()
'Needs reference the Axtive X Library 2.0 or higher
Const projectIDColumn = "B"
Const projectDescColumn = "C"
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim looper As Long
Dim cellPointer As Variant

'C:\PathToYourMdb\Database.mdb (Change)
'
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & ThisWorkbook.Path & "\db2.mdb;Persist Security Info=False"
Set cnn = New ADODB.Connection
cnn.Open strConn

For looper = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
Set cellPointer = Worksheets("Sheet1").Range(projectIDColumn & looper)
'If you project number field is text use this sSQL
'sSQL = "SELECT tblProjects.* FROM tblProjects WHERE
(((tblProjects.PROJECT_NUM)='" & cellPointer & "'));"
'If you project number field is number use this sSQL
sSQL = "SELECT tblProjects.* FROM tblProjects WHERE
(((tblProjects.PROJECT_NUM)=" & cellPointer & "));"
Set rs = New ADODB.Recordset

rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
If Not IsNull(rs.Fields("PROJECT_DESCRIPTION").Value) Then
Range(projectDescColumn & looper) =
rs.Fields("PROJECT_DESCRIPTION").Value
End If
rs.Close
Set rs = Nothing
Next looper

cnn.Close
Set cnn = Nothing
End Sub

 

[Joel]

When the query wizrd is running the second menu allows you to select a
filter. Select filter options froyour project.

Once you get the macro you can modifiy it as necessary. You can make the
filter options a variable to you can modify the query each time it is run
based on data in your workbook.

You can post the macro and I can modify as you require. This is the easiest
way of getting the code you need. It is difficult to post code bacause each
database is unique. There are too many options.

Here is sample code that I recorded. I modified the code slightly to make it
more readable. I added additional line continueations to make the code more
readable. Most of the options in the codee are long strings. I broke the
strings up to shorter pieces.


With Sheets("Sheet3").QueryTables.Add( _
Connection:=Array(Array("ODBC;" & _
"DSN=MS Access Database;" & _
"DBQ=C:\TEMP\Part Log.mdb;" & _
"DefaultDir=C:\TEMP;" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout"), _
Array("=5;")), _
Destination:=Range("H22"))
.CommandText = Array( _
"SELECT `parts received`.ID," & _
"`0215 & 0064 parts received`.Program," & _
"`0215 & 0064 parts received`.`Procurement Part Number`," * _
"`parts received`.`Upscreen Part Number`, `222", _
"parts received`.`Date Code`," & _
"`parts received`.`Date Received`," & _
"`parts received`.`Lot Review Date`," & _
"`parts received`.`Drawing Revision`" & _
Chr(13) & "" & Chr(10) & "FROM `C:\TEMP\Part Log`.", _
"`parts received` `parts received`" & _
Chr(13) & "" & Chr(10) & _
"WHERE (`parts received`.Program='Proj ABC')")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

 

[Mike]

If you need help to modify the ADODB code let me know.
And if you do, give the the Field Names in the tblProject database.

 

[LenJr]

All set with the Connections. That works great! Thank for all your help.

 

[richard gregson]

I am trying to return a value from an access database by looking up a value in excel. This is the code I have - but I cannot get it to work - I keep getting a run time error not a valid file name at the cnn.open part - please help!

Sub getProjectDescFromAccess()
'Needs reference the Axtive X Library 2.0 or higher
Const projectIDColumn = "A"
Const projectDescColumn = "J"
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim looper As Long
Dim cellPointer As Variant

'Q:\IT\Database Masters\Guarantees2.mdb
'
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & ThisWorkbook.Path & "Q:\IT\Database Masters\Guarantees2.mdb;Persist Security Info=False"
Set cnn = New ADODB.Connection
cnn.Open strConn

For looper = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
Set cellPointer = Worksheets("Sheet1").Range(projectIDColumn & looper)
'If you project number field is text use this sSQL
sSQL = "SELECT tblBank Gtes.* FROM tblBank Gtes WHERE(((tblBank Gtes.GTEE_NMBR)='" & cellPointer & "'));"
'If you project number field is number use this sSQL
'sSQL = "SELECT tblBank Gtes.* FROM tblBank Gtes WHERE" (((tblBank Gtes.GTEE_NMBR)=" & cellPointer & "));"
Set rs = New ADODB.Recordset

rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
If Not IsNull(rs.Fields("GTEE_NMBR").Value) Then
Range(projectDescColumn & looper) = rs.Fields("GTEE_NMBR").Value
End If
rs.Close
Set rs = Nothing
Next looper

cnn.Close
Set cnn = Nothing
End Sub

 

[Harald Staff]

This can't be right:
Data Source=" & ThisWorkbook.Path & "Q:\IT\Database Masters\Guarantees2.mdb

try
MsgBox strConn
somewhere and see what it reads.

HTH. Best wishes Harald



in message
news:[email protected]...