by Dan Belluscio (1 Submission)
Category: Microsoft Office Apps/VBA
Compatability: Visual Basic 3.0
Difficulty: Intermediate
Date Added: Wed 3rd February 2021
Rating: 
(3 Votes)
This code uses a value in cell B2 in Excel as a lookup on the AS400 and returns values to cells C2 and D2 in Excel
You must add a reference to:
Microsoft ActiveX DAta Objects 2.0 library
Inputs
Put the value you would like to lookup in cell B2
then run macro
Assumes
DSN-Less connection
VBcodersDim CN As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim strSQL As String
Sub GetNameCity2()
CN.Open "Driver={Client Access ODBC Driver (32-bit)}; System=typeyouras400ipaddress-or-as400namehere; Uid=typeyouras400Namehere; Pwd=typeyouras400passwordhere;" ' open connection to database
'this section retrieves the name and site
'PLTFILES# is the library
'ONETI561 is the file
'NAME, CITY, ADRNUM are the fields to retrieve
RS.Open strSQL, CN
strSQL = "select NAME, CITY, ADRNUM from PLTFILES#.ONETI561 where PRADDR = 'Y' AND ADRNUM = '" & Range("B2").Value & "'"
RS.Open strSQL, CN
If RS.BOF Or RS.EOF Then
msgbox "Could not find lookup value."
Else
RS.MoveFirst
Range("C2").Value = RS.Fields(0)
Range("D2").Value = RS.Fields(1)
End If
RS.Close 'Close recordset
CN.Close 'Close connection
End Sub
