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!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

goto next loop 1

Status
Not open for further replies.
buko88

buko88

Programmer
Jul 20, 2016
8
0
0
GB
Hi guys,

I`m copying fields from excel to attachmate, I need to go to next loop if the value already exists in attachmate.

Code: 
Dim Row As Long
        With xlApp.ActiveSheet
           Set MyRange = .Range("A1:A65536").Resize(xlApp.CountA(.Range("A1:A65536")))
           Set MyRange2 = .Range("B1:B65530").Resize(xlApp.CountA(.Range("B1:B65530")))
        End With
        For Row = 1 To MyRange.Rows.Count
           Sess0.Screen.PutString MyRange.Rows(Row).Value, 7, 4     * insert first string from column A in excel
           Sess0.Screen.WaitHostQuiet 30
           Sess0.Screen.SendKeys "<ENTER>"
           If Sess0.Screen.GetString(20,2,8) = "SVIP210E" Then     * here if the string already exist i need to go to the next row in loop
                
                
           End If     
           Sess0.Screen.WaitHostQuiet 30
           Sess0.Screen.PutString MyRange2.Rows(Row).Value, 8, 33    * enter another string from column B in excel
           Sess0.Screen.SendKeys "<ENTER>"
           Sess0.Screen.WaitHostQuiet 30
           Sess0.Screen.SendKeys("<PF3>")
           Sess0.Screen.WaitHostQuiet 30
           
        Next Row




End Sub
 
Sort by date Sort by votes
hi,

Welcome to Tek-Tips y'all!

Code: 
'
    Dim Row As Long
    
    With xlApp.ActiveSheet
       Set MyRange = .Range("A1:A65536").Resize(xlApp.CountA(.Range("A1:A65536")))
       Set MyRange2 = .Range("B1:B65530").Resize(xlApp.CountA(.Range("B1:B65530")))
    End With
    
    For Row = 1 To MyRange.Rows.Count
       Sess0.Screen.PutString MyRange.Rows(Row).Value, 7, 4     ' insert first string from column A in excel
       Sess0.Screen.WaitHostQuiet 30
       Sess0.Screen.SendKeys "<ENTER>"
       If Sess0.Screen.GetString(20, 2, 8) [b][highlight #FCE94F]<>[/highlight][/b] "SVIP210E" Then   ' here if the string already exist i need to go to the next row in loop
            Sess0.Screen.WaitHostQuiet 30
            Sess0.Screen.PutString MyRange2.Rows(Row).Value, 8, 33    ' enter another string from column B in excel
            Sess0.Screen.SendKeys "<ENTER>"
            Sess0.Screen.WaitHostQuiet 30
            Sess0.Screen.SendKeys ("<PF3>")
            Sess0.Screen.WaitHostQuiet 30
       End If
       
    Next Row

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
Hi,

Thank you :)

I`m now experiencing strange behavior. When i run this step-by-step, it`s working how it should, but when I let it run it seems that the IF line is skipped and is processing lines that are inside if.

Any ideas how to resolve this?

 
Upvote 0 Downvote
You are interfacing with an asynchronous system. Off goes a command, like...
Code: 
Sess0.Screen.SendKeys "<ENTER>"
...and there is no guarantee when your mainframe will respond, but your code keeps plowing on. So you must WAIT until you get a response.

Some developers like to use...
Code: 
Sess0.Screen.SendKeys "<ENTER>"
Do While Sess0.Screen.OIA.XSataus <> 0
   DoEvents
Loop

I've also used Sess0.Screen.WaitForCursor(r,c) at the screen rest coordinates (r,c)

Check these out in your Extra VB HELP.


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
Hi,

with your help i managed to get it work almost perfect.
There is however this strange thing: i`m adding IPs, if one already exist, skip it, otherwise add mask. While it skips IPs that are already there, it sometmies (cca every 6th skip ) ad 2 random number after 3rd octet. so instead of checking 10.10.10.1 its checking 10.10.10.06.1
When I delete all record, its working fine and is adding IPs as they are in excel. Any Idea?

Thanks

Code: 
For Row = 1 To MyRange.Rows.Count
        
          
		
                Sess0.Screen.PutString MyRange.Rows(Row).Value, 7, 4
                Sess0.Screen.WaitHostQuiet 300
                Do While Sess0.Screen.OIA.XStatus <> 0
                 DoEvents
                  Loop
                Sess0.Screen.SendKeys (sendCommand & "<Enter>")
                Sess0.Screen.WaitHostQuiet 300
                Do While Sess0.Screen.OIA.XStatus <> 0
                 DoEvents
                  Loop
                

