VB.Net Excel Series Order? RRS feed

  • Question

  • How do I get the Series to not be in the same order as Excel Columns?  

     Dim xlChart As Excel.Chart
            Dim xlWorksheet As Excel.Worksheet
            Dim xlRange As Excel.Range
            xlWorksheet = CType(MyExcel.Sheets("Data"), Worksheet)
            xlChart = CType(MyExcel.Sheets("Chart1"), Chart)
            Dim c1series1 As Excel.Range
            Dim c1series2 As Excel.Range
            Dim c1series3 As Excel.Range
            Dim c1series4 As Excel.Range
            Dim c1series5 As Excel.Range
            Dim c1series6 As Excel.Range
            Dim c1series7 As Excel.Range
            Dim c1series8 As Excel.Range
            Dim c1series9 As Excel.Range
            c1series1 = xlWorksheet.Range("A1", "A" & TextBoxExcelRowAdjustmentCount.Text)
            c1series2 = xlWorksheet.Range("G1", "G" & TextBoxExcelRowAdjustmentCount.Text)
            c1series3 = xlWorksheet.Range("F1", "F" & TextBoxExcelRowAdjustmentCount.Text)
            c1series4 = xlWorksheet.Range("D1", "D" & TextBoxExcelRowAdjustmentCount.Text)
            c1series5 = xlWorksheet.Range("C1", "C" & TextBoxExcelRowAdjustmentCount.Text)
            c1series6 = xlWorksheet.Range("B1", "B" & TextBoxExcelRowAdjustmentCount.Text)
            c1series7 = xlWorksheet.Range("H1", "H" & TextBoxExcelRowAdjustmentCount.Text)
            c1series8 = xlWorksheet.Range("E1", "E" & TextBoxExcelRowAdjustmentCount.Text)
            c1series9 = xlWorksheet.Range("I1", "I" & TextBoxExcelRowAdjustmentCount.Text)
            xlRange = MyExcel.Union(c1series1, c1series2, c1series3, c1series4, c1series5, c1series6, c1series7, c1series8, c1series9)
            xlChart.SetSourceData(xlRange, Excel.XlRowCol.xlColumns)

    Example of above code: Excel Columns B, C, D, E, F, G, H, I

    Desired Order: Excel Columns G, F, D, C, B, H, E, I


    Work Smarter Not Harder

    Friday, February 14, 2020 3:48 PM

All replies

  • Either apply the series in the desired order in the first place, or refer to respective series in the order you want them to appear and apply the series.PlotOrder incrementally.

    When done look at the fourth (normally last) value in series formulas which define the plot order.

    Friday, February 14, 2020 8:48 PM
  • No matter what order I put the c1series1, c1series2 and c1series3 etc.  it puts them in the order of the columns.  I don't know what you mean by series.PlotOrder.   can you provide example with my code?  


    but if I change the spreadsheet to the below I get the chart the way I want it, but this is a bad solution as I need the data to be like above.    


    Work Smarter Not Harder

    Friday, February 14, 2020 9:14 PM