How to set the XValues of a series in excel using

Question

I am trying to create a XY-Scatter chart with two col of data. The first col is X from cell A2 to A30, the second col is Y from cell B2 to B30 in the excel sheet.

I am able to create the chart but it is plotting with two series of data, it is taking col X as one series, and col Y as another series. Because I don't know how the syntax works in vb.net and I could not find documents on how to do this in vb.net, I got some ideas from vba documentations( it can be defined like this in vba :

     Charts("Chart1").SeriesCollection(1).XValues =_Worksheets("Sheet1").Range("B1:B5")

and generated the following lines.

So I tried to set the XValues of the series with the line

    xlApp.ActiveChart.SeriesCollection(1).XValues = xlWorkSheet.Range("$A$2", "$A$30")
    xlApp.ActiveChart.SeriesCollection(1).Values = xlWorkSheet.Range("$B$2", "$B$30")

But it is throwing errors at me: COMException was unhandled on the above lines. I am not sure what I did wrong, so please help.

Here is the code block to generate the chart. basically, it's reading in a excel file and then creating a chart using the data in the file.

Private Sub Create_Chart_Click(sender As Object, e As EventArgs) Handles Create_Chart.Click
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    xlApp = New Excel.ApplicationClass

    '~~> Add a New Workbook
    xlWorkBook = xlApp.Workbooks.Open("C:\Test_data.xlsx")

    'Display Excel
    xlApp.Visible = True

    '~~> Set the relebant sheet that we want to work with
    xlWorkSheet = xlWorkBook.Sheets("Sheet1")

    With xlWorkSheet

        '~~> Inserting a Graph
        .Shapes.AddChart.Select()

        '~~> Formatting the chart
        With xlApp.ActiveChart
            '~~> Make it a Line Chart
            .ApplyCustomType(Excel.XlChartType.xlXYScatterSmoothNoMarkers)

            '~~> Set the data range
            xlApp.ActiveChart.SeriesCollection(1).Name = "X-Y"
            xlApp.ActiveChart.SeriesCollection(1).XValues = xlWorkSheet.Range("$A$2", "$A$30")
            xlApp.ActiveChart.SeriesCollection(1).Values = xlWorkSheet.Range("$B$2", "$B$30")

            '~~> Fill the background of the chart
            xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.ObjectThemeColor = _
            Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorBackground1 '<~~ Grey
            xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.TintAndShade = 0
            xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.Brightness = -0.150000006
            xlApp.ActiveChart.ChartArea.Format.Fill.Transparency = 0
            xlApp.ActiveChart.ChartArea.Format.Fill.Solid()
            xlApp.ActiveChart.SeriesCollection(1).Trendlines.Add()
            xlApp.ActiveChart.SeriesCollection(1).Trendlines(1).Type = Microsoft.Office.Interop.Excel.XlTrendlineType.xlPolynomial
            xlApp.ActiveChart.SeriesCollection(1).Trendlines(1).Order = 2
            'xlApp.ActiveChart.SeriesCollection(1).Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse





            '~~> Make the corners of the Chart Rount
            '.Parent.RoundedCorners = True

            '~~> Removing lines and the back color so plot area shows char's background color
            With .PlotArea
                .Format.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
                .Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
            End With

            '~~> Removing the major gridlines
            '.Axes(Excel.XlAxisType.xlValue).MajorGridlines.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse

            '~~> Making the series line smooth
            '.SeriesCollection(1).Smooth = True

            '~~> Formatting the legend
            With .Legend
                With .Format.TextFrame2.TextRange.Font.Fill
                    .Visible = Microsoft.Office.Core.MsoTriState.msoTrue
                    .ForeColor.RGB = RGB(0, 0, 0)
                    .Transparency = 0
                    .Solid()
                End With

                With .Format.Fill
                    .Visible = Microsoft.Office.Core.MsoTriState.msoTrue
                    .ForeColor.ObjectThemeColor = Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorBackground1
                    .ForeColor.TintAndShade = 0
                    .ForeColor.Brightness = -0.25
                    .Transparency = 0
                    .Solid()
                End With
            End With

            '~~> Change the format of Y axis to show $ signs
            '.Axes(Excel.XlAxisType.xlValue).TickLabels.NumberFormat = "#,##0.00"

            '~~> Underline the Chart Title
            ' .ChartTitle.Format.TextFrame2.TextRange.Font.UnderlineStyle = _
            ' Microsoft.Office.Core.MsoLineStyle.msoLineSingle
        End With
    End With

End Sub

Thanks

Answer 1

XValues actually can be an Array for example:

Dim MyXVal() as string={"123","345","567"}
xlApp.ActiveChart.SeriesCollection(1).XValues = MyXVal

Answer 2

Please Try like This

xlApp.ActiveChart.SeriesCollection(1).XValues = "={""tes1"",""Test2""}"

or

MyVal="{""" & xlWorkSheet.range("A2") & """"
MyVal=MyVal & ",""" & xlWorkSheet.range("A30") & """}"
xlApp.ActiveChart.SeriesCollection(1).XValues = MyVal