Excel VBA : Detect Attachmate session (create new if necessary)

[APMOB]

Hi guys,

I've developed a macro in Excel 2007 to read/write to a pre-configured Attachmate session (the path for which is described by a string variable, stEDPFile). The macro is designed, on opening the workbook, to check if this session is already open, and if not, to open it :

Global MySystem As Object
Global MySession As Object
Global MyScreen As Object

Private Sub Workbook_Open()
    
    If MyScreen Is Nothing Then
        On Error Resume Next
        Set MySystem = GetObject("", "EXTRA.System")
        If MySystem Is Nothing Then
            Set MySystem = CreateObject("EXTRA.System")
            If (MySystem Is Nothing) Then
                Response = MsgBox("Could not create the EXTRA System object", vbCritical, "EXTRA System")
                End
            End If
        End If
        Set MySession = MySystem.Sessions(stEDPFile)
        If MySession Is Nothing Then
            Set MySession = MySystem.Sessions.Open(stEDPFile)
            If MySession Is Nothing Then
                Response = MsgBox("Could not create the EXTRA Session object", vbCritical, "EXTRA Session")
                End
            End If
        End If
        Set MyScreen = MySession.Screen
        If MyScreen Is Nothing Then
            Response = MsgBox("Could not create the EXTRA Screen object", vbCritical, "EXTRA Session")
            End
        End If
    End If

End Sub

It works perfectly fine on my machine, and on my colleague's also (we are both using Office 2007 and Attachmate v8.0)

However, when the end-users try to run it, the code does not automatically open the session (Office versions vary between 2003 and 2007, Attachmate appears to be v7.0 across the board)

It can create the system object fine, but when it tries to open the edp file [Set MySession = MySystem.Sessions.Open(stEDPFile)], it does nothing (i.e. on debugging, after this line, the object MySession is still 'Nothing')

I can probably arrange for them to get their Attachmate upgraded but given the simplicity of the code, I wouldn't have thought there would be any issue with using 7.0 as opposed to 8.0? Has anybody else come up against this problem?

Thanks in advance!

APMOB

 

[SkipVought]

hi,

The contents of stEDPFile that you must have declared in some other place and assigned in some other code, may have the incorrect path for the users in question. I have some old code I just ran, which predates some recent software updates, and gor an error, "The specified session is not available" for my code

    Set oSess = oSystem.Sessions.Open("C:\Program Files\E!PC\Sessions\Mainframe.edp")

In my environment, I cannot have multiple sessions, so I cannot verify your MySession logic. ButI'd use something like this...

for each MySession in MySystem.Sessions
  if MySession.Name = "TheSessionNameOfInterest" then
     'do this when MySession IS open

  else
     'do this when MySession IS NOT open

  end if
next

Furthermore, I run ALL my Extra code from Excel VBA -- ALL! The VBA editor is so much better than the Extra VB editor. Much better facility for debugging! Its like the difference between driving a Vega and a Cadillac, or maybe a Buick, depending on your opinion.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[APMOB]

Thanks Skip!

Just to clarify, this code is running from Excel VBA (within the workbook that will reference the session)

And the stEDPFile string is the fully qualified path and filename of the pre-configured session (i.e. "\\server\folder\session.edp") - which the users have full visibility to and can run independently from Windows Explorer.

I tweaked the code slightly to your suggestion (copied below) but it doesn't solve the problem. What is annoying, though, is if I open the .edp file on the user's PC, before running the VB script, the code works as expected (i.e. it detects that this particular session is open, sets the object variables and continues as normal)

But if the session is not already open prior to running the script, it won't create a new session for me (i.e. the line : Set MySession = MySystem.Sessions.Open(stEDPFile) : has no effect on the MySession object (it remains = 'Nothing')

But this does work on other PC's?? Albeit running a slightly more recent version of Attachmate (but as I mentioned before, I would not have thought this code to be so advanced that it would work on v8.0 but not on v7.0?)

Private Sub Workbook_Open()
    
    If MyScreen Is Nothing Then
        On Error Resume Next
        Set MySystem = GetObject("", "EXTRA.System")
        If MySystem Is Nothing Then
            Set MySystem = CreateObject("EXTRA.System")
            If (MySystem Is Nothing) Then
                Response = MsgBox("Could not create the EXTRA System object", vbCritical, "EXTRA System")
                End
            End If
        End If
        For Each objTempSession In MySystem.Sessions
            On Error GoTo CreateSession
            If UCase(objTempSession.Name) = UCase(Replace(Dir(stEDPFile), ".edp", "")) Then
                Set MySession = objTempSession
                GoTo SessionIsSet
            End If
        Next objTempSession
    
CreateSession:
    
        Set MySession = MySystem.Sessions.Open(stEDPFile)
        If MySession Is Nothing Then
            Response = MsgBox("Could not create the EXTRA Session object", vbCritical, "EXTRA Session")
            End
        End If
                
SessionIsSet:
    
        Set MyScreen = MySession.Screen
        If MyScreen Is Nothing Then
            Response = MsgBox("Could not create the EXTRA Screen object", vbCritical, "EXTRA Session")
            End
        End If
    End If
 
End Sub

Any suggestions?

(P.S. We don't have Vega's, or Caddy's, or even Buick's, here in Ireland, but I like your analogy and couldn't agree more!)

 

[SkipVought]

But this does work on other PC's

And you're ABSOLUTELY SURE that the path to the session file on that particular PC, is exactly the same as your string contents?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[APMOB]

Pretty sure - that's why I went to the trouble of using a fully qualified file name / path (to avoid the situation of different users having the same network drive mapped to different letters on their workstations etc.)

But to eradicate that 0.1% of doubt, I'll go and test it on one of the troubled PC's and confirm...

 

[SkipVought]

Sorry, I did not notice that you are using a file on a network, and as such, IS the same for every user regardless of drive mapping.

Is THAT PC's environment any different than any other PC environment?

BTW, your code implies Extra VB, not Excel VBA, when you posted...

Global MySystem As Object
Global MySession As Object
Global MyScreen As Object

as Global is not a VBA word for global variables; rather Public.

What happens if you STEP thru your code if you BREAK just prior to executing the MySession code? faq707-4594. I'd step into your Workbook_Open code and watch what happens.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[APMOB]

Global is a valid variable type in VBA, Skip - it's pretty much synonymous with Public. Incidentally, I tried adding the Attachmate object libraries within Excel to see if that helped :

Public MySystem As ExtraSystem
Public MySession As ExtraSession
Public MyScreen As ExtraScreen

It didn't...

There's no difference in environment for any of the PC's, we're all on the same network. Some cosmetic differences in terms of versions of Office but nothing that should be detrimental to this.

Stepping through the code, I can set the system object fine :

Set MySystem = GetObject("", "EXTRA.System")

And it shows as expected in the Watch window. The problem is when I try to open the session within the system :

Set MySession = MySystem.Sessions.Open(stEDPFile)

On my own PC (and that of a colleague), this opens the session with no issues. However, on a couple of the other PC's, this prompts an error message :

The connectivity for the session [stEDPFile] is not installed

The 'MySession' object has a 'Nothing' value throughout.

I'm stumped...

 

[SkipVought]

So I would question the veracity of the path & filename in stEDPFile on that pc.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]