Using excel macro to open attachmate Reflection for UNIX and Open VMS Session

[Frank Melan]

I have searched the internet & have found how to use Attachmate to do stuff in excel, but nothing to open a file. The code I have so far acts like it is going to open, then hangs up on ActiveWorkbook.FollowHyperlink MyFolder. I also get the following error: Run-time error '-2147417851 (80010105), an unexpected error has occurred. I am using excel 2010, with an active x control. I have tried several variations of code, but this is the furthest, that I have gotten. Could you please let me know what I am doing wrong & how to correct it. I have reached out to another coder at work, & he is as stumped as I am. Here is the current code:

 Sub VISTA()

Dim Msg As String, Ans As Variant
     
    Msg = "Would you like to open VISTA?"
     
    Ans = MsgBox(Msg, vbYesNo)
     
    Select Case Ans
         
    Case vbYes
     Dim MyFolder As String
MyFolder = "C:\ProgramData\Attachmate\Reflection\R04-reflection.r2w"
 ActiveWorkbook.FollowHyperlink MyFolder
    End Select
End Sub

I have edit the code that you provided. Here is the current code, I have highlighted the line it hangs on:

Sub VISTA()

Dim Msg As String, Ans As Variant
     
    Msg = "Would you like to open VISTA?"
     
    Ans = MsgBox(Msg, vbYesNo)
     
    Select Case Ans
         
    Case vbYes
    
'
    Set oSystem = CreateObject("Reflection.System")
    
    If oSystem.Sessions.Count = 0 Then
        Set oSess = oSystem.Sessions.Open("C:\ProgramData\Attachmate\Reflection\R04-reflection.r2w")
    Else
        Set oSess = oSystem.ActiveSession
    End If
    
    With oSess
        .Visible = True
        .WindowState = xNORMAL
    End With

    Set oScrn = oSess.Screen
   [highlight #CC0000] If (oScrn Is Nothing) Then GoTo Exit Macro[/highlight]
    
    With oScrn
            .Area(17, 28, 17, 28) = "S"
            .SendKeys ("<ENTER>")

            Do Until .WaitForCursor(14, 37)
                DoEvents
            Loop
    End With

    
    
    
     'Dim MyFolder As String
'MyFolder = "C:\ProgramData\Attachmate\Reflection\R04-reflection.r2w"
 'ActiveWorkbook.FollowHyperlink MyFolder
    End Select
End Sub

 

[SkipVought]

Hi,

Never used Attachmate Reflection. Did use Attachmate Extra as a 3270 terminal emulator.

I always opened and ran Extra from Excel, since I was most often processing a list from Excel to generate another list to put into Excel to pass on to a user. Excel VBA is so mu easier to use than Attachmate VB.

So here's code that worked to open Extra. Modify as required.

'you must have a reference set for Attachmate EXTRA! n.m Object Library

Public oSystem As ExtraSystem
Public oSessions As ExtraSessions
Public oSess As ExtraSession
Public oScrn As ExtraScreen

'
    Set oSystem = CreateObject("Extra.System")
    
    If oSystem.Sessions.Count = 0 Then
        Set oSess = oSystem.Sessions.Open("C:\Program Files\E!PC\Sessions\Mainframe.edp")
    Else
        Set oSess = oSystem.ActiveSession
    End If
    
    With oSess
        .Visible = True
        .WindowState = xNORMAL
    End With

    Set oScrn = oSess.Screen
    If (oScrn Is Nothing) Then GoTo ExitMacro
    
    With oScrn
            .Area(17, 28, 17, 28) = "S"
            .SendKeys ("<ENTER>")

            Do Until .WaitForCursor(14, 37)
                DoEvents
            Loop
    End With
'...

BTW, welcome to Tek-Tips. You'll find lots of good stuff here. And born & raised in the keystone state, though now living in the lone star state.

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

 

[Frank Melan]

Sorry it has taken me this long to respond. I modified your code & get "Invalid attribute in Sub or Function" on Private. I checked & MS suggested either changing to Public or removing it. I tried both & got other error messages. I think I am missing something in my code, but not certain exactly what it is. This is the hardest thing I have had to do (63), since I have had no code training & have been taught by others or stumbling across pieces of code. Here is what I currently have:

Dim Msg As String, Ans As Variant
     
    Msg = "Would you like to open VISTA?"
     
    Ans = MsgBox(Msg, vbYesNo)
     
    Select Case Ans
         
    Case vbYes
'you must have a reference set for Attachmate EXTRA! n.m Object Library

Private oSystem As ReflectionSystem
Private oSessions As ReflectionSessions
Private oSess As ReflectionSession
Private oScrn As ReflectionScreen

'
    Set oSystem = CreateObject("Reflection.System")
    
    If oSystem.sessions.Count = 0 Then
        Set oSess = oSystem.sessions.Open("C:\ProgramData\Attachmate\Reflection\R04-reflection.r2w")
    Else
        Set oSess = oSystem.ActiveSession
    End If
    
    With oSess
        .Visible = True
        .WindowState = xNORMAL
    End With

    Set oScrn = oSess.Screen
    If (oScrn Is Nothing) Then GoTo ExitMacro
    
    With oScrn
            .Area(17, 28, 17, 28) = "S"
            .SendKeys ("<ENTER>")

            Do Until .WaitForCursor(14, 37)
                DoEvents
            Loop
    End With
'...
    
End Select
End Sub

 

[SkipVought]

Sorry, the Public declarations must be at the Module level, while the executable code must be in a Sub Procedure.

Public oSystem As Object
'.....
Sub Main()
'Your code
End Sub

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

 

[Frank Melan]

LOL. I didn't even think about a module. When you miss that, you know it has been a long day. Is there any thing else that you can see, that I should change?

 

[SkipVought]

Get your Public declarations (variables that all your procedures will use and share) at the TOP of your module. Then your Main, for instance, and then compile. It will burp at anything that is bot syntactically correct. Then all you have to worry about is your logic.

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

 

[Frank Melan]

Skip. It now hangs on GoTo ExitMacro, the error message I get is "Label not defined". Any suggestions?

 

[Andrzejek]

Do you have a label [tt]ExitMacro[/tt] in your code? It would look something like this:

If (oScrn Is Nothing) Then GoTo ExitMacro
...
ExitMacro:  [green] '<-- label ExitMacro[/green]
...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Frank Melan]

