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 bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Efficiency & Speed of code Excel- Attachmate Extra V93

Status
Not open for further replies.
jgarvey23

jgarvey23

Technical User
Jul 9, 2015
10
US
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
Code: 
Sess0.Screen.PutString Pull, Row#, Col#
instead of
Code: 
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?

Code: 
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
           
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''++++++++++++++++''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
Sort by date Sort by votes
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.

Code: 
'
                   .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
 
Upvote 0 Downvote
Skip,

Thank you for the quick response. I tried to use
Code: 
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
Code: 
Pull = .Cells(i, "A").Text
Sess0.Screen.PutString Pull 5,8
Sess0.Screen.MoveKeysTo 5,8

9,8 would now become 14,77
Code: 
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?
 
Upvote 0 Downvote
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...
Code: 
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...
Code: 
Scrn.SendKeys ("<ENTER>")
Do Until (Scrn.WaitForCursor(rw, cl))   'rw, cl are screen rest coordinates
   DoEvents
Loop
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

247xavi
  • Locked
  • Question
Attachmate to BlueZone
Replies
0
Views
362
247xavi
247xavi
nightrider69
  • Locked
  • Question
Micro mainframe transmission issues using Attachmate myExtra
Replies
0
Views
431
nightrider69
nightrider69
S
  • Locked
  • Question
Reflection Workspace extract all screen/pages
Replies
0
Views
728
SSS27
S
CADD6953
  • Locked
  • Question
Help needed with syntax/logic in Attachmate macro
2
Replies
26
Views
1K
SkipVought
S
FreCard
  • Locked
  • Question
Extra macro example
Replies
3
Views
606
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top