Efficiency & Speed of code Excel- Attachmate Extra V93

[jgarvey23]

All,

I need to make the below code more efficient and process at a faster speed. 800 Milliseconds is the fastest rate as which Attachmate can keep up with Excel. I tried using

Sess0.Screen.PutString Pull, Row#, Col#

instead of

Pull = .Cells(i, "B").Text              ''Closing Price
           Sess0.Screen.SendKeys ("<Tab><Tab>")
           Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
           Sess0.Screen.PutString Pull

Unfortunately, the Row & Column would never match up after data was input.

Any suggestions?

Sub cRAZY()
Dim Sessions, System As Object, Sess0 As Object

Dim StartTime As Double
Dim MinutesElapsed As String

'Remember time when macro starts
  StartTime = Timer

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


g_HostSettleTime = 800    ' milliseconds

    OldSystemTimeout& = System.TimeoutValue
        If (g_HostSettleTime > OldSystemTimeout) Then
            System.TimeoutValue = g_HostSettleTime
        End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''++++++++++++++++++++'''''''''''''''''''''''''''''''''''''
'Activate Corp Loading Sheet & Format Date Columns
        Sheets("Corp Loading").Activate
                 Range("B2:C300").Select
                    Selection.Value = Selection.Value
                        Selection.NumberFormat = "yyyymmdd"
                        
