Attachmate to Excel
Attachmate to Excel
(OP)
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 !
Talk To Other Members
RE: Attachmate to Excel
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 LoopSecond, 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 Screen Navigation logic is the very first thing you need to design: 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!
RE: Attachmate to Excel