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

[kamfl610]

Hi all,

Don't know if this is happening with you.
I have a macro that runs in Excel that opens Extra 6.4 processes some screens, data, yada yada. When my macro finishes and I'm done with the process, I go to close the Workbook and Excel crashes. I've isolated it to Sess0.Visible statement. If it's commented out and I run the macro and finishes (obviously nothing happens in Extra), excel will close normally. What could I do to help with this problem? Anyone have any ideas? Here's the code of the macro i'm executing.

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

Does Sess0.Activate acomplish what you want?

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

 

[kamfl610]

MrMilson,

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

 

[kamfl610]

Session.Activate is cool but still the same problem.

 

[IknowMe]

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.





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

 

[IknowMe]

'On Error Resume Next

and see what error your getting

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

 

[kamfl610]

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]

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

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

 

[kamfl610]

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]

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

 

[IknowMe]

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

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

 

[kamfl610]

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]

Definatly, now I'm curious.

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

 

[kamfl610]

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

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]

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


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

 

[kamfl610]

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]

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?

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

 

[Skie]

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]

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

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.

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]

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.