Opening Excel from Visual Basic many times.

I have a program that uses a template in excel. If I populate the template and show excel, everything is fine. But if the user closes that instance of excel and tries to run another report, when it reopens excel, you can't see anything but the menus and the frame. The sheet itself is not visible. It is like it is transparent. It works fine again if I close my program and reopen it but I don't want the user to have to do that. Any ideas why it does that and how to avoid it? I can show a screen shot if needed. Thanks.

J_K_M_A_N
LVL 3
J_K_M_A_NAsked:
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.

fanpagesCommented:
Hi,

Do you set the Application.ScreenUpdating setting to False in your program, and not set it back to True before you quit your object instance?

With Excel in the state you describe, try going into the Visual Basic for Applications environment (<ALT>+[F11]), and then use the "Immediate" Window (<CTRL>+[G]) to type:
Application.ScreenUpdating = True

Return to the main Excel worksheet view (<ALT>+[F11] again) and see if the situation improves.

BFN,

fp.

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
J_K_M_A_NAuthor Commented:
Yes it does...but I don't set that ever that I know of. I have added that line to my visual basic program and it worked. What I have found though is that my program doesn't close it's instance of excel until my program actually closes because I am leaving it open for the user to see until they are finished. I can check that checking the task manager. I will show my excel code below.

On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
If Err = 429 Then
    NeedToOpen = True
    Err = 0
    Set oExcel = New Excel.Application
    'Can 't create object
    If Err = 429 Then
        MsgBox Err & ": " & Error, vbExclamation + vbOKOnly
        Exit Sub
    End If
Else
    NeedToOpen = False
End If
Set oBook = oExcel.Workbooks.Open("T:\End Of Month Reports\EOM Report Template.xls")
Set oSht = oBook.Sheets(1)


I got the above from a web site a long time ago. The code below is to close excel. The problem is, I want to leave excel open for the user to see the template as long as they would like so I don't actually close excel ever until my program closes. Any idea how to keep excel open until the user closes it?


oExcel.Visible = True
oExcel.Interactive = True
oExcel.ScreenUpdating = True

If NeedToOpen = True And DontClose.Value = 0 Then
    Call oBook.Close(False)
    oExcel.Quit
    Set oExcel = Nothing
End If


If Not (oSht Is Nothing) Then
    Set oSht = Nothing
End If
If Not (oBook Is Nothing) Then
    Set oBook = Nothing
End If

Thanks for the help so far. It will work this way if nothing else. I would just like to find a way to close excel all the way when the user closes it. Thanks for the help.

J_K_M_A_N

fanpagesCommented:
Hi,

If I understand you correctly:

You create an Excel.Application object in your application, and open an existing workbook (template) to update values in the cells.

You then leave this Excel instance open so that the user can review the changed values, and only close the object when your program closes (that may be before, or after, the user wishes to close MS-Excel).


What I suggest is that you use the code you already have to create the object, & open the file, but then close it & set to internal objects to 'Nothing' as you do already to release the resources.

Now, use the Shell() VB function or ShellExecute() Windows API function to then re-load MS-Excel externally to your application so that it is controlled by the Desktop environment rather than by your VB application.

Does that make sense?

BFN,

fp.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

wraith821Commented:
open excel noninteractively, do you thing, close excel, shellexecute excel filename
fanpagesCommented:
There seems to be an echo :)
J_K_M_A_NAuthor Commented:
I understand. I would rather not do it that way but if there is no way around it then that is what I will have to do. Thanks for the help.

J_K_M_A_N
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
Microsoft Development

From novice to tech pro — start learning today.