Excel objects time out and become unusable - Help!

[Huw2007]

Hi all, I posted this under the wrong topic before, sorry about that.

I'm writing code for a COM add-in to manipulate Microsoft Excel graph
objects, embedded in Word documents. I need to programmatically open the
graph object and manipulate the underlying chart and worksheet.

When the Excel object is clicked in the document, the following code
displays the number of sheets in the embedded workbook:

Private Sub applicationObject_WindowSelectionChange(ByVal Sel As
Word.Selection) Handles applicationObject.WindowSelectionChange

Dim shp As Word.InlineShape

shp = Sel.InlineShapes.Item(0)

shp.OLEFormat.Activate()

Dim wb As Excel.Workbook = _
CType(shp.OLEFormat.Object, Excel.Workbook)

MsgBox("Number of sheets: " & wb.Sheets.Count)

wb = Nothing
End Sub

After this code executes, and I let the document sit for a few minutes, the
object seems to become corrupted, and I get the following message when I
click it again: "System.Runtime.InteropServices.COMException (0x800A14F0):
The server application, source file, or item cannot be found. Make sure the
application is properly installed, and that it has not been deleted, moved,
or renamed."

I guessed that there was some kind of memory problem, and have tried each of
the following pieces of code after the MsgBox above:

' -- Releasing the workbook using the runtime interop marshal --
Try
Do Until _
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb) <= 0
Loop
Catch
Finally
wb = Nothing
End Try
' -- I also tried using the system marshal to release the wb.Workbooks
' first --

' -- Asking the host application to quit --
wb.Application.Quit()

' -- Setting the reference to NULL which should release the object --
wb = Nothing

' -- Resetting the shape in the Word doc --
shp.Reset()

' -- Saving the document --
applicationObject.ActiveDocument.Save()

' -- Hiding the OLE object --
shp.OLEFormat.DoVerb(Word.WdOLEVerb.wdOLEVerbHide)

If I simply double click the Excel chart to edit it (non-programmatically),
everything seems fine, nothing times out and the Excel process disappears
when Word terminates. But as soon as I retrieve the pointer to the workbook
in code, the problems start. And then the Excel process is not terminated
when Word closes..

Am I missing some important code to finish working with the Excel object
cleanly? I am developing in VS .NET 2003, in VB.NET. I'm using Office 2000
(9.0.8960 SP-3), and .NET Framework 1.1 (with Hotfix). This is really my last
resort so any help or advice at all is appreciated!

Thanks a bunch
Huw

 

[Cindy M.]

Hi =?Utf-8?B?SHV3MjAwNw==?=,

I don't really have a definitive answer for you, but...

At no point is your Excel object truly being released. And unless it's opened in
its own window (instead of in-place in the document) it cannot be released
programmatically. If you open it in its own window, then you can create an
object variable for the Excel application, assign it to wb.Application, then use
the Quit method. That will release Excel from memory correctly.

Beyond that, the only thing I can imagine would be to use
System.Diagnostics.Processes to get hold of the process and force it to quit.
(Analog to what one does manually in the Task Manager).

I'm writing code for a COM add-in to manipulate Microsoft Excel graph
objects, embedded in Word documents. I need to programmatically open the
graph object and manipulate the underlying chart and worksheet.

When the Excel object is clicked in the document, the following code
displays the number of sheets in the embedded workbook:

Private Sub applicationObject_WindowSelectionChange(ByVal Sel As
Word.Selection) Handles applicationObject.WindowSelectionChange

Dim shp As Word.InlineShape

shp = Sel.InlineShapes.Item(0)

shp.OLEFormat.Activate()

Dim wb As Excel.Workbook = _
CType(shp.OLEFormat.Object, Excel.Workbook)

MsgBox("Number of sheets: " & wb.Sheets.Count)

wb = Nothing
End Sub

After this code executes, and I let the document sit for a few minutes, the
object seems to become corrupted, and I get the following message when I
click it again: "System.Runtime.InteropServices.COMException (0x800A14F0):
The server application, source file, or item cannot be found. Make sure the
application is properly installed, and that it has not been deleted, moved,
or renamed."

I guessed that there was some kind of memory problem, and have tried each of
the following pieces of code after the MsgBox above:

' -- Releasing the workbook using the runtime interop marshal --
Try
Do Until _
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb) <= 0
Loop
Catch
Finally
wb = Nothing
End Try
' -- I also tried using the system marshal to release the wb.Workbooks
' first --

' -- Asking the host application to quit --
wb.Application.Quit()

' -- Setting the reference to NULL which should release the object --
wb = Nothing

' -- Resetting the shape in the Word doc --
shp.Reset()

' -- Saving the document --
applicationObject.ActiveDocument.Save()

' -- Hiding the OLE object --
shp.OLEFormat.DoVerb(Word.WdOLEVerb.wdOLEVerbHide)

If I simply double click the Excel chart to edit it (non-programmatically),
everything seems fine, nothing times out and the Excel process disappears
when Word terminates. But as soon as I retrieve the pointer to the workbook
in code, the problems start. And then the Excel process is not terminated
when Word closes..

Am I missing some important code to finish working with the Excel object
cleanly? I am developing in VS .NET 2003, in VB.NET. I'm using Office 2000
(9.0.8960 SP-3), and .NET Framework 1.1 (with Hotfix). This is really my last
resort so any help or advice at all is appreciated!

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail