Input from excel to attachmate

[poem2004]

Gentlement:

I have the code to grab data from a range of cells from excel, row by row and input to attachmate screen. The code works fine for a few rows, but I would like to see if we can add the 2 FOR loops to automatically run the macro until the end of data rows.
Below is my code. Thanks for any help!

=======================
'Declare the Excel Object
Dim xlApp As Object, xlWorkbook As Object, xlSheet As Object, MyRange As Object
Set xlApp = CreateObject("excel.application")
xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
xlApp.Visible = True

xlApp.Workbooks.Open FileName:="C:\Documents and Settings\pp28569\Desktop\exception_for_notes.xls"

Set xlSheet = xlApp.worksheets("Sheet1")
'=======exception 1
Set cus1 = xlapp.activesheet.Range("A2") '
Set fac1 = xlapp.activesheet.Range("B2") '
Set obl1 = xlapp.activesheet.Range("C2") '
Set reqst1 = xlapp.activesheet.Range("D2") '
Set mstop1 = xlapp.activesheet.Range("E2")
Set amt1 = xlapp.activesheet.Range("F2")
Set date1 = xlapp.activesheet.Range("G2") '
'=======exception 2
Set cus2 = xlapp.activesheet.Range("A3") '
Set fac2 = xlapp.activesheet.Range("B3") '
Set obl2 = xlapp.activesheet.Range("C3") '
Set reqst2 = xlapp.activesheet.Range("D3") '
Set mstop2 = xlapp.activesheet.Range("E3")
Set amt2 = xlapp.activesheet.Range("F3")
Set date2 = xlapp.activesheet.Range("G3") '
'=======exception 3
Set cus3 = xlapp.activesheet.Range("A4") '
Set fac3 = xlapp.activesheet.Range("B4") '
Set obl2 = xlapp.activesheet.Range("C4") '
Set reqst3 = xlapp.activesheet.Range("D4") '
Set mstop3 = xlapp.activesheet.Range("E4")
Set amt3 = xlapp.activesheet.Range("F4")
Set date3 = xlapp.activesheet.Range("G4") '

'======switch from PNC General Customer Information screen to Collateral Create screen
'Sess0.Screen.Sendkeys("<Pf12>")
' Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Sess0.Screen.Sendkeys("<Pf12>")
'Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Sess0.Screen.Sendkeys("<Pf8>")
'Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Sess0.Screen.PutString "s", 9, 24
'Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Sess0.Screen.SendKeys("<Enter>")
'Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Sess0.Screen.PutString "s", 8, 24
'Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Sess0.Screen.SendKeys("<Enter>")
'Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

'--------START INPUT exception 1

Sess0.Screen.PutString(cus1) 5, 42
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(fac1) 6, 42
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(obl1) 7, 42
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString "005", 11, 42 '================ exception code 005
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys ("<Tab><Tab><Tab>0898") '------our cost center here
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Tab>TN<Pf8>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Pf8><Pf8>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(reqst1) 5, 18
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(mstop1) 10, 18
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(amt1) 11, 18
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(date1) 12, 18

'Sess0.Screen.Sendkeys("<Enter>") '============= enter to input exception here
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Sess0.Screen.Sendkeys("<Pf12>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Sess0.Screen.Sendkeys("<Pf12>")

'=========================================exception 2
If xlSheet.Cells(3,1).value > 0 Then
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(cus2) 5, 42
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(fac2) 6, 42
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(obl2) 7, 42
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString "005", 11, 42 '================ exception code 005
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys ("<Tab><Tab><Tab>0898") '------our cost center here
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Tab>TN<Pf8>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Pf8><Pf8>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(reqst2) 5, 18
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(mstop2) 10, 18
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(amt2) 11, 18
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(date2) 12, 18

'Sess0.Screen.Sendkeys("<Enter>") '============= enter to input exception here
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Sess0.Screen.Sendkeys("<Pf12>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Sess0.Screen.Sendkeys("<Pf12>")
End If
'=========================exception 3
If xlSheet.Cells(4,1).value > 0 Then
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(cus3) 5, 42
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(fac3) 6, 42
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(obl3) 7, 42
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString "005", 11, 42
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.SendKeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys ("<Tab><Tab><Tab>0898")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Tab>TN<Pf8>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Pf8><Pf8>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(reqst3) 5, 18
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(mstop3) 10, 18
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(amt3) 11, 18
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.PutString(date3) 12, 18

'Sess0.Screen.Sendkeys("<Enter>") '============= enter to input exception here
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Sess0.Screen.Sendkeys("<Pf12>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'Sess0.Screen.Sendkeys("<Pf12>")
End If

 

[SkipVought]

hi,

Here's an example of the first 3 puts. Should give you an idea of looping thru the values in a table on an Excel sheet...

  dim r as object, iCol as integer

  Set xlSheet = xlApp.worksheets("Sheet1")
  
  with xlsheet
    for each r in .range(.[A2], .[A2].end(xldown))
      Sess0.Screen.PutString(r.value) 5, 42
      Sess0.Screen.PutString(r.offset(0,1).value) 6, 42
      Sess0.Screen.PutString(r.offset(0,2).value) 7, 42
      Sess0.Screen.PutString(r.offset(0,3).value) 11, 42
'.....
    next
  end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[poem2004]

First, thanks for your tip Skip!
There is a syntax error on this line:

for each r in .range(.[A2], .[A2].end(xldown))

Should the format has an equal sign and "to" ?
How do I use iCol that declared?

I'll try to work around the code...

Thanks again.

 

[SkipVought]

if you do not have a reference set to the Microsoft Excel n.m Object Library, then you cannot use excel CONSTANTS like xldown value is -4121.

The correct syntax for objects in a collection is For Each...Next.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]