Attachmate to Excel

lankaw · Sep 17, 2015

Hi Guys

Newbie to attachmate here . learnt everything about it off theses forums *thanks for the great work ! *

so to the problem at hand ....

I have written a simple code (below) to pull some client numbers off coordinates on attachmate and copy it into cells in excel. The marco works on my pc

but when i send the excel file to a colleague of mine it doesn't pull the data i want and gives me data that is no where near the coordinates or sometimes it gets them partially which just leaves me confused.

What ive looked into are the reference libraries and playing around with the WaitHostQuiet times. I'm sorta stumped ....is there something i'm not seeing ?

--------------------Code in my spreadsheet----------------

Sub ExtractingClientNumber_from_225Sheet1()
With Sheets("Sheet1")

Dim Sessions As Object
Dim System As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession

Sess0.Screen.WaitHostQuiet (10)
Sess0.Screen.SendKeys ("<Tab>3<Enter>")
Sess0.Screen.WaitHostQuiet (10)

' Copy Client Number from 7/33
.Cells(30, 2).Value = Sess0.Screen.GetString(7, 33, 8)
Sess0.Screen.WaitHostQuiet (10)

' Client Name from 9/31
.Cells(30, 4).Value = Sess0.Screen.GetString(9, 31, 25)

Sess0.Screen.SendKeys ("<pf12>")

Sess0.Screen.WaitHostQuiet (10)
Sess0.Screen.SendKeys ("<Tab><Tab>3<Enter>")
Sess0.Screen.WaitHostQuiet (10)

' Copy Client Number from 7/33
.Cells(31, 2).Value = Sess0.Screen.GetString(7, 33, 8)
Sess0.Screen.WaitHostQuiet (10)

' Client Name from 9/31
.Cells(31, 4).Value = Sess0.Screen.GetString(9, 31, 25)
Sess0.Screen.WaitHostQuiet (10)

Sess0.Screen.SendKeys ("<pf12>")

End With
End Sub

---------------------------------------

let me know what you think

thank you !

SkipVought · Sep 17, 2015

Hi,

Good for you to code in Excel VBA, which has much better editing and debugging features!

I've got several issues with your code.

First, after you issue a SendKeys that sends the screen data off to the mainframe to process ASYNCHRONOUSLY, (meaning it could take 1 millisecond or 1 second or 1 minute or...??? to return data to the next screen), your code waits for 10 milliseconds. This is like you deciding when you take off in your car for work, that you'll wait for 10 seconds at every intersection before proceeding through the intersection at full throttle. Hmmmmmmm? I rather use a technique where I wait until the cursor returns to the Screen Rest Coordinates...

Code:

'
        Sess0.Screen.SendKeys ("<Tab>3<Enter>")
        Do Until Sess0.Screen.WaitForCursor(r, c)       'r,c is are the REST COORDINATES for this screen
            DoEvents
        Loop

Second, you are doing 2 loops through your screen to put data into your sheet in columns B & D. You need to design your code to process an indefinite number of pages, looking for a MESSAGE that indicates that there is no more data to retrieve. This would be done in a loop that checks in each iteration, whether to Exit the loop if there is no more data. AND in your sheet, you need to find the next available row, rather than hard coding a row number.

In fact, the [highlight #FCE94F]Screen Navigation logic is the very first thing you need to design[/highlight]: what to do when various MESSAGES appear in the MESSAGE AREA of your screen. YOU know how your screen operates, so I cannot specify how this would work.

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

for a NUance!}

lankaw · Sep 21, 2015

Thanks Skip for the advice , i have put those techniques to use and am revamping the code with this advice. Will post the solution when i am done for critique.

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!

Attachmate to Excel

lankaw

Programmer

SkipVought

Programmer

lankaw

Programmer

Similar threads

Part and Inventory Search

Sponsor