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.

Jobs

Attachmate to Excel switch

Attachmate to Excel switch

(OP)
Hello,

This is my first post so be kind!

Below I have a very basic Macro I have recorded using Attachmate.


' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$


Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
Dim Sessions As Object
Dim System As Object
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System is Nothing) Then
Msgbox "Could not create the EXTRA System object. Stopping macro playback."
STOP
End If
Set Sessions = System.Sessions

If (Sessions is Nothing) Then
Msgbox "Could not create the Sessions collection object. Stopping macro playback."
STOP
End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 30 ' milliseconds

OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If

' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 is Nothing) Then
Msgbox "Could not create the Session object. Stopping macro playback."
STOP
End If
If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

System.TimeoutValue = OldSystemTimeout

' This section of code contains the recorded events
Sess0.Screen.MoveTo 3, 50
Sess0.Screen.Sendkeys("dlfp07<Pf2>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("Hello<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("Test<Enter>")

End Sub


This has worked fine for basic tasks (just adjusting the send keys portion).

However, what I have been doing in the past is pasting my data (to input in attachmate) in excel, creating a VBA macro to generate the code for attachmate, and then pasting the code into a separate macro in attachmate manually and then running the attachmate macro.

Now I have learnt that it is possible to do everything from Excel. I have adjusted the code to run directly from excel (changing global to public & setting the mainframe directory), however the code sometimes works when run once, but on the second time it always freezes! Meaning I have to manually end tasks for attachmate and excel and re-open both again. Below is the code I have posted in VBA. Is there something I am doing wrong? I believe it could be due to the host wait time but honestly I am not sure.

' Global variable declarations
Public g_HostSettleTime%
Public g_szPassword$

Sub Main()
'--------------------------------------------------------------------------------
' Get the main system object
Dim Sessions As Object
Dim System As Object


Set System = GetObject("", "EXTRA.System")
If System Is Nothing Then
Set System = CreateObject("EXTRA.System")
If (System Is Nothing) Then
MsgBox "Could not create the EXTRA System object. Stopping macro playback."
Stop
End If
End If
Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System Is Nothing) Then
MsgBox "Could not create the EXTRA System object. Stopping macro playback."
Stop
End If

Set Sessions = System.Sessions.Open("C:\Program Files (x86)\E!PC\Sessions\Mainfrme.edp")
If Sessions Is Nothing Then
Set Sessions = System.Sessions.Open("C:\Program Files (x86)\E!PC\Sessions\Mainframe.edp")
If MySession Is Nothing Then
Response = MsgBox("Could not create the EXTRA Session object", vbCritical, "EXTRA Session")
End
End If
End If



'--------------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 30 ' milliseconds

OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If

' Get the necessary Session Object
Dim Sess0 As Object
Set Sess0 = System.ActiveSession
If (Sess0 Is Nothing) Then
MsgBox "Could not create the Session object. Stopping macro playback."
Stop
End If
If Not Sess0.Visible Then Sess0.Visible = True
Sess0.Screen.WaitHostQuiet (g_HostSettleTime)

' This section of code contains the recorded events

System.TimeoutValue = OldSystemTimeout


