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:
Sess0.Screen.PutString Pull, Row#, Col#
Code:
Pull = .Cells(i, "B").Text ''Closing Price
Sess0.Screen.SendKeys ("<Tab><Tab>")
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
Sess0.Screen.PutString Pull
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
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''++++++++++++++++''''''''''''''''''''''''''''''''''''''''''''''''''''''''''