Excel object crashing when used multiple times (not multiple instances)

[pdevries]

The problem occurs when I try to export data to an Excel object. I can do it once after I start my VB app but if I try to export the data a second time it seems to work but Excel stays in hiding even though I have it to show. After I remove it's task by using Ctrl-Alt-Del, I try again and a message pops up saying "The remote server machine does not exist or is unavailable." Although, something tells me that this has something to do with the way I'm closing Excel. Programmatically though, I'm showing Excel then I'm setting the Excel objects to Nothing. I'm wondering how I can use Excel objects over and over without having it go hidden.

[Cimperiali]

"I'm showing Excel then I'm setting the Excel objects to Nothing"
who closes excell, Vb code or user by quitting?
before setting it to nothing, do you quit via code?
(maybe posting your code may help understand)

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[pdevries]

Excel is made visible then the user can do as he/she wishes. The object is set to nothing after Excel becomes visible


objExcelApp.Visible = true
set objExcelApp = nothing
End Sub

[coolbiz]

Well since you kill the object, it closes Excel too. You can re-create it by set oExcelObj = new Excel.Application and set the .Visible = True again. I'm still confuse with your code since you destroy to Excel object right after you set .Visible. How does your user do the modification?

-Cool Bizs

[pdevries]

It works fine the first time. It's every subsequent time the routine is run (while vb app is still running) is when the thing craps out. I'll try it without killing the object though. Thanks

[dammansky]

Does the program run on windows 2000 (with office 2000)?
Bacause we have this problem as well, but if we run it on windows NT 4, the excel.exe is gone.

[Cimperiali]

try this:
Option Explicit
Private objExcelApp As Excel.Application
Private Sub Command1_Click()
If objExcelApp Is Nothing Then
Set objExcelApp = Excel.Application
End If

objExcelApp.Visible = True
If objExcelApp.WindowState = xlMinimized Then
objExcelApp.WindowState = xlMaximized
End If
End Sub
'set it to nothing only when closing form
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
If objExcelApp.Visible = False Then
If Not objExcelApp Is Nothing Then
Set objExcelApp = Nothing
End If
Else
Cancel = True
End If
End Sub


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[pdevries]

I tried that code (not setting the object to nothing) and the same problem occurs: it works fine the first time but the second time Excel hides itself--it doesn't close, just hides. Ctrl-Alt-Del reveils that Excel is still running. If you try to end its task you get a message "Would you like to save your worksheet".


To answer your question, dammansky, I'm using VB6 and I'm running this app on Win98SE and Win2000 with Excel 8

[Cimperiali]

"with Excel 8"
You mean you have servicepack 4 for vb? May be you can try downloading sp5. But I am afraid this is a matter for Iouri...





Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[coolbiz]

Hi,

I guess I still don't understand what exactly you're doing. You have a VB application that hooks up to Excel and you want it to be visible so that user can do changes right? Then your program will provide a way to close the Excel once the user is done. Is that right? Maybe you can post your objective and pretty sure a lot of people in this forum can help you with the logic :)

-Cool Bizs

[pdevries]

I just double checked. It's Excel 9 (Excel 2000).

[pdevries]

This procedure takes data from an array and inserts it into an Excel worksheet. When it's done inserting the data it makes Excel visible so the user can do what they wish with it (ie. extra editing, save, print, etc...).

I Originally de-referenced the Excel objects that I used but under suggestion I tried without de-referencing the objects and there was no difference.

I'd paste the code but there's a lot of extra crap going on in the procedure that works fine. The only part that doesn't is at the end where Excel is to be made visible. I know this is true because I have a progress indicator that says what the procedure is currently doing. Again, it works fine the first time around but on the second try you see Excel flash on the screen and then it disappears. Excel's task is shown in the task list (Ctrl-Alt-Del under Win98 and Task Manager under Win2000).

At the beginning of the code I'm doing this:

Dim objExcelApp as Excel.Application
Dim objExcelWkBk as Excel.Workbook
Dim objExcelSheet as Excel.Worksheet

on error resume next
set objExcelApp = new Excel.Application
set objExcelWkBk = Excel.Workbooks.Open(App.Path & "\bom.xls")
MsgBox Err.Description & ".", vbInformation, "Export to Excel"
Exit Sub
End If




And at the end:

objExcelApp.Visible = true
End Sub




I did have the set objExcelApp = nothing

just before End Sub but it did nothing.


Thanks to all who are taking the time to help :)

[Cimperiali]

I think I found out...
'here your matter: you did not refer workbook to setted excel variable!
Set objExcelWkBk = objExcelApp.Workbooks.Open(App.Path & "\boom.xls")

'instead of
'set objExcelWkBk = Excel.Workbooks.Open(App.Path & "\bom.xls")
' and on closing:
'workbook is not nothing
If Not objExcelWkBk Is Nothing Then
Set objExcelWkBk = Nothing
End If

If Not objExcelApp Is Nothing Then
'excel is not quitted!
objExcelApp.Quit
Set objExcelApp = Nothing
End If


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[pdevries]

Using objExcelApp to open the workbook instead of the Excel object did the trick.


Thanks all

[coolbiz]

Hmm i tested almost the same code as yours and it worked fine. The only line that is different is I'm opening the workbook by using the objExcelApp instead of Excel object itself. not sure if that will help you. Other than that, I've no idea what else could be wrong.

-Cool Bizs