×
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!
  • Students Click Here

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

Students Click Here

Jobs

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

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

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

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

CODE --> VBA

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

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

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

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

CODE

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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

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

CODE --> VBA

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!)

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

Quote:

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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

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

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

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

CODE

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: How to use the Watch Window as a Power Programming Tool. I'd step into your Workbook_Open code and watch what happens.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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

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

CODE --> VBA

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 :

CODE --> VBA

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 :

CODE --> VBA

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 :

Quote (EXTRA! Enterprise)

The connectivity for the session [stEDPFile] is not installed

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

I'm stumped...

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

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

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! Already a Member? Login

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