×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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

How do I make it run faster?

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

RE: How do I make it run faster?

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 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] 
But my question is, does the cursor actually appear at 1,1 for this screen.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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