Apparently I did not. I added it & now it hangs on Set oSystem = CreateObject("Reflection.System"). The error message now reads "run-time error '429': ActiveX Component can't create object".

 

[SkipVought]

Did you set a reference to the Attachmate Reflections Object Library?

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

 

[Frank Melan]

I am not quite certain how to do that. Coding is something that you young guys find easy. An old person like myself (63) still struggle with coding. Please point me in the right direction. I checked & there was no reference to the Attachmate Reflections Object Library. I did add a reference to the Attachmate Reflections Object Library, I still get the same error code in the same location.

 

[SkipVought]

s'ok. I'm 75.

You want to be sure that your declarations regarding Reflections Objects are absolutely correct.

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

 

[Frank Melan]

Skip. Right now, I am so certain what you mean. I did check the object name & it is "C:\ProgramData\Attachmate\Reflection\R04-reflection.r2w" I am guessing that it has something to do with the object name, but I have no idea. I am providing the following info, in the hope that it may hep us solve this problem. The location is "C:\ProgramData\Attachmate\Reflection", the type of file is "Reflection for UNIX and OpenVMS Session (.r2w)", the file name is "R04-Reflection.R2w, & The path is "C:\ProgramData\Attachmate\Reflection

 

[SkipVought]

That's what you'll need to set the oSess Session object.

What I'm referring to is the DECLARATIONS. For me, the correct declaration was

Public oSystem As ExtraSystem
Public oSessions As ExtraSessions
Public oSess As ExtraSession
Public oScrn As ExtraScreen

That's according to the reference I added for AttachmateExtra Object Library. You have set a different reference. Your declarations must be in accordance with that object Library. I don't know what it should be. Only you can determine what's correct.

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

 

[Frank Melan]

Oh. So if I understand you, it should be Public oSystem As "Something"System. etc.

 

[SkipVought]

Yes. Your Object Browser (F2) ought to list all the available Libraries in your VBAProject.

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

 

[Frank Melan]

I want to thank Skip & Andrzejek for your assistance. I learned several things in this process, one is that you don't give up. The other thing is that occasionally an old school person like me can find a solution to a problem. Here is the code:

Sub VISTA()
Dim Msg As String, Ans As Variant
     
    Msg = "Would you like to go to LVAMC Vista?"
     
    Ans = MsgBox(Msg, vbYesNo)
     
    Select Case Ans
         
    Case vbYes
  Dim MyFile As String, Cmd As String
 MyFile = "C:\Users\Public\Desktop\Vista Access.lnk"
 Cmd = "RunDLL32.EXE shell32.dll,ShellExec_RunDLL "
 Shell (Cmd & MyFile)
 End Select
 End Sub

It is a variation of code I have used for another application.

 

[SkipVought]

Frank, don't let "old school" keep you back. Might be a good title for a book! ;-)

This ol' guy, born & raised in The Keystone State. PWHS Plymouth Mtg, Lehigh U.



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

 

[Andrzejek]

May I suggest a little 're-arranging' of you code?

Sub VISTA()
Dim MyFile As String, Cmd As String

If vbYes = MsgBox("Would you like to go to LVAMC Vista?", vbYesNo) Then
    MyFile = "C:\Users\Public\Desktop\Vista Access.lnk"
    Cmd = "RunDLL32.EXE shell32.dll,ShellExec_RunDLL "
    Shell (Cmd & MyFile)
End If

End Sub

Just a suggestion...

63 - a young guy...

Have fun.

---- Andy

There is a great need for a sarcasm font.