Load a Spreadsheet from a DB2/400 Database

Published: 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?

--"D"

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.

--Ted

RELATED STORIES

How to Call a Stored Procedure from Excel

Using Different ODBC DSN Types for i5 Access

DSN-Less Connections Using ActiveX Data Object

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot