Loop not working!

Biznez · Jun 23, 2015

Code below extracts the data for the first account # that is in column A but does not continue the loop to goto next account #. Column A has the accounts numbers and column D would be the data that is extracted from mainframe into excel

Code:

Sub Extract()

Dim Sessions, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession


   With Worksheets("Template")

       Row = 2
 Do

           Pull = .Cells(Row, "A").Value
           Sess0.Screen.putstring Pull, 12, 43
           Sess0.Screen.SendKeys ("<Enter>")
           Sess0.Screen.WaitHostQuiet (100)
           .Range("D1").Offset(1).Value = Trim(Sess0.Screen.GetString(7, 2, 25))
           Sess0.Screen.SendKeys ("<Clear>")
           
Exit Do

           Row = Row + 1
       Loop Until .Cells(Row, "A").Value <> ""

   End With

End Sub

SkipVought · Jun 23, 2015

Hi,

Well here's what you're doing:
[pre]
start loop
Assign value from Excel column A
Put that value in screen
SendKeys
Wait 100 ms regardless if the mainframe has processed the entry (which is good)
or NOT (which is bad, cuz you'll never get a valid value to put in Excel column D)
Put a screen value in Excel column D
Clear

EXIT THE LOOP!!!!!!!!
[/pre]

You only process the FIRST row!!!

Biznez · Jun 23, 2015

crap thats right...got it working now but one problem. The value that is extracted from Mainframe to column D keeps getting overwritten and does not move to next row

Code:

Sub Extract()

Dim Sessions, System As Object, Sess0 As Object

Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession

With Worksheets("Template")

Do
    For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row
           Pull = .Cells(i, "A").Value
           Sess0.Screen.putstring Pull, 12, 43
           Sess0.Screen.SendKeys ("<Enter>")
           Sess0.Screen.WaitHostQuiet (100)
           .Range("D1").Offset(1).Value = Trim(Sess0.Screen.GetString(7, 2, 25))
           Sess0.Screen.SendKeys ("<Clear>")
    Next i
Exit Do

           Row = Row + 1
       Loop Until .Cells(Row, "A").Value <> ""

   End With

End Sub

Biznez · Jun 23, 2015

got it working now. replaced

Code:

.Range("D1").Offset(1).Value = Trim(Sess0.Screen.GetString(7, 2, 25))

to

Code:

.Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Trim(Sess0.Screen.GetString(7, 2, 25))

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Loop not working!

Biznez

Technical User

SkipVought

Programmer

Biznez

Technical User

Biznez

Technical User

Similar threads

Part and Inventory Search

Sponsor