• Home
 · 
• Microsoft Office Apps/VBA
 · 
• AS400 to Excel

AS400 to Excel

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

Rate AS400 to Excel

• 1
• 2
• 3
• 4
• 5

(4(4 Vote))

Dim 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