if Sess0.screen.getstring(20,2,8) <> "SVIP210E" then
if Sess0.screen.getstring(20,2,8) <> "SVIP808E"  then
   
   Sess0.Screen.PutString MyRange2.Rows(Row).Value, 8, 33
   Sess0.Screen.SendKeys (sendCommand & "<Enter>")
   Sess0.Screen.WaitHostQuiet 300
   Do While Sess0.Screen.OIA.XStatus <> 0
                 DoEvents
                  Loop
   Sess0.Screen.SendKeys("<PF3>")
   Sess0.Screen.WaitHostQuiet 300
   Do While Sess0.Screen.OIA.XStatus <> 0
                 DoEvents
                  Loop
end if    
end if           
    
Next Row
End Sub
 
Upvote 0 Downvote
The .06 is not popping up from NOTHING!

Your code, I assume, is adding it from SOMETHING!

How do these values come into being?

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
That what i`m trying to find out :D

When I change the value in excel, it will change that extra digits also.

72.5.190.1 in excel - 72.5.190.06.1 in attachmate
72.5.190.100 in excel - 72.5.190.1000 in attachmate
72.5.190.5 in excel - 72.5.190.56.0 in attachmate

After I have deleted this line from exel(line 6) now it is changing 165.225.96.0 to 165.225.36.0092 (line 18)
it is also inputing 92 after many IPs ending with 0 wich is not invalid and then adding this ip into system which is not good.
I have checked excel, it seems that formatting is OK in there.

Code: 
' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$
 
Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
    Dim Sessions As Object
    Dim System As Object
    Dim Host 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 = 3000        ' 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)
    
'--------------------------------------------------------------------------------
'Declare the Excel Object
        Dim xlApp As Object, xlSheet As Object, MyRange As Object, MyRange2 As Object
        Set xlApp = CreateObject("excel.application")
        xlApp.Application.DisplayAlerts = True
        xlApp.Visible = True
        xlApp.Workbooks.Open FileName:="C:\macros\att\Fetch.xls"
        Set xlSheet = xlApp.activesheet
        Set MyRange = xlApp.activesheet.Range("A:A")
        Set MyRange2 = xlApp.activesheet.Range("B:B")
 
Dim Row As Long
        With xlApp.ActiveSheet
           Set MyRange = .Range("A1:A65536").Resize(xlApp.CountA(.Range("A1:A65536")))
           Set MyRange2 = .Range("B1:B65530").Resize(xlApp.CountA(.Range("B1:B65530")))
        End With
        For Row = 1 To MyRange.Rows.Count
        
          

		
                Sess0.Screen.PutString MyRange.Rows(Row).Value, 7, 4
                Sess0.Screen.WaitHostQuiet 10
                Do While Sess0.Screen.OIA.XStatus <> 0
                 DoEvents
                  Loop
                Sess0.Screen.SendKeys (sendCommand & "<Enter>")
                Sess0.Screen.WaitHostQuiet 10
                Do While Sess0.Screen.OIA.XStatus <> 0
                 DoEvents
                  Loop
                

if Sess0.screen.getstring(20,2,8) <> "SVIP210E" then
if Sess0.screen.getstring(20,2,8) <> "SVIP808E"  then
   
   Sess0.Screen.PutString MyRange2.Rows(Row).Value, 8, 33
   Sess0.Screen.SendKeys (sendCommand & "<Enter>")
   Sess0.Screen.WaitHostQuiet 10
   Do While Sess0.Screen.OIA.XStatus <> 0
                 DoEvents
                  Loop
   Sess0.Screen.SendKeys("<PF3>")
   Sess0.Screen.WaitHostQuiet 10
   Do While Sess0.Screen.OIA.XStatus <> 0
                 DoEvents
                  Loop
end if    
end if           
    
Next Row
End Sub
 
Upvote 0 Downvote
ok,

I have found the issue, i was not deleting previous record and if new one was shorter the last one, it added the last digits.

Thanks for help :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

jeaneshe
  • Locked
  • Question
How do I select different Excel cells using a variable column loop?
Replies
10
Views
108
SkipVought
S
2009luca
  • Locked
  • Question
LOOP on cursor position....
Replies
1
Views
42
SkipVought
S
Tai Tran
  • Locked
  • Question
Putstring doesnt insert next to text
Replies
3
Views
39
SkipVought
S
Biznez
  • Locked
  • Question
Loop not working!
Replies
3
Views
42
Biznez
Biznez
jgarvey23
  • Locked
  • Question
Excel to Attachmate Extra. Copy &amp; Paste &amp; Loop
Replies
2
Views
42
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top