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 instead of Unfortunately, the Row & Column would never match up after data was input.
Any suggestions?
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#
CODE --> VBA
Pull = .Cells(i, "B").Text ''Closing Price Sess0.Screen.SendKeys ("<Tab><Tab>") Sess0.Screen.WaitHostQuiet (g_HostSettleTime) Sess0.Screen.PutString Pull
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
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
RE: Efficiency & Speed of code Excel- Attachmate Extra V93
Thank you for the quick response. I tried to use
CODE --> VBA
CODE --> VBA
9,8 would now become 14,77
CODE --> VBA
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
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
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