×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Efficiency & Speed of code Excel- Attachmate Extra V93

thread99-1752242

Efficiency & Speed of code Excel- Attachmate Extra V93

Efficiency & Speed of code Excel- Attachmate Extra V93

(OP)
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 --> VBA

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

CODE --> VBA

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 --> VBA

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
           
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''++++++++++++++++''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

RE: Efficiency & Speed of code Excel- Attachmate Extra V93

hi,

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

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

RE: Efficiency & Speed of code Excel- Attachmate Extra V93

(OP)
Skip,

Thank you for the quick response. I tried to use

CODE --> VBA

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 --> VBA

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

9,8 would now become 14,77

CODE --> VBA

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?

RE: Efficiency & Speed of code Excel- Attachmate Extra V93

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close