Attachmate to Excel switch

[Vik12]

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!

 

[SkipVought]

Vik12,

Hello, and welcome to Tek-Tips. You can find a wealth of knowledge here. Be sure to check Forum68 and Forum707 at a minimum, if you are an Excel user.

For code...

MsgBox "Hello world"

...post like this
[ignore]

MsgBox "Hello world"

[/ignore]
...or look for the CODE ICON (4[sup]th[/sup] 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...continuing...

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

    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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Vik12]

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!

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Vik12]

Duhhhh... of course! You have no idea how long I spent on this issue

Thank you Skip!!

 

[Vik12]

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?

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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Vik12]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Vik12]

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:

 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?

 

[Vik12]

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?

 

[SkipVought]

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, 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Vik12]

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

  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.

 

[SkipVought]

Great! Glad you found a good solution.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]