Re: What is the best way to import data from a spreadheet to iSeries file?

Martin,

This may help VB Script.

Public Sub addopenpubip()
'   Dev:  EG
'   Date 2005/10/05
'   Function: SQL Insert the Date on work sheet Openip to an AS400 file 
HGERISC.EGRIFFIN.UTTDDCDP.

' Setup the ADODB Connection
If Not AS400Connected() Then
   Exit Sub
End If

Set Conn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.command")
Conn.Open "Provider=IBMDA400;Data Source=*AS400*;User 
ID=*ID*;Password=*PW*;"
Set rs = CreateObject("ADODB.Recordset")
Set rs.ActiveConnection = Conn

Sheets("Openip").Select                                     ' move to the 
Openip worksheet
maxrow = Range("A1").SpecialCells(xlCellTypeLastCell).Row   ' Get the max 
rows of the Openip worksheet

' MsgBox "Max Rows : " & maxrow
' This Do-Loop is to validate column A value is 1
counter = 2

For counter = 2 To maxrow                                   ' Loop though 
each row in Openip work sheet
    mCustomer_name = Range("A" & counter).Value
    mGateway = Range("B" & counter).Value
    mSubnet = Range("C" & counter).Value
    mIP_Address = Range("D" & counter).Value
    mCIDR = Range("E" & counter).Value
    If RTrim(Range("A" & counter).Value) = "* AVAILABLE *" Then
        Sql = "INSERT INTO 
HGERISC.EGRIFFIN.hgePubips(Customer_name,IP_Address,Gateway,Subnet,CIDR) 
VALUES('" & mCustomer_name & "','" & mIP_Address & "','" & mGateway & 
"','" & mSubnet & "','" & mCIDR & "')"
        ' MsgBox "SQL  : " & Sql
        Set rs = CreateObject("ADODB.Recordset")
        Set rs.ActiveConnection = Conn

        rs.Open Sql
        ' Run the SQL Insert for each row in Openip
    End If
Next counter

' rs.Close
Conn.Close
Sheets("Main").Select
End Sub


Ed Griffin
IT Analyst
City of Holyoke Gas & Electric Dept.
1 Canal Street
Holyoke, Ma 01040

T: (413)536-9422
F: (413)536-9440
egriffin@xxxxxxxx

http://www.hged.com/
http://www.hge.net/netsurf/home/




Booth Martin <booth@xxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
12/06/2005 03:29 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

Subject
What is the best way to import data from a spreadheet to iSeries file?






What is the best way to import data from a spreadsheet to iSeries file, 
not using Java, which I do not know?

I thought this had just been discussed, but I can not find it.