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

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

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

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

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

CODE --> VBA

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:

CODE --> VBA

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
    If (oScrn Is Nothing) Then GoTo Exit Macro
    
    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 

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

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.

CODE

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

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

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

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

CODE -->

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 

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

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

CODE

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

Skip,

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

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

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

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

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,

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

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

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

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

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

CODE

If (oScrn Is Nothing) Then GoTo ExitMacro
...
ExitMacro:   '<-- label ExitMacro
... 

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

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

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

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

Skip,

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

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

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

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

s'ok. I'm 75.

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

Skip,

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

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

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

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

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

CODE

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,

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

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

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

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

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

Skip,

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

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

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

CODE --> VBA

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.bigsmile

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

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

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

Skip,

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

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

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

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... smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

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