how to loop thru rows?

[mizsydney]

sorry so dumb - I got so excited when Skip helped me fix the last macro, that I thought I could expand on it on my own. HAHA! big mistake.

the intent is to collect 2 cells from Excel, send to extra, enter and wait for an update screen, then paste another cell and enter. this should be able to loop thru the spreadsheet until it finds a blank cell (right now limited to 20 rows). columns won't change, and rows start at 4 and end at 25.

this code sorta works, but I can't get the loop language right. I can get it to work fine for the first row, but that's it.

I've read several postings on looping from excel to extra, and I just don't understand enough to make it work for me. I've read the Help files, but they seem to require some existing knowledge that I do not possess.

I think if someone can help me figure out the loop thing I can get this working. could someone please help me in the right direction? TIA!!

' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$
'
'
'   update narrative
'
'
Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
    Dim Sessions As Object
    Dim System As Object
    Set System = CreateObject("EXTRA.System")    ' Gets the system object
    If (System is Nothing) Then
        Msgbox "Could not create the EXTRA System object.  Stopping macro playback."
        STOP
    End If
    Set Sessions = System.Sessions
 
    If (Sessions is Nothing) Then
        Msgbox "Could not create the Sessions collection object.  Stopping macro playback."
        STOP
    End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
    g_HostSettleTime = 100        ' milliseconds
 
    OldSystemTimeout& = System.TimeoutValue
    If (g_HostSettleTime > OldSystemTimeout) Then
        System.TimeoutValue = g_HostSettleTime
    End If
 
' Get the necessary Session Object
    Dim Sess0 As Object
    Set Sess0 = System.ActiveSession
    If (Sess0 is Nothing) Then
        Msgbox "Could not create the Session object.  Stopping macro playback."
        STOP
    End If
    If Not Sess0.Visible Then Sess0.Visible = TRUE
    Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
'
'-------------------------------------------------------------------------------- starts here
'
Dim MyScreen As Object
Set myscreen = Sess0.Screen
'
'   -------------------------------------------fetch and check for logged-on SMUP screen
'
'
  Sess0.Screen.Sendkeys("<Clear>")
  Do While Sess0.Screen.OIA.Xstatus <> 0
           DoEvents
            Loop  
    Do While Sess0.Screen.OIA.XStatus <> 0 : Loop        
  
Sess0.Screen.MoveTo 1, 1
Sess0.Screen.Sendkeys("SMUP<Enter>")
  Do While Sess0.Screen.OIA.Xstatus <> 0
           DoEvents
            Loop  

  
    v1$ = MyScreen.GetString(1, 2, 4)
    if v1$ <> "SMUP" then
           'no -> error!!!
            msgbox "oops!  Log onto SMUP first."
       exit sub
    end if
   
    
'---------------------------------------------------------get info from excel file
 
'Declare the Excel Object

        Dim obj as object
        Dim xlApp As Object, xlSheet As Object, MyRange As Object
        Set xlApp = CreateObject("excel.application")
        
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        
        Set obj = Getobject("G:\Property Transfers\Programs\PT macros\MH\narr.xls")
      
    
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = False 'Turn off Warning Messages'
        
    Set xlSheet = obj.Worksheets("Sheet1") 
    
    xlApp.Visible = True
 
'-------------------------------------------------------------------------------- seems to go south here      
    With xlApp.ActiveSheet  
    
    

  
  
Dim APN 
Dim SessSM as object
Set myscreen = Sess0.Screen  


'--------------------------------------------------------- get the new info  
       
        
        Set APN = xlsheet.Range("G4")  'get APN
        
        Set yr = xlsheet.Range("A4")   'get year
        
        Set nar = xlsheet.Range("H4")  'get narr
        
        usr = "JUD"

     
 


        
'---------------------------------------------------------call up the record to change             
          
    Sess0.Screen.putstring(APN) 1, 7     'send APN


    Sess0.Screen.putstring(yr) 2,24    'send roll year
   
        Sess0.Screen.Sendkeys("<Enter>")
        
  Do While Sess0.Screen.OIA.Xstatus <> 0
           DoEvents
            Loop  

   End With    
    Sess0.Screen.WaitForString "SMUP",1,2,4
   


'<---------------------------------------------------send update codes & initials
'
Sess0.Screen.putstring("u") 2, 65
Sess0.Screen.putstring(usr) 4, 52 '<------------------initials

        Sess0.Screen.Sendkeys("<Enter>")
        
  Do While Sess0.Screen.OIA.Xstatus <> 0
           DoEvents
            Loop  


    Sess0.Screen.WaitForString "ENTER",12,24
      

Sess0.Screen.putstring("adr") 2, 74 '------------------send reason code


