RE: Automating downloading an Excel spread sheet to the iSeriesu sing a macro

Thanks Tim, I will give it a try.

Rene

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Hatzenbeler, Tim
Sent: Tuesday, May 09, 2006 1:52 PM
To: Midrange Systems Technical Discussion
Subject: RE: Automating downloading an Excel spread sheet to the
iSeriesu sing a macro


Here's how I do it...

I have a button... it calls this...

With the calendar1&2 values I used via the form controls..

That's about it...  I have other stuff I do, but this is the relevant
code... Also you will want to reference the ADO (ActiveX Data Objects) I
used the mdac 2.8

Hth, tim

--
Odbcdsn is the DSN name I used when I created my odbc connection...

Private Sub CommandButton1_Click()

'Defining variables
Dim cnOra As ADODB.Connection
Dim rsOra As ADODB.Recordset
Dim db_name As String
Dim UserName As String
Dim Password As String

Set cnOra = New ADODB.Connection
Set rsOra = New ADODB.Recordset

Dim wsSheet As Worksheet
Dim Sheet2 As Worksheet
Dim Sheet3 As Worksheet

Set wsSheet = ThisWorkbook.Worksheets(1)
Set Sheet2 = ThisWorkbook.Worksheets(2)

Set Sheet3 = ThisWorkbook.Worksheets(3)

newStrDate = Format(UserForm1.Calendar1.Value, "yyyymmdd")
newEndDate = Format(UserForm1.Calendar2.Value, "yyyymmdd")

db_name = "odbcdsn"
UserName = "superuser"
Password = "suprpassword"

'Making an ODBC connection according to ADO
cnOra.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" _
& Password + ";"
rsOra.CursorLocation = adUseServer

'Running a query
rsOra.Open "select * from ICDBFA.ICPAJ where ajtcod = '21' and (ajatyp >=
'A' and ajatyp <= 'Z') and ajatyp <> 'ISSUE' and (ajtdat >= " + newStrDate +
" and ajtdat <= " + newEndDate + ")", cnOra, adOpenForwardOnly

'Passing on data from the recordset to a variable or cell.
'Notice that the column name or alias is used to address
'data in the recordset.

wsSheet.Range("A1").CurrentRegion.Clear

' the 1st row is the resultsets column headers

fldCount = rsOra.Fields.Count
    For iCol = 1 To fldCount
        wsSheet.Cells(1, iCol).Value = rsOra.Fields(iCol - 1).Name
    Next


' copy the result set at row#2, leaving the header in tact.
wsSheet.Cells(2, 1).CopyFromRecordset rsOra



rsOra.Close



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Chavez, Rene (NBC
Universal)
Sent: Tuesday, May 09, 2006 1:05 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Automating downloading an Excel spread sheet to the iSeries using a
macro

> I have been given a project to accomplish the above.
> I can't seem to get a straight approach from anyone, 
> Can you help.
> 
> Thanks in advance,  Rene