×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

Attachmate to Excel

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 !

RE: Attachmate to Excel

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 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,

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

RE: Attachmate to Excel

(OP)
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.

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