'<---------------------------------------------------  blank field text and send new text to smup screen
 Sess0.Screen.MoveTo 8,14
Sess0.Screen.Sendkeys("<EraseEOF>")    
Sess0.Screen.putstring(L1) 5, 14 
Sess0.Screen.putstring("*") 8, 74 




'<---------------------------------------------------  enter changes
'
 Sess0.Screen.Sendkeys("<Enter>")

  Do While Sess0.Screen.OIA.Xstatus <> 0
           DoEvents
            Loop 
            
 Sess0.Screen.WaitForString "UPD", 24, 30



'<--------------------------------------------------  need loop to next XL line here





    Set MyRange = xlSheet.Range("A:A")
    
    
    dim Row As Long, rw as long
    rw=5
    do
     
   

  
Set MyRange = xlsheet.Range(xlsheet.[g4:h25])

For Rows = 4 To 25
if MyRange.Rows(Rows).Value = "" Then Exit Sub
Sess0.Screen.PutString MyRange.Rows(Rows).Value, rw, 18
if rw = 22 then
rw = 5
else
rw = rw + 1
end if
      

   Sess0.Screen.Sendkeys("<Enter>")    
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Next Rows

loop
 









'
'<---------------------------------------------------  End Program
'
  Do While Sess0.Screen.OIA.Xstatus <> 0
           DoEvents
            Loop  
 '           
sess0.Screen.WaitHostQuiet(qt)
System.TimeoutValue = OldSystemTimeout




exit sub
  



end sub

 

[SkipVought]

Hi,

Try sonething like this, but use the screen column for the column H value that works for you...

    Dim r As Range, rw As Integer, co As Integer, i As Integer
    
    With xlsheet
        rw = 5
        For Each r In Range(.[G4], .[G4].End(xlDown))
            MsgBox "this is the value in column G: " & r.Value
            MsgBox "this is the value in column H: " & r.Offset(0, 1).Value
            For i = 0 To 1
                Select Case i
                    Case 0: co = 18[b]
                    Case 1: co = 50 'dunno, just picked a number[/b]
                End Select
                Sess0.Screen.PutString MyRange.Rows(Rows).Value, rw, co
            Next
            rw = rw + 1
        Next
    End With

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

 

[SkipVought]

oops, sorry

    Dim r As Range, rw As Long, co As Integer, i As Integer
    
    With xlsheet
        rw = 5
        For Each r In Range(.[G4], .[G4].End(xlDown))
            MsgBox "this is the value in column G: " & r.Value
            MsgBox "this is the value in column H: " & r.Offset(0, 1).Value
            For i = 0 To 1
                Select Case i
                    Case 0: co = 18
                    Case 1: co = 50 'dunno, just picked a number
                End Select
                Sess0.Screen.PutString [b]r.Offset(0, i).Value[/b], rw, co
            Next
            rw = rw + 1
        Next
    End With

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

 

[mizsydney]

thank you again for your assistance and your patience, Skip!

but I think you may be improperly assuming that I am not clueless. I am utterly lost.

I inserted your code and compiled - five bugs, in order -

range is not a record type
FOR syntax error
r is not a record type
r is not a record type (again)
r is not a record type (yet again)

>>>bug locations:

>>>Dim r As Range, rw As Long, co As Integer, i As Integer

With xlsheet
rw = 5
>>> For Each r In Range(.[G4], .[G4].End(xlDown))
>>> MsgBox "this is the value in column G: " & r.Value
>>> MsgBox "this is the value in column H: " & r.Offset(0, 1).Value
For i = 0 To 1
Select Case i
Case 0: co = 18
Case 1: co = 50 'dunno, just picked a number
End Select
>>> Sess0.Screen.PutString r.Offset(0, i).Value, rw, co
Next
rw = rw + 1
Next



sadly, I am writing this in Extra basic because my knowledge of Excel basic is even worse than my knowledge of Extra.

thanks again for your patience.

 

[SkipVought]

am writing this in Extra basic

THEN, you must reference ALL Excel object declarations to Excel...

    Dim r As Excel.Range, rw As Long, co As Integer, i As Integer

or...

    Dim r As Object, rw As Long, co As Integer, i As Integer

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

 

[mizsydney]

thanks Skip!

 

[mizsydney]

I'm sorry, I am just not smart enough to get this to work.

could someone please explain how the loop works, maybe using small words? I know what I want the macro to do, I just don't understand how to make it go thru more than one row. I've read thru other posts on looping until you hit a blank cell, and everyone seems to be doing it a little differently so I am unable to puzzle out how to apply to my situation.

my programming experience is very limited, and answers that seem really obvious to you guys are beyond my comprehension. I don't want to annoy anyone or test your patience!

 

[SkipVought]

Please post the code that you are using and explain what is not working.

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