[RESOLVED] Excel automation 2 dot issue with Charts collection

[topshot]

I'm getting close to what I want but not quite there. I can get it to work if I'm just adding a ChartObject to the current sheet, but I want to create a new Chart sheet and can't seem to get the declarations and/or casting to come out right (even if I turn off Option Strict) so it leaves the Excel process running.

Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing
        Dim xlRange1 As Excel.Range = Nothing
        Dim xlInterior As Excel.Interior = Nothing
        Dim xlColumns As Excel.Range = Nothing
 
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)
 
        xlApp.Visible = False
 
        xlWorkSheets = xlWorkBook.Sheets
        xlWorkSheet = CType(xlWorkSheets(1), Excel.Worksheet)
 
        'process the data for the chart into Sheet1
 
        Dim xlChart As Excel.Chart = Nothing
        xlChart = CType(xlWorkBook.Charts.Add(After:=xlApp.ActiveSheet), Excel.Chart)

The last line is what causes me problems. I tried several different ways to declare the Charts collection (for example, xlCharts = CType(xlWorkBook.Charts, Excel.Charts), but it would eventually give me an error.

This operation failed because the QueryInterface call on the COM component for the interface with IID '{0002086D-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002

A minor complaint is that it is also not putting the Chart sheet after the ActiveSheet even though that is returning "Sheet1" as it should.

So what am I missing?

[topshot]

A workaround that is not ideal is creating a new sheet and moving the chart to it. However, this makes it look like a normal embedded chart (ie, sitting on top of a sheet of empty cells) rather than a chart sheet.

xlWorkSheet = CType(xlWorkSheets.Add, Excel.Worksheet)
            xlWorkSheet.Name = "Torque"
            xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Torque")

If I do the following, it automatically creates a new chart sheet, but then I have the original issue of leaving the Excel process hanging since this new sheet wasn't explicitly declared.

xlChart.Location(Excel.XlChartLocation.xlLocationAsNewSheet, "Torque")

Creating a chart sheet is getting really annoying.

[topshot]

I thought I had it but was denied again! What the heck is the difference between what I had done above (copied here)

xlWorkSheet = CType(xlWorkSheets.Add, Excel.Worksheet)
            xlWorkSheet.Name = "Torque"
            xlChart.Location(Excel.XlChartLocation.xlLocationAsObject, "Torque")

and this? (Note: in this example objChart1 was the Excel.Chart variable for the chart I had created on Sheet1. xlChart is Excel.Chart that is for the chart sheet)

xlChart = CType(xlWorkSheets.Add(Type:=Excel.XlSheetType.xlChart), Excel.Chart)
            xlChart.Name = "Torque"
            objChart1.Location(Excel.XlChartLocation.xlLocationAsObject, "Torque")
            Marshal.FinalReleaseComObject(xlChart)
            xlChart = Nothing

I even added the last 2 lines to keep it from holding the Excel process but that doesn't help.

[topshot]

Well, I guess nevermind. I don't know what I did exactly, but it is working now in either of these options. A chart sheet is being created without holding Excel open.

'I did make all the names more consistent everywhere
 
        xlChartFlow.Location(Excel.XlChartLocation.xlLocationAsNewSheet, "Flow")
 
OR
 
        xlChartSheet = CType(xlWorkSheets.Add(Type:=Excel.XlSheetType.xlChart), Excel.Chart)
        xlChartSheet.Name = "Flow"
        xlChartFlow.Location(Excel.XlChartLocation.xlLocationAsObject, "Flow")

[topshot]

This post is just to remind me of this advice from TnTinMN if I ever need to deal with Excel again.