'Activate Muni Loading Sheet & Format Date Columns
        Sheets("Muni Loading").Activate
                Range("B2:C300").Select
                    Selection.Value = Selection.Value
                        Selection.NumberFormat = "yyyymmdd"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''+++++++++++++++++++''''''''''''''''''''''''''''''''''''''''
            
Sess0.Screen.PutString "EDITSEC", 23, 19
Sess0.Screen.SendKeys ("<Enter>")
 Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

With Worksheets("Muni Loading")

Dim Pull As String

Do
    For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row

           Pull = .Cells(i, "I").Text
           Sess0.Screen.PutString Pull, 12, 7
           Sess0.Screen.SendKeys ("<Enter>")
           Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
            Sess0.Screen.MoveTo 13, 38
            Sess0.Screen.SendKeys ("<Enter>")
            Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
            Sess0.Screen.MoveTo 12, 37
            Sess0.Screen.SendKeys ("<Enter>")
            Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
            Sess0.Screen.MoveTo 6, 2
            Sess0.Screen.SendKeys ("<Enter>")
            Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
            Sess0.Screen.MoveTo 13, 38
            Sess0.Screen.SendKeys ("<Enter>")
            Sess0.Screen.WaitHostQuiet (1000)
            
        
            Pull = .Cells(i, "A").Text              ''Closing Price
            Sess0.Screen.PutString Pull
            Sess0.Screen.SendKeys ("<Tab><Tab><Tab>")
           Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                        
            Sess0.Screen.PutString "50"

           Pull = .Cells(i, "B").Text              ''Closing Price
           Sess0.Screen.SendKeys ("<Tab><Tab>")
           Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
           Sess0.Screen.PutString Pull
           
           Pull = .Cells(i, "D").Text              ''Closing Price
           Sess0.Screen.SendKeys ("<Tab><Tab>")
           Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
           Sess0.Screen.PutString Pull
 
           Pull = .Cells(i, "C").Text              ''Maturity Date
           Sess0.Screen.SendKeys ("<Tab><Tab>")
           Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
           Sess0.Screen.PutString Pull
           Sess0.Screen.SendKeys ("<Tab><Tab><Tab><Tab><Tab><Tab><Tab>")
           Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

           Pull = .Cells(i, "E").Text              ' M Rating
           Sess0.Screen.PutString Pull
           Sess0.Screen.SendKeys ("<Tab>")
           Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

           Pull = .Cells(i, "F").Text              'S&P Rating
           Sess0.Screen.PutString Pull
           Sess0.Screen.SendKeys ("<Tab>")
           Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
 
           Pull = .Cells(i, "G").Text              ''Coupon Rate
           Sess0.Screen.PutString Pull
           Sess0.Screen.SendKeys ("<Tab><Tab>")
           Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
           
           Pull = .Cells(i, "H").Text              'STATE
           Sess0.Screen.PutString Pull
        
           Sess0.Screen.SendKeys ("<Ctrl+[>[010q<Ctrl+[>[B<Ctrl+M>")   'Hit F10, Select Yes Option to save
                        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)                   'Wait
                    Sess0.Screen.SendKeys ("<Ctrl+[>[003q<Ctrl+[>[B<Ctrl+M>")   'Hit Escape, Select Yes to add another Security
            Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
            
    Next i
Exit Do

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

   End With
   
'Determine how many seconds code took to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")

'Notify user in seconds
  MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation
   
End Sub
           
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''++++++++++++++++''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[SkipVought]

hi,
[pre]
PutString
This method writes text to the session presentation space beginning at the row and column
specified. If a protected field is encountered while writing, characters from the text string are discarded, and writing continues with the next unprotected field.

Syntax
PutString inText, inRow, inColumn
[/pre]

row and column are not optional arguments!

Since you are TABBING, I guess to get to position the cursor to the next field, all you need to
do is COPY 'n' PASTE.

Also TABBING does not require a delay! Only when you SendKeys to the mainframe, do you need to delay UNTIL THE MAINFRAM RESPONDS.

'
                   .Cells(i, "B").Copy              ''Closing Price
                   Sess0.Screen.SendKeys ("<Tab><Tab>")
                   Sess0.Screen.Paste
                   
                   .Cells(i, "D").Copy              ''Closing Price
                   Sess0.Screen.SendKeys ("<Tab><Tab>")
                   Sess0.Screen.Paste

 

[jgarvey23]

Skip,

Thank you for the quick response. I tried to use

Pull = .Cells(i, "A").Text
 Sess0.Screen.PutString Pull 5,8

& it worked fine. Unfortunately, after inputting the first set of Instring, Inrow, Incolumn coordinates accompanied by data from Excel, the next set of coordinates would be thrown off. I tried to compensate by USING

Pull = .Cells(i, "A").Text
Sess0.Screen.PutString Pull 5,8
Sess0.Screen.MoveKeysTo 5,8

9,8 would now become 14,77

Pull = .Cells(i, "A").Text
Sess0.Screen.PutString Pull 9,8

Do I have to compensate for the data I input using the putstring in regards to the InRow, InColumn?

 

[SkipVought]

I think that you are confusing two different methods of placing data on the screen.

1) the coordinates method: This is the method that I almost always used.
Using PutString you can place text at any coordinates, one after another. For instance, suppose that you wanted to put 50 in column 2 for rows 8 to 10...

For r = 8 to 10
  Scrn.PutString "50", r, 2
Next

Notice that the cursor is ignored.

2) the cursor method: TAB from one data entry field to the next.

I don't like this method, because it is not as direct as using coordinates. Besides, I also use GetString to get text from the screen (and from places that you cannot TAB to), so it seems to me much more logical to use coordinates all around and simply ignore the cursor.

Keep this in mind as well: navigating the screen and manipulating data, is performed in-line with your code. One statement executes completely before any other statement is executed. This is a SYNCHRONOUS operation. HOWEVER, when you send data to to the mainframe, your program has no idea when the mainframe will respond. It performs ASYNCHRONOUSLY. So your code has to WAIT until the mainframe responds. It could be in milliseconds, minutes or longer. So I found that a LOOP was the best way to assure that I waited the proper amount of time. I use WaitForCursor() at the screen rest coordinates for that screen...

Scrn.SendKeys ("<ENTER>")
Do Until (Scrn.WaitForCursor(rw, cl))   'rw, cl are screen rest coordinates
   DoEvents
Loop