Load a Spreadsheet from a DB2/400 Database
June 6, 2007 Hey, Ted
I know from my reading that it is possible to make an Excel spreadsheet read from an iSeries database. I’ve been an RPG programmer for years, and I don’t know where to start. Can you point me in the right direction?
I’ll do my best. I have some Visual Basic code that I’ve used for years that I can share with you. I wish I could give credit to the original author, but I don’t remember where I got it.
First, crank up a new Excel spreadsheet. Pull down the Tools menu, select Macro, then Visual Basic Editor. The VB editor will open in a new window.
Use the Insert menu to create a new module. The editor will probably give it the name Module1, which is fine. Copy the following code into the module window.
Option Explicit Private Sub Workbook_Open() Dim Con As New ADODB.Connection Dim Cmd As New ADODB.Command Dim Rs As ADODB.Recordset Con.Open "provider=IBMDA400;data source=xxx;USER ID=xxx;PASSWORD=xxx;" Set Cmd.ActiveConnection = Con Cmd.CommandText = "SELECT * FROM QIWS.QCUSTCDT" Dim rowCount As Integer Dim colCount As Integer Dim text As String Dim Number As Long Dim val As Variant Set Rs = Nothing Set Rs = Cmd.Execute() Worksheets("sheet1").Activate Range("A1").Activate Selection.CurrentRegion.Select Selection.ClearContents Range("A1").Select rowCount = 1 For colCount = 0 To Rs.Fields.Count - 1 Worksheets("Sheet1").Cells(rowCount, colCount + 1).Value = Rs.Fields(colCount).Name Next colCount While Not Rs.EOF rowCount = rowCount + 1 For colCount = 0 To Rs.Fields.Count - 1 If Rs.Fields(colCount).ActualSize = -1 Then text = "" Else val = Rs.Fields(colCount).Value If VarType(val) = vbNull Then text = "" Else text = val End If End If Worksheets("Sheet1").Cells(rowCount, colCount + 1).Value = text Next colCount Rs.MoveNext Wend Set Rs = Nothing Con.Close End Sub
Replace the xxx tokens with values to indicate data source name, user profile name, and password. Also change the SQL command that is assigned to Cmd.CommandText so that it retrieves the desired data. I’d save the spreadsheet at this point.
In the VB editor, pull down the Tools menu and select References. VB responds with a list of available references. You’ll need to add one of the Microsoft ActiveX Data Objects libraries. I always pick the one with the highest release number. Click the check box to select, then click on the OK button.
To run the macro, press F5. Return to the spreadsheet window and you should see data.
I have used this code and variations of it for years and it has always served me well. If you’ll search the Four Hundred Guru archives, you’ll find more articles that touch on this subject. I’ve listed some of them below.