'paste macro below
Sess0.Screen.MoveTo 3, 50
Sess0.Screen.Sendkeys("dlfp07<Pf2>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("Hello<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("Test<Enter>")


End Sub


Also as it is my first post, please tell me of any way to make my post visually easy to read. Looked for a "spoiler" button to hide the code but found no option.

Thank you very much!

RE: Attachmate to Excel switch

Vik12,

Hello, and welcome to Tek-Tips. You can find a wealth of knowledge here. Be sure to check forum68: Microsoft: Office and forum707: VBA Visual Basic for Applications (Microsoft) at a minimum, if you are an Excel user.

For code...

CODE

MsgBox "Hello world" 
...post like this

[code]
MsgBox "Hello world"
[/code]

...or look for the CODE ICON (4th from the right) in the toolbar above the posting window.

Regarding the freezing, could be any number of things. FIRST off, make it a practice to Close, Quit, Set to Nothing your object variables especially the Extra objects.

Regarding the use of g_HostSettleTime: realize that your terminal emulator is interfacing between your program and the mainframe computer in an ASYNCHRONOUS manner. Your send off a message and there's not telling when you will get a response. So as an analogy, would you leave the house for work and set your g_intersection_timeout to 30 seconds, so that each time you came to an intersection, you would stop for 30 seconds and then proceed through the intersection? NO! In some cases, there would be no need to stop. In other cases, you would need to stop. But in all cases, you would need feedback from the environment, to be assured that it was safe to proceed through the intersection. Similarly, with the code following a message sent to the mainframe that requires a response before executing subsequent lines of code.

Must go. Will return.

Skip,

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

RE: Attachmate to Excel switch

...continuing...

I use the Cursor as the indicator that the system is now ready to process...

CODE

With oScrn
        .SendKeys ("<enter>")
        Do Until .WaitForCursor(8, 1)
            DoEvents
        Loop
'........ 
So Enter sends a process command to the mainframe and the Cursor will rest a 8,1 when the system is ready. There is a loop that waits for the cursor to respond, not just a fixed wait time.

So make some changes and see if things improve.

Skip,

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

RE: Attachmate to Excel switch

(OP)
Hi Skip,

Fantastic, introduced the wait for cursor loop and it worked a treat, no more freezing!

However, there is another issue I am now having. Say for example I wanted to send keys to the screen such as:

Sess0.Screen.SendKeys (" Cells(9, "F").Value & <Enter>")

I want to send to the screen the value in cell F9 and press enter for the example.

I have had quite a few issues with this:

- The quotation marks seem to treat everything as text, which is working as normal, but I do not know how to get round this. (it is sending "Cells(9,.." to the screen instead of the value.

- I have set the value within that cell to a string name and put that name in but it still doesn't work.

I believe the main issue is the quotation marks, do you have any advice regarding this?

Thank you so much!

RE: Attachmate to Excel switch

CODE

Sess0.Screen.SendKeys (Cells(9, "F").Value & "<Enter>") 

Skip,

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

RE: Attachmate to Excel switch

(OP)
Duhhhh... of course! You have no idea how long I spent on this issuemad

Thank you Skip!!

RE: Attachmate to Excel switch

(OP)
Hi Skip,
The solution below that you suggested still isn't working, it is still freezing everytime meaning I have to close down excel and Attachmate. Any suggestions?

CODE

Do Until sess0.screen.WaitForCursor(24, 17)
            DoEvents
        Loop 

RE: Attachmate to Excel switch

So on this screen, the position where the cursor comes to rest after a SendKeys string is row 24 column 17?

Skip,

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

RE: Attachmate to Excel switch

(OP)
Well it should, but as soon as it runs that line of code, it freezes.

RE: Attachmate to Excel switch

So if you were to send the same value manually, how long does it take for the mainframe to respond at the 24,17 position.

Lastly, if you comment the SendKeys and following wait loop lines of code, does your code still lock up?

Skip,

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

RE: Attachmate to Excel switch

(OP)
Apologies for the late reply.

Usually it would take less than a second, but sometimes it can take over 4/5 seconds. Hence I wanted a method for the code to continue once the previous process had completed.

On your final note, yes it does still lock up.

Within the attachmate macro section, after each process I have:

CODE

Sess0.Screen.WaitHostQuiet(g_HostSettleTime) 

And this works flawlessly. Now moving over to VBA it freezes for some reason!

Is there any other line of code I can attempt to try to wait for the current process to finish?

RE: Attachmate to Excel switch

(OP)
I believe its an issue with the (wait) function, wait for cursor or string or wait on host are not working at all and causing freezing. Is there any alternative function which will do the same thing?

RE: Attachmate to Excel switch

The reason that I started using the WaitForCursor/loop method is that 1) I saw that I was loosing process/data with the (wait) method 2) I could find no documentation regarding the WaitHostQuiet method in order to understand how that worked and 3) the WaitForCursor/loop method seemed to solve my loosing process/data problem.

I can't sleuth your situation. In fact I don't even have access to a mainframe or the Attachmate!Extra emulator any more as I'm retired. I do have all my VBA that I used to access the Attachmate!Extra emulator: an entire workbook designed to store code and access dozens of screens.

This is your problem to solve. I can help here, from across the pond. It's going to take some digging. Here's what I'd do in Excel. Comment out everything but setting the System, Session and Screen objects. RUN. Then start uncommenting lines. RUN. When the problem reappears, you've located a problem. Keep in mind that you could have more than one contributing problem code line. If you discover something significant and make a substantive change that works better, be sure to document your findings/solution in comments so you don't forget and following coders understand.

I have an FAQ in forum707: VBA Visual Basic for Applications (Microsoft), regarding the Watch Window in the VBA editor. It is a powerful debugging tool. Won't be much good for this exercise, but the Watch Window can give you a window into the state of objects and variables in the Break mode for analysis. This forum can also help you with VBA related code questions.

Skip,

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

RE: Attachmate to Excel switch

(OP)
Thank you skip for the resources you have provided.

For anyone else reading this in the future with a similar problem such as myself, the solution is to use

CODE

Do While Sess0.Screen.OIA.Xstatus <> 0
    DoEvents
    Loop 

Instead of waiting for the processes to finish, this waits for the xclock at the bottom left of your screen to disappear. Pretty much the same as the WaitHostQuiet method, but directly referencing to the clock to disappear.

RE: Attachmate to Excel switch

Great! Glad you found a good solution.

Skip,

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

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!

Resources

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