Can I turn a Microsoft.Office.Interop.Excel.Chart object into a Microsoft.Office.Tools.Excel.Chart object?

Question

Basically I've coded an Excel 2007 project in VB.NET 2010 that allows you to create charts with a fair amount of interactivity. I want the user to be able to save and reopen this workbook and still have that interactivity in any already-created charts, so they don't have to re-create them.

When I create the charts, I use Sheet1.Controls.AddChart(...), which returns a Microsoft.Office.Tools.Excel.Chart with which I can handle events and such. However, when I reopen the file and look through the Sheet1.Controls collection, there are no Chart objects. Accessing the charts through Sheet1.ChartObjects.Chart gives me Interop Charts, when I need the Tools Charts.

Is there a better way to do this? Should I just be using Interop charts from the get-go?

Answer 1

According to the MSDN article, you can use the HasVstoObject and GetVstoObject methods to convert a native object into a host control. The docs don't specifically mention the chart object, though.

Answer 2

Paul, I apologize in advance if I'm not addressing your issue. Up until now I didn't realize there were two types of charts and I'd be interested in understanding the issues with them. At any rate, after some fooling around I was able to create a chart, name it, have it persist and have it respond to events. It does rely on interop charts though:

Public Class ThisWorkbook
    Dim clsChart As cChart

    Private Sub ThisWorkbook_Startup() Handles Me.Startup
        Dim coChartObj As Microsoft.Office.Interop.Excel.ChartObject
        Dim chtMyChart As Microsoft.Office.Interop.Excel.Chart
        Dim i As Int32

        With Globals.Sheet1
            For i = 1 To .ChartObjects.count
                If .ChartObjects(i).Name = "MyChart" Then
                    chtMyChart = .ChartObjects(i).chart
                    Exit For
                End If
            Next i
            If chtMyChart Is Nothing Then
                chtMyChart = .Shapes.AddChart.Chart
                chtMyChart.SetSourceData(.Range("A1:B2"))
                coChartObj = chtMyChart.Parent
                coChartObj.Name = "MyChart"
            End If
            clsChart = New cChart
            clsChart.chtChart = chtMyChart
        End With
    End Sub
End Class

Public Class cChart
    Public WithEvents chtChart As Microsoft.Office.Interop.Excel.Chart

    Private Sub chtChart_Resize() Handles chtChart.Resize
        MessageBox.Show("resize")
    End Sub
End Class