Search Tools Links Login

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

Original Author: Dan Belluscio

Inputs

Put the value you would like to lookup in cell B2
then run macro

Assumptions

DSN-Less connection

Code

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

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 117 times

Categories

Visual Basic 6

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.