Vb.net add a second line to a chart

Question

0.00/5 (No votes)

See more:

I'm trying to graph two lines from this data

Here is my excel spreadsheet ----
sheet 1
time	value1
0	6
1	6
2	7
3	6
4	5
5	6
6	7
7	6
8	5
9	4
10	3
sheet 2------------------------------
time	value2
0	5
1	5
2	6
3	5
4	4
5	5
6	6
7	5
8	4
9	3
10	2

What I have tried:

Here is the code that generates one graph line from sheet 1

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp.Visible = True
        xlWorkBook = xlApp.Workbooks.Open("L:\x_GGNS_addons\WorkingJunk\book1.xlsx")
        xlWorkSheet = CType(xlWorkBook.Sheets("sheet1"), Excel.Worksheet)

        Dim xlcharts = xlWorkSheet.ChartObjects
        Dim myChart As Excel.ChartObject = xlcharts.Add(180, 80, 300, 250)
        Dim chartPage As Excel.Chart = myChart.Chart
        chartPage.Activate()


        With chartPage
            Dim range1 As Excel.Range
            Dim range2 As Excel.Range
            Dim chartRange As Excel.Range
            'range1 = xlWorkSheet.Range("$A$1", "$A$10")
            range1 = xlWorkSheet.Range("sheet1!$A$1:$A$10")
            'range2 = xlWorkSheet.Range("$B$1", "$B$10")
            range2 = xlWorkSheet.Range("sheet1!$B$1:$B$10")
            chartRange = xlApp.Union(range1, range2)
            .SetSourceData(chartRange, Excel.XlRowCol.xlColumns)
            .ApplyCustomType(Excel.XlChartType.xlXYScatterLines)
            .Location(Excel.XlChartLocation.xlLocationAsNewSheet, "chart1")
          
        End With
    End Sub
End Class

The code above generates the graph as expected.
Here is the macro that adds the second line

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveChart.ChartArea.Select
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).XValues = "=Sheet2!$A$2:$A$12"
    ActiveChart.FullSeriesCollection(2).Values = "=Sheet2!$B$2:$B$12"
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$A$2:$A$12"
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet2!$B$1"
End Sub

For the life of me I cannot figure out how to add the second line in Visual basic!
HELP!
Robert

Posted 28-Feb-18 3:09am

Robert Goldman

Updated 28-Feb-18 8:58am

Add a Solution

Comments

Maciej Los 28-Feb-18 10:59am

DO you want to create chart object inside worksheet, or you want to create a sheet-chart?

Robert Goldman 28-Feb-18 12:49pm

I created a chart called "chart1" with the line from sheet1. I want to add a second line to "chart1" from the data from sheet2.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2018-02-28T08:59:00

As i mentioned in my comment to the question, there are 2 types of charts in Excel: embedded chart and a chart sheet. See: Excel Dashboard Templates What is an Excel chart sheet vs. an embedded chart? - Excel Dashboard Templates[^]

Depending on that, the method of adding source of chart lines/curves may differ.
See: Creating Charts in Microsoft Office Excel 2003 Using Visual Basic for Applications Code[^]

Good luck!

[EDIT]

Try this:

VB.NET

 Dim xlApp As Excel.Application, xlWbk As Excel.Workbook, xlChart As Excel.Chart
 Dim sFileName As String = "FullPathAndFileName.xlsx" '

 Try
     xlApp = New Excel.Application
     ''uncomment below line if workbook is close
     xlWbk = xlApp.Workbooks.Open(sFileName)
     'Create a new chart.
     xlChart = xlWbk.Charts.Add()
     With xlChart
         .Name = "MyChart_" & xlWbk.Charts.Count
         .ChartType = 4 'xlLine
         .HasTitle = True
         .ChartTitle.Text = "Time And Value"
         .SeriesCollection(1).Name = "=Sheet1!$B$1"
         .SeriesCollection(1).Values = "=Sheet1!$B$2:$B$12"
         .SeriesCollection(2).Name = "=Sheet2!$B$1"
         .SeriesCollection(2).Values = "=Sheet2!$B$2:$B$12"
         .Axes(1, 1).HasTitle = True
         .Axes(1, 1).AxisTitle.Characters.Text = "Time"
         .Axes(2, 1).HasTitle = True
         .Axes(2, 1).AxisTitle.Characters.Text = "Value"

     End With
     xlApp.Visible = True

 Catch ex As Exception
     Console.WriteLine("{0}", ex.Message)

 Finally
     xlChart = Nothing
     xlWbk = Nothing
     xlApp = Nothing
End Try

Above code draws a chart with 2 lines ;)