How do I make it run faster?
How do I make it run faster?
(OP)
Hi,
I am entirely new to creating macros for Attachmates Extra. But I wanted to create a macros to pull data from the mainframe into an excel sheet. There is always a delay/pause after each loop so it takes some time to go through every mainframe page to extract the data onto my excel sheet. My script is below. Can you help me identify if there is a faster way of running this macro? Thank you.
Sub ExtractAllocation()
Dim sessions As Object 'create object
Dim system As Object 'create object
Dim sess0 As Object 'create object
Dim result As String 'create date type, data that is pasted to the cell in excel
Set system = CreateObject("extra.system") '3 set codes are used to call pplex
Set sessions = system.sessions
Set sess0 = system.activesession
Count = 11
ActiveSheet.Range("A12").Select
Do Until sess0.screen.WaitForCursor(1, 1)
Count = Count + 1
CustAcct = sess0.screen.getstring(12, 3, 7)
CustName = sess0.screen.getstring(13, 18)
BillDate = sess0.screen.getstring(12, 19, 6)
AdjQty = sess0.screen.getstring(12, 15, 2)
PrevQty = sess0.screen.getstring(13, 15, 2)
With ActiveSheet
.Cells(Count, "A").Value = CustAcct
.Cells(Count, "B").Value = CustName
.Cells(Count, "P").Value = BillDate
.Cells(Count, "D").Value = AdjQty
.Cells(Count, "E").Value = PrevQty
End With
Count = Count + 1
CustAcct = sess0.screen.getstring(14, 3, 7)
CustName = sess0.screen.getstring(15, 18)
BillDate = sess0.screen.getstring(14, 19, 6)
AdjQty = sess0.screen.getstring(14, 15, 2)
PrevQty = sess0.screen.getstring(15, 15, 2)
With ActiveSheet
.Cells(Count, "A").Value = CustAcct
.Cells(Count, "B").Value = CustName
.Cells(Count, "P").Value = BillDate
.Cells(Count, "D").Value = AdjQty
.Cells(Count, "E").Value = PrevQty
End With
Count = Count + 1
CustAcct = sess0.screen.getstring(16, 3, 7)
CustName = sess0.screen.getstring(17, 18)
BillDate = sess0.screen.getstring(16, 19, 6)
AdjQty = sess0.screen.getstring(16, 15, 2)
PrevQty = sess0.screen.getstring(17, 15, 2)
With ActiveSheet
.Cells(Count, "A").Value = CustAcct
.Cells(Count, "B").Value = CustName
.Cells(Count, "P").Value = BillDate
.Cells(Count, "D").Value = AdjQty
.Cells(Count, "E").Value = PrevQty
End With
Count = Count + 1
CustAcct = sess0.screen.getstring(18, 3, 7)
CustName = sess0.screen.getstring(19, 18)
BillDate = sess0.screen.getstring(18, 19, 6)
AdjQty = sess0.screen.getstring(18, 15, 2)
PrevQty = sess0.screen.getstring(19, 15, 2)
With ActiveSheet
.Cells(Count, "A").Value = CustAcct
.Cells(Count, "B").Value = CustName
.Cells(Count, "P").Value = BillDate
.Cells(Count, "D").Value = AdjQty
.Cells(Count, "E").Value = PrevQty
End With
Count = Count + 1
CustAcct = sess0.screen.getstring(20, 3, 7)
CustName = sess0.screen.getstring(21, 18)
BillDate = sess0.screen.getstring(20, 19, 6)
AdjQty = sess0.screen.getstring(20, 15, 2)
PrevQty = sess0.screen.getstring(21, 15, 2)
With ActiveSheet
.Cells(Count, "A").Value = CustAcct
.Cells(Count, "B").Value = CustName
.Cells(Count, "P").Value = BillDate
.Cells(Count, "D").Value = AdjQty
.Cells(Count, "E").Value = PrevQty
End With
sess0.screen.SendKeys ("<PF8>")
sess0.screen.WaitHostQuiet (g_HostSettleTime)
Loop
End Sub
I am entirely new to creating macros for Attachmates Extra. But I wanted to create a macros to pull data from the mainframe into an excel sheet. There is always a delay/pause after each loop so it takes some time to go through every mainframe page to extract the data onto my excel sheet. My script is below. Can you help me identify if there is a faster way of running this macro? Thank you.
Sub ExtractAllocation()
Dim sessions As Object 'create object
Dim system As Object 'create object
Dim sess0 As Object 'create object
Dim result As String 'create date type, data that is pasted to the cell in excel
Set system = CreateObject("extra.system") '3 set codes are used to call pplex
Set sessions = system.sessions
Set sess0 = system.activesession
Count = 11
ActiveSheet.Range("A12").Select
Do Until sess0.screen.WaitForCursor(1, 1)
Count = Count + 1
CustAcct = sess0.screen.getstring(12, 3, 7)
CustName = sess0.screen.getstring(13, 18)
BillDate = sess0.screen.getstring(12, 19, 6)
AdjQty = sess0.screen.getstring(12, 15, 2)
PrevQty = sess0.screen.getstring(13, 15, 2)
With ActiveSheet
.Cells(Count, "A").Value = CustAcct
.Cells(Count, "B").Value = CustName
.Cells(Count, "P").Value = BillDate
.Cells(Count, "D").Value = AdjQty
.Cells(Count, "E").Value = PrevQty
End With
Count = Count + 1
CustAcct = sess0.screen.getstring(14, 3, 7)
CustName = sess0.screen.getstring(15, 18)
BillDate = sess0.screen.getstring(14, 19, 6)
AdjQty = sess0.screen.getstring(14, 15, 2)
PrevQty = sess0.screen.getstring(15, 15, 2)
With ActiveSheet
.Cells(Count, "A").Value = CustAcct
.Cells(Count, "B").Value = CustName
.Cells(Count, "P").Value = BillDate
.Cells(Count, "D").Value = AdjQty
.Cells(Count, "E").Value = PrevQty
End With
Count = Count + 1
CustAcct = sess0.screen.getstring(16, 3, 7)
CustName = sess0.screen.getstring(17, 18)
BillDate = sess0.screen.getstring(16, 19, 6)
AdjQty = sess0.screen.getstring(16, 15, 2)
PrevQty = sess0.screen.getstring(17, 15, 2)
With ActiveSheet
.Cells(Count, "A").Value = CustAcct
.Cells(Count, "B").Value = CustName
.Cells(Count, "P").Value = BillDate
.Cells(Count, "D").Value = AdjQty
.Cells(Count, "E").Value = PrevQty
End With
Count = Count + 1
CustAcct = sess0.screen.getstring(18, 3, 7)
CustName = sess0.screen.getstring(19, 18)
BillDate = sess0.screen.getstring(18, 19, 6)
AdjQty = sess0.screen.getstring(18, 15, 2)
PrevQty = sess0.screen.getstring(19, 15, 2)
With ActiveSheet
.Cells(Count, "A").Value = CustAcct
.Cells(Count, "B").Value = CustName
.Cells(Count, "P").Value = BillDate
.Cells(Count, "D").Value = AdjQty
.Cells(Count, "E").Value = PrevQty
End With
Count = Count + 1
CustAcct = sess0.screen.getstring(20, 3, 7)
CustName = sess0.screen.getstring(21, 18)
BillDate = sess0.screen.getstring(20, 19, 6)
AdjQty = sess0.screen.getstring(20, 15, 2)
PrevQty = sess0.screen.getstring(21, 15, 2)
With ActiveSheet
.Cells(Count, "A").Value = CustAcct
.Cells(Count, "B").Value = CustName
.Cells(Count, "P").Value = BillDate
.Cells(Count, "D").Value = AdjQty
.Cells(Count, "E").Value = PrevQty
End With
sess0.screen.SendKeys ("<PF8>")
sess0.screen.WaitHostQuiet (g_HostSettleTime)
Loop
End Sub
Talk To Other Members
RE: How do I make it run faster?
It appears, from reading your code, that your screen has 10 rows of tabular data.
You have 2 Wait statements. The first one is misplaced. The second one is a wait of some undetermined constant length.
Let me address the second one first. The interaction between your program running on your PC in Attachmate and the mainframe computer is asynchronous, meaning that when you send a command to the mainframe like sess0.screen.SendKeys ("<PF8>") to paint the next page of data for the screen, the mainframe, operating independently of your PC, may take a millisecond or a second or 5 seconds or ??? No one knows until it happens. So your program needs a way to detect when the mainframe has completed its operation.
So you need more than one loop. You actually need 2 nested loops.
CODE
Do 'Put read/write here for 5 rows sess0.screen.SendKeys ("<PF8>") 'send command to mainframe Do Until sess0.screen.WaitForCursor(1, 1) 'wait until the mainframe is done DoEvents Loop Loop Until [you run out of pages for this screen]Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein