Strange behavior opening Excel

In a new project:
-Create an Excel file called "C:\Temp\Bax.xls";
-add a reference to Excel;
-add a command button to the form;
-add the following code to your project:

Option Explicit

Private gobjExcel As Excel.Application

Private Sub Command1_Click()
    If Not OuvrirExcel("C:\Temp\Bax.xls") Then End
   
    With gobjExcel
        .Worksheets("Sommaire").Activate
        .Range("Date").Value = Date
        .Range("A1").Select
    End With
   
    gobjExcel.Visible = True
End Sub

Private Sub Form_Unload(Cancel As Integer)
    ExcelQuit
End Sub

Private Function OuvrirExcel(ByVal pstrFichierTemplate As String) As Boolean

    On Error GoTo ErrorHandler
   
    OuvrirExcel = False

    ExcelOpen
    If gobjExcel Is Nothing Then Exit Function

    gobjExcel.Workbooks.Open pstrFichierTemplate

    OuvrirExcel = True

ErrorHandler:
    MsgBox "Impossible d'ouvrir le fichier demandé." & _
           vbCrLf & vbCrLf & _
           "Code d'erreur: " & Err.Number & _
           vbCrLf & _
           "Description: " & Err.Description, _
           vbExclamation + vbOKOnly
End Function

Public Sub ExcelCloseDoc()
Dim objWorkBookTmp As Excel.Workbook

    On Error Resume Next

    If gobjExcel.Workbooks.Count > 0 Then
        For Each objWorkBookTmp In gobjExcel.Workbooks
            objWorkBookTmp.Close False
        Next
    End If
End Sub

Private Sub ExcelOpen()
' Getobject function called without the first argument
' returns a reference to an instance of the application.
' If the application isn't running, an error occurs.

    On Error Resume Next

    Set gobjExcel = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Err.Clear   ' Clear Err object in case error occurred.
        Set gobjExcel = New Excel.Application
    End If
    ExcelCloseDoc
End Sub

Public Sub ExcelQuit()
    On Error Resume Next

    If gobjExcel Is Nothing Then Exit Sub
    ExcelCloseDoc
    gobjExcel.Quit
    Set gobjExcel = Nothing
End Sub

Now run it this way:
-Click on the button: Excel will open.
-Close the workbook (not Excel - only the workbook).
-Click on the button again: Excel will open the workbook again.
-Up to here everything is going as it is suppose to go.
-Now close Excel.
-Click on the button again. What I see now is the Excel frame (title bar, menu, status bar, ...) but not the worksheet!

What can I do to correct it?
LVL 71
Éric MoreauSenior .Net ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EDDYKTCommented:
I use excel 2000 but I don't have any problem.

8->
0
Éric MoreauSenior .Net ConsultantAuthor Commented:
I have Excel 97 and I can't upgrade it since the client already deployed this version and don't plan to upgrade a couple of thousands PC now!
0
TimCotteeHead of Software ServicesCommented:
I tried out your code and made a few changes, the wrong behaviour now seems to have gone away, I think that your error handling routines were catching each other out and causing a bit of a problem.

Change your excelopen function to :
    Set gobjExcel = Nothing
    Set gobjExcel = GetObject(, "Excel.Application")
    If Err.Number <> 0 Or gobjExcel Is Nothing Then
        Err.Clear   ' Clear Err object in case error occurred.
        Set gobjExcel = CreateObject("Excel.Application")
    End If

And add

    On Error GoTo 0
    Exit Function

before the errorhandler: line in ouvrirexcel function
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Éric MoreauSenior .Net ConsultantAuthor Commented:
I don't want to close Excel before re-opening it. I left Excel open to save time!

0
TimCotteeHead of Software ServicesCommented:
Ok, emoreau I have thought about this a little more and checked to see what is actually happening here. The root of the problem is that when you are creating the object reference to excel within VB if the user closes excel then the reference is not completely destroyed. You can verify this by clicking your command button and then closing excel. If you then goto the windows task list (ctrl-alt-del etc) you can go to the processes tab and see that excel is still running. This is because the object still exists in memory within VB.

If you now attempt to connect to it again you get the problem that you are identifying. As for a solution to this, I am not sure what to suggest.

0
GouimetCommented:
I'm not really sure, but you could try to:

1. Refresh the screen

2. Replace
    gobjExcel.Visible = True

   With:
    With gobjExcel
        .DisplayFullScreen =  
                 Not .DisplayFullScreen
        .DisplayFullScreen =
                 Not .DisplayFullScreen
        .Visible = True
    End With

Tell me how it goes... ;-)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantAuthor Commented:
It works!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.