VB.net formatting series and removing 1 legend entry

Question

I've got some VB.NET code which uses Excel to create a chart from some data. The chart works how it should but I'm finding the switch from VBA to VB formatting-wise difficult.

I want just series 1 in the legend to be deleted and the data in the chart itself to either have transparency set to 100 or to have fill set to no fill. I've played with charpage.seriescollection(1).format.fill etc. but I'm not getting it.

My code:

Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
    Dim xlWorkBook As Excel.Workbook = Nothing
    Dim xlWorkbooks As Excel.Workbooks = Nothing
    Dim xlWorkSheet As Excel.Worksheet = Nothing

    Dim chartPage As Excel.Chart
    Dim xlCharts As Excel.ChartObjects
    Dim myChart As Excel.ChartObject
    Dim chartRange As Excel.Range
    Dim Chartarea As Excel.ChartArea

xlCharts = xlWorkSheet.ChartObjects
        myChart = xlCharts.Add(10, 80, 900, 500)
        chartPage = myChart.Chart
        chartRange = xlWorkSheet.Range("B1", "E2000")
        chartPage.SetSourceData(Source:=chartRange)
        chartPage.ChartType = Excel.XlChartType.xlBarStacked
        chartPage.ChartWizard(Title:="Gantt Chart of Offshore Operations")
        chartPage.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop

        'Y axis
        With chartPage.Axes(Excel.XlAxisType.xlCategory)
            .categorytype = Excel.XlCategoryType.xlTimeScale
            .BaseUnit = Excel.XlTimeUnit.xlDays
        End With

        'X axis
        With chartPage.Axes(Excel.XlAxisType.xlValue)
            .HasTitle = True
            .AxisTitle.Caption = "Time (Days)"
        End With

score 1 · Accepted Answer · answered Dec 13 '17 at 20:31

I made up some data in Excel, saved it, and used the following code:

Imports Microsoft.Office.Interop

Public Class Form1

    Sub X()
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing

        Try
            xlApp = New Excel.Application()
            xlWorkBook = xlApp.Workbooks.Open("C:\temp\Book1.xlsx")
            Dim xlWorkSheet As Excel.Worksheet = xlWorkBook.Worksheets(1)

            Dim xlCharts As Excel.ChartObjects = xlWorkSheet.ChartObjects
            Dim myChart As Excel.ChartObject = xlCharts.Add(10, 80, 900, 500)
            Dim chartPage As Excel.Chart = myChart.Chart
            Dim chartRange As Excel.Range = xlWorkSheet.Range("B1", "E20")

            chartPage.SetSourceData(Source:=chartRange)
            chartPage.ChartType = Excel.XlChartType.xlBarStacked
            chartPage.ChartWizard(Title:="Gantt Chart of Offshore Operations")

            ' *****************
            myChart.Chart.SeriesCollection(1).Name = ""
            myChart.Chart.SeriesCollection(1).Interior.ColorIndex = Excel.Constants.xlNone

            chartPage.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop

            'Y axis
            With chartPage.Axes(Excel.XlAxisType.xlCategory)
                .categorytype = Excel.XlCategoryType.xlTimeScale
                .BaseUnit = Excel.XlTimeUnit.xlDays
            End With

            'X axis
            With chartPage.Axes(Excel.XlAxisType.xlValue)
                .HasTitle = True
                .AxisTitle.Caption = "Time (Days)"
            End With

            xlWorkBook.SaveAs("C:\temp\Book2")

        Finally
            xlWorkBook.Close()
            xlApp.Quit()

        End Try

    End Sub


    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            X()
        Finally
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
        End Try

    End Sub

End Class

Which generated this:

Which I think is what you wanted.

Credits:

Dude! That's exactly what I was after. Thank you. I've been struggling a bit with the correct commands. Where do you go to mainly get commands for things you don't know? I've been looking on https://msdn.microsoft.com/en-us/library/dd489216.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1 but as I don't have chart1 assigned it doesn't work — Sean Kilburn, Dec 14 '17 at 09:26

VB.net formatting series and removing 1 legend entry

1 Answers1