INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

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.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...This was the ONLY place that I could find information that I could use to resolve the problem. So thanks once again to member TomSark and the SQL forum!..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > MIS/IT > Inter-Connectivity Gateways > AttachMate solutions Forum

Sess0.Visible messing up my Excel when I close the workbook

CODE

'-----------------------------------------------------------------------------------------------
Public Sub Allotments22()
'*********************************************************************************************
'*  This program screen scripts what's in the excel spreadsheets:                            *
'*  C:\Allotment Process\Allotment Entries into Extra.  For every type their is a sub to     *
'*  perform this since each allotment process has different fields and positions on the      *
'*  screen.  This one is for TR22's.  What it does is open the spreadsheet and then opens    *
'*  an instance of Extra and logs on with the lines 1-6.  And then scripts the cells to      *
'*  cells to the end.  If it encounters an error, it writes that error from the screen to    *
'*  excel spreadsheet and moves on to the next row.  This helps in not having them monitor   *
'*  this.  They will have to check to see what has processed.                                *
'*                                                                                           *
'*  Version History:                                                                         *
'*  1.0                 Kyong-A Minter          Original Program         07/26/2006          *
'*  1.1                 Kyong-A Minter          added logic to put          07/27/2006       *
'*                                              & check for complete & date in end columns   *
'*                                              so user's don't have to monkey with deleting *
'*                                              rows and stuff                               *
'*********************************************************************************************
    Dim introwcount As Integer
    Dim x As Integer
    Dim excelvalue As String
    Set System = New ExtraSystem

    ' Declare variables to contain the OLE objects
        Dim objExcel As Object
        Dim objWorkBook As Object
        Dim objChart As Object

        On Error Resume Next

' Attempt to get a reference to an open instance of Excel
        Set objExcel = GetObject(, "Excel.Application")
        If objExcel Is Nothing Then
           'If GetObject failed, open a new instance of Excel
            Set objExcel = CreateObject("Excel.Application")
            If objExcel Is Nothing Then
               MsgBox ("Could not open Excel.")
                Exit Sub
            End If
        End If

' Make Excel visible on the screen
        objExcel.Visible = True

' Create a new Workbook
        Set objWorkBook = objExcel.Workbooks.Open("C:\Allotment Process\Allotment Entries.xls")

        If objWorkBook Is Nothing Then
            MsgBox ("Could not open a new Excel workbook.")
            objExcel.Quit
            Exit Sub
        End If

    objWorkBook.Worksheets("TR22").Select
    'Exit Sub

    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 Sess0 = System.Sessions.Open("FLAIR.EDP")
    If Not Sess0.Visible Then Sess0.Visible = True
    Dim move
    Dim move2
    Dim strL2L5 As String
    Dim fieldtest
    Dim strmessage
    Dim result
    Dim result2
    Dim x2

    Set MyScn = Sess0.Screen
    move = MyScn.WaitForCursorMove(3)
    Application.Wait (Now + TimeValue("0:00:01"))
    MyScn.PutString Range("A1").Offset(0, 1).Value
    MyScn.SendKeys ("<Enter>")
    move2 = MyScn.WaitForCursorMove(12)
    Application.Wait (Now + TimeValue("0:00:01"))
    MyScn.PutString Range("A1").Offset(1, 1).Value
    fieldtest = Value(Range("A1").Offset(2, 1).Value, 17, 36)
    MyScn.SendKeys ("<Enter>")
    move = MyScn.WaitForCursorMove(6)
    Application.Wait (Now + TimeValue("0:00:01"))
    MyScn.PutString "1"
    MyScn.SendKeys ("<Enter>")
    move2 = MyScn.WaitForCursorMove(-20)
    Application.Wait (Now + TimeValue("0:00:01"))
    MyScn.SendKeys ("<Enter>")
    move = MyScn.WaitForCursorMove(3)
    Application.Wait (Now + TimeValue("0:00:01"))
    MyScn.PutString Range("A1").Offset(3, 1).Value
    fieldtest = Value(Range("A1").Offset(4, 1).Value, 6, 18)
    fieldtest = Value(Range("A1").Offset(5, 1).Value, 6, 30)
    MyScn.SendKeys ("<Enter>")
    move2 = MyScn.WaitForCursorMove(16)
    Application.Wait (Now + TimeValue("0:00:01"))
    MyScn.PutString "22"
    fieldtest = Value("S", 22, 80)
    MyScn.SendKeys ("<Enter>")
    move = MyScn.WaitForCursorMove(-15)
    Application.Wait (Now + TimeValue("0:00:01"))
    For x = 9 To 250
        If Range("A1").Offset(x, 0) = "END" Then
            MyScn.SendKeys ("<Clear>")
            Application.Wait (Now + TimeValue("0:00:01"))
            MyScn.PutString ("cesf logoff")
            MyScn.SendKeys ("<Enter>")
            Sess0.Close
            Set MyScn = Nothing
            Set Sess0 = Nothing
            strmessage = MsgBox("TR22 Input Complete.  Please click okay and check your work.", vbOKOnly)
            Exit Sub
        End If
        '1.1 new change here:
        If Range("A1").Offset(x, 26).Value <> "Complete" Then
        '*****
            x2 = x + 1
            strL2L5 = L2L5(Range("A1").Offset(x, 1).Value)
            MyScn.PutString L2
            Sess0.Screen.MoveTo 7, 8
            MyScn.PutString L3
            Sess0.Screen.MoveTo 7, 11
            MyScn.PutString L4
            Sess0.Screen.MoveTo 7, 14
            MyScn.PutString L5
            fieldtest = Value(Range("A1").Offset(x, 2).Value, 7, 18)
            fieldtest = Value(Range("A1").Offset(x, 3).Value, 7, 21)
            fieldtest = Value(Range("A1").Offset(x, 4).Value, 7, 24)
            MyScn.SendKeys ("<Enter>")
            Application.Wait (Now + TimeValue("0:00:02"))
            result2 = Sess0.Screen.GetString(2, 2, 4)
            If result2 = "22S2" Then
                MyScn.PutString Range("A1").Offset(x, 5).Value
                fieldtest = Value(Range("A1").Offset(x, 6).Value, 6, 16)
                fieldtest = Value(Range("A1").Offset(x, 7).Value, 6, 47)
                fieldtest = Value(Range("A1").Offset(x, 8).Value, 6, 62)
                fieldtest = Value(Range("A1").Offset(x, 9).Value, 9, 7)
                fieldtest = Value(Range("A1").Offset(x, 10).Value, 9, 25)
                fieldtest = Value(Range("A1").Offset(x, 11).Value, 9, 33)
                fieldtest = Value(Range("A1").Offset(x, 12).Value, 9, 42)
                fieldtest = Value(Range("A1").Offset(x, 13).Value, 9, 62)
                fieldtest = Value(Range("A1").Offset(x, 14).Value, 9, 66)
                fieldtest = Value(Range("A1").Offset(x, 15).Value, 9, 71)
                fieldtest = Value(Range("A1").Offset(x, 16).Value, 12, 7)
                fieldtest = Value(Range("A1").Offset(x, 17).Value, 12, 15)
                fieldtest = Value(Range("A1").Offset(x, 18).Value, 12, 19)
                fieldtest = Value(Range("A1").Offset(x, 19).Value, 12, 23)
                fieldtest = Value(Range("A1").Offset(x, 20).Value, 12, 38)
                fieldtest = Value(Range("A1").Offset(x, 21).Value, 12, 44)
                fieldtest = Value(Range("A1").Offset(x, 22).Value, 12, 51)
                fieldtest = Value(Range("A1").Offset(x, 23).Value, 12, 57)
                fieldtest = Value(Range("A1").Offset(x, 24).Value, 12, 66)
                fieldtest = Value(Range("A1").Offset(x, 25).Value, 15, 41)
                MyScn.SendKeys ("<Enter>")
                Application.Wait (Now + TimeValue("0:00:03"))
                result2 = Trim(Sess0.Screen.GetString(1, 2, 78))
                If result2 <> "" Then
                    Sess0.Screen.MoveTo 1, 2
                    objWorkBook.Worksheets("TR22").Cells(x2, 27).Value = result2
                    '1.1 new change here:
                    objWorkBook.Worksheets("TR22").Cells(x2, 28).Value = Now()
                    strrange = "A" & x2 & ":AB" & x2
                    objWorkBook.Worksheets("TR22").Range(strrange).Select
                    With Selection.Interior
                        .ColorIndex = 3
                        .Pattern = xlSolid
                    End With
                    '*****
                    MyScn.SendKeys ("<PF12>")
                    MyScn.SendKeys ("<Enter>")
                    move2 = MyScn.WaitForCursorMove(6)
                    Application.Wait (Now + TimeValue("0:00:01"))
                Else
                    '1.1 new change here:
                    objWorkBook.Worksheets("TR22").Cells(x2, 27).Value = "Complete"
                    objWorkBook.Worksheets("TR22").Cells(x2, 28).Value = Now()
                    strrange = "A" & x2 & ":AB" & x2
                    objWorkBook.Worksheets("TR22").Range(strrange).Select
                    Selection.Interior.ColorIndex = xlNone
                    '*****
                    MyScn.SendKeys ("<PF12>")
                    MyScn.SendKeys ("<Enter>")
                    move2 = MyScn.WaitForCursorMove(1)
                    Application.Wait (Now + TimeValue("0:00:01"))
                End If
            Else
                Sess0.Screen.MoveTo 1, 2
                result = Sess0.Screen.GetString(1, 2, 78)
                objWorkBook.Worksheets("TR22").Cells(x2, 27).Value = result
                '1.1 new change here:
                objWorkBook.Worksheets("TR22").Cells(x2, 28).Value = Now()
                strrange = "A" & x2 & ":AB" & x2
                objWorkBook.Worksheets("TR22").Range(strrange).Select
                With Selection.Interior
                    .ColorIndex = 3
                    .Pattern = xlSolid
                End With
                '*****
                MyScn.SendKeys ("<PF4>")
                move = MyScn.WaitForCursorMove(21)
                Application.Wait (Now + TimeValue("0:00:01"))
                MyScn.PutString "22"
                Sess0.Screen.MoveTo 22, 80
                MyScn.PutString "S"
                MyScn.SendKeys ("<Enter>")
                move = MyScn.WaitForCursorMove(-15)
                Application.Wait (Now + TimeValue("0:00:01"))
            End If
        End If
    Next x

End Sub

Gimme some clues cause I'm stumped and this sucks.

IknowMe (Programmer)

27 Jul 06 12:29

Does Sess0.Activate acomplish what you want?

thumbsup2 Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

kamfl610 (Programmer)

27 Jul 06 13:46

MrMilson,

are you saying replace Sess0.Visible to Sess0.Activate?

kamfl610 (Programmer)

27 Jul 06 14:06

Session.Activate is cool but still the same problem.

IknowMe (Programmer)

27 Jul 06 14:09

yeah, assuming you just want the session window to come forward for the user to see.

Sess.visible would only be false if you set it to false.

thumbsup2 Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

IknowMe (Programmer)

27 Jul 06 14:12

'On Error Resume Next

and see what error your getting

thumbsup2 Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

kamfl610 (Programmer)

27 Jul 06 14:16

There's no error message or anything. The macro finishes and I think everything hunky dory. Then I go to physically close the excel spreadsheet and excel crashes.

IknowMe (Programmer)

27 Jul 06 14:19

and commenting out the line sess0.visible prevents the crash?

thumbsup2 Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

kamfl610 (Programmer)

27 Jul 06 14:24

Yep. If i run the macro without the Sess0.visible commented out (which does nothing in extra), it'll run through and I physically click on excel to close it, excel closes normally. I tried the Sess0.Activate thing and I get the same reaction with Sess0.visible.

kamfl610 (Programmer)

27 Jul 06 14:25

I meant with the Sess0.Visible commented out. Too fast typing.

IknowMe (Programmer)

27 Jul 06 14:59

I'm stumped, I've never seen this behaviour before.

thumbsup2 Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

kamfl610 (Programmer)

27 Jul 06 15:12

I'm at this point running parts of my code to see where it's freaking out. Let you know what I find out.

IknowMe (Programmer)

27 Jul 06 15:13

Definatly, now I'm curious.

thumbsup2 Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

kamfl610 (Programmer)

27 Jul 06 15:21

Okay. Found it. It's when I actually do the waitformovecursor. Code below:

CODE

Public Sub Testing()
    Dim strmessage
    Set System = New ExtraSystem

' Declare variables to contain the OLE objects
        Dim objExcel As Object
        Dim objWorkBook As Object

        On Error Resume Next

' Attempt to get a reference to an open instance of Excel
        Set objExcel = GetObject(, "Excel.Application")
        If objExcel Is Nothing Then
           'If GetObject failed, open a new instance of Excel
            Set objExcel = CreateObject("Excel.Application")
            If objExcel Is Nothing Then
               MsgBox ("Could not open Excel.")
                Exit Sub
            End If
        End If

' Make Excel visible on the screen
        objExcel.Visible = True

' Create a new Workbook
        Set objWorkBook = objExcel.Workbooks.Open("C:\Allotment Process\Allotment Entries.xls")

        If objWorkBook Is Nothing Then
            MsgBox ("Could not open a new Excel workbook.")
            objExcel.Quit
            Exit Sub
        End If
'Select the sheet
    objWorkBook.Worksheets("TR20").Select

    'Get Extra Object
    If (System Is Nothing) Then
    MsgBox "Could not create the EXTRA System object.  Stopping macro playback."
    Stop
    End If

'Establish a Session of Extra
Set Sessions = System.Sessions

    If (Sessions Is Nothing) Then
       MsgBox "Could not create the Sessions collection object.  Stopping macro playback."
             Stop
    End If

'Open Extra
    Set Sess0 = System.Sessions.Open("FLAIR.EDP")
    If Not Sess0.Visible Then
        Sess0.Visible = True
    End If
     Dim move
    Dim move2
    Dim strL2L5 As String
    Dim fieldtest
    Dim result
    Dim result2
    Dim x2
    Dim strrange

'Login process
    Set MyScn = Sess0.Screen
'!!!!!This is the culprit!!!!!
    Sess0.Screen.WaitForCursorMove (3)
    Sess0.Close
    objExcel.ActiveWorkbook.Close
    Set MyScn = Nothing
    Set Sess0 = Nothing
    strmessage = MsgBox("TR21 Input Complete.  Please click okay and check your work.", vbOKOnly)
End Sub

When I run this much of the code and macro runs thru and then I physically try to close the workbook, Excel crashes. Now I have no idea why this happens.

IknowMe (Programmer)

27 Jul 06 17:12

after

MyScn.SendKeys ("<Enter>")

Do you get anything else you can watch for on the screen besides a cursor movement

ex.

at row 24, col 1, I get a "REQUEST COMPLETE" msg

thumbsup2 Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

kamfl610 (Programmer)

27 Jul 06 17:16

I tried all the waitfor commands. They all do the same thing. Take it out, Excel has no problem. I'm about to go mad. Does this have anything to do with timing out?

IknowMe (Programmer)

28 Jul 06 10:20

I've never used waitforcursormove
but after looking it up I beleive

move = MyScn.WaitForCursorMove(-15)

is waiting for the cursor to move to row -15 ???
is that what you want it to do?

thumbsup2 Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

Skie (Programmer)

28 Jul 06 17:04

WaitForCursor(-15) waits for it to move to row -15.

WaitForCursorMove(-15) waits for the cusror to move 15 rows up from it's current position. This may cause a problem if the cursor is at row 15 or less.

I don't see any reason why your WaitFor would be causing an error.

Skie (Programmer)

28 Jul 06 17:19

While not as clean as a wait for.. Have you tried like a loop..

CODE

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Whatever
    TimeOutTenths = Sess0.TimeOutValue
    TimeOutTenths = TimeOutTenths/100
    Row = MyScr.Row
    For i = 1 to TimeOutTenths
        If MyScr.Row <= Row + 3 Then Exit For
        Sleep 100
    Next
End Sub

You could set it up as a sub, so that you didn't have to duplicate it in the code.

[code]Sub WaitForRows(MoveRows)
    TimeOutTenths = Sess0.TimeOutValue
    TimeOutTenths = TimeOutTenths/100
    Row = MyScr.Row
    If MoveRows > 0 Then
        For i = 1 to TimeOutTenths
            If MyScr.Row <= Row + MoveRows Then Exit For
            Sleep 100
        Next
    ElseIf MoveRows < 0 Then
        For i = 1 to TimeOutTenths
            If MyScr.Row >= Row + MoveRows Then Exit For
            Sleep 100
        Next
    End If
End Sub

Sub Blah
   WaitForRows(3)
End Sub

kamfl610 (Programmer)

28 Jul 06 20:49

I will try what you're suggesting skie on Monday. Let you know later that day what happens. I don't know why any of the Waitfor's freak out excel but it only freaks it out when you close the excel spreadsheet. So stupid. Might be a microsoft thing for all i know. I tried WaitforCursor, WaitforCursorMove, basically and waitfor everything and excel freaks out.

kamfl610 (Programmer)

28 Jul 06 20:52

MrMilson,

Why i had to put (-15) is that the cursor could be at row 22 on my screen but on the next screen, it would be row 7 and the only way waitforcursormove would work is that it remembers what the last screen row you were in (22) and I had to make it go back 15 rows so it would recognize row 7. Kind of backwards but I couldn't seem to get the WaitforCursor to work out right.

kamfl610 (Programmer)

1 Aug 06 11:43

Thanks Skie for the suggestion. I did try it and it worked somewhat. I made some slight tweaks:

CODE

Sub WaitForRows(MoveRows)
    For i = 1 To 100
        If MyScn.Row = MoveRows Then Exit For
        Application.Wait (Now + TimeValue("0:00:01"))
    Next
End Sub

Chose to wait for the exact row position and just had it wait for it. Works great. Thanks for all the help! Excel doesn't freak out anymore when I close it.

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!

Back To Forum

Back To AttachMate solutions

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

Sess0.Visible messing up my Excel when I close the workbook

CODE

CODE

CODE

CODE

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!