How do I make it run faster?

jujifruit783 · Jan 16, 2020

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

SkipVought · Jan 16, 2020

Hi,

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 [tt] sess0.screen.SendKeys ("<PF8>")[/tt] 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]

But my question is, does the cursor actually appear at 1,1 for this screen.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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!

How do I make it run faster?

jujifruit783

Technical User

SkipVought

Programmer

Similar threads

Part and Inventory Search

Sponsor