VB.NET: Excel Crashes when Updating Data in a Word Chart

Question

Update: Releasing objects has no effect on Excel crashing. The problematic line is:

Dim wChartData = wChart.ChartData

---

I have written a VB.Net application that opens a word document, iterates through the inline shapes, and updates each chart with data from the database. Sometimes Excel will crash when opening the sheet containing the chart data. Can anyone tell me how I fix this?

Here is the code that iterates through the shapes:

wApp = New Word.Application
wApp.Visible = True
wDoc = wApp.Documents.Add("Some_File_Name.docx")
Console.WriteLine("Updating Charts")
Dim chartName As String
    For Each wShape As Word.InlineShape In wDoc.InlineShapes
        If wShape.HasChart = Core.MsoTriState.msoTrue Then
            If wShape.Chart.HasTitle Then
                chartName = wShape.Chart.ChartTitle.Text
            Else
                chartName = "NO_TITLE"
            End If
            UpdateChart(wShape.Chart, reportID, reportTitle, 
                        reportUser, curriculumYear, chartName)
        End If
    Next

The UpdateChart subroutine grabs a SQL query and some options related to the chart, then fires off the FillChartData subroutine below:

Public Sub FillChartData(ByRef wChart As Word.Chart, ByVal sql As String, 
                         Optional ByVal addDataPointsToLabels As Boolean = False)

    If sql = "" Then Exit Sub

    Dim cmd = O.factory.CreateCommand()
    cmd.CommandText = sql

    cmd.Connection = O.con
    O.factory.CreateDataAdapter()

    Dim adapter = O.factory.CreateDataAdapter
    adapter.SelectCommand = cmd

    Dim dt As New System.Data.DataTable()
    Dim ds As New System.Data.DataSet()

    adapter.Fill(ds, "report_name")
    dt = ds.Tables(0)

    Dim wChartData = wChart.ChartData
    Dim wChartWb As Excel.Workbook = wChartData.Workbook
    Dim wChartSheet As Excel.Worksheet = wChartWb.Sheets(1)

    Dim title As String = "No title"
    If wChart.HasTitle Then title = wChart.ChartTitle.Text.ToString

    Dim r As Excel.Range
    r = wChartSheet.Range("A1")
    r.CurrentRegion.Clear()

    For i = 0 To dt.Columns.Count - 1
        Dim c As System.Data.DataColumn = dt.Columns(i)
        r.Offset(0, i).Value2 = c.ColumnName
    Next

    r = wChartSheet.Range("A2")
    For Each row As System.Data.DataRow In dt.Rows
        For i = 0 To row.ItemArray.Count - 1
            r.Offset(0, i).Value2 = row.Item(i)
        Next
        r = r.Offset(1)
    Next
    r = wChartSheet.Range("A1")

    If addDataPointsToLabels Then
        While r.Value <> ""
            r.Value &= " " & r.Offset(1).Value
            r = r.Offset(0, 1)
        End While
    End If

    wChartWb.Close()
    releaseObject(r)
    releaseObject(wChartSheet)
    releaseObject(wChartWb)
    releaseObject(wChartData)
    r = Nothing
    wChartSheet = Nothing
    wChartWb = Nothing
    wChartData = Nothing
    GC.Collect()
End Sub

The releaseObject subroutine is as follows:

Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        MessageBox.Show(ex.ToString)
        obj = Nothing
    End Try
End Sub

And here's the crash report:

Problem signature:
  Problem Event Name:   APPCRASH
  Application Name: EXCEL.EXE
  Application Version:  15.0.5007.1000
  Application Timestamp:    5a5eb36d
  Fault Module Name:    EXCEL.EXE
  Fault Module Version: 15.0.5007.1000
  Fault Module Timestamp:   5a5eb36d
  Exception Code:   c0000005
  Exception Offset: 002b71c8
  OS Version:   6.1.7601.2.1.0.256.4
  Locale ID:    1033

Additional information about the problem:
  LCID: 1033
  skulcid:  1033

Read our privacy statement online:
  http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

If the online privacy statement is not available, please read our privacy statement offline:
  C:\Windows\system32\en-US\erofflps.txt

Thanks for your help!

Answer 1

You need to Activate the Word ChartData object to begin the inter-process communication between Word and Excel.

The example below is a simplified demonstration of code pattern and contains no error handling. This example also demonstrates releasing out of scope COM objects via the garbage collector. See this answer for more discussion on this COM clean-up procedure.

This code was verified against Office 2007.

Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel
Imports Word = Microsoft.Office.Interop.Word

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        InterOpWork("Embedded Excel Chart.docx")
        COMCleanup()
    End Sub

    Sub InterOpWork(filePath As String)
        Dim appWord As New Word.Application

        Dim doc As Word.Document = appWord.Documents.Open((filePath))
        Dim shp As Word.InlineShape = doc.InlineShapes(1)
        Dim ch As Word.Chart = shp.Chart
        Dim chData As Word.ChartData = ch.ChartData

        chData.Activate()  ' **** This is what your code is missing

        Dim wb As Excel.Workbook = DirectCast(chData.Workbook, Excel.Workbook)
        Dim appExcel As Excel.Application = DirectCast(wb.Application, Excel.Application)

        Dim ws As Excel.Worksheet = DirectCast(wb.Worksheets("Sheet1"), Excel.Worksheet)
        Dim rng As Excel.Range = ws.Range("B2:B4")
        Dim dataToChange As Object(,) = DirectCast(rng.Value2, Object(,))
        For i As Int32 = dataToChange.GetLowerBound(0) To dataToChange.GetUpperBound(0)
            dataToChange(i, 1) = i * 2 + (5 - i)
        Next
        rng.Value = dataToChange

        wb.Save()
        wb.Close(False)
        appExcel.Quit()

        doc.Save()
        doc.Close(False)
        appWord.Quit()
    End Sub

    Private Sub COMCleanup()
        Do
            GC.Collect()
            GC.WaitForPendingFinalizers()
        Loop While Marshal.AreComObjectsAvailableForCleanup
    End Sub
End Class