Graphs with various Y values and one X values in Excel VBA

Question

This is the code i use to create a graph which searches for .csv {created using excel application} file in the path specified. It plots the column 'B' { Y axis } against column 'C' {X-axis}.. I want to one more column 'A' to my Y axis keeping column 'C' as the X axis.. How can i do that???

here is the code...

Sub Draw_Graph()
    Dim strPath As String
    Dim strFile As String
    Dim strChart As String
    Dim i As Integer
    Dim j As Integer

    strPath = "C:\PortableRvR\report\"
    strFile = Dir(strPath & "*.csv")
    i = 1

    Do While strFile <> ""
        With ActiveWorkbook.Worksheets.Add
            With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _
                Destination:=.Range("A1"))
                Parent.Name = Replace(strFile, ".csv", "")
                TextFileParseType = xlDelimited
                TextFileTextQualifier = xlTextQualifierDoubleQuote
                TextFileConsecutiveDelimiter = False
                TextFileTabDelimiter = False
                TextFileSemicolonDelimiter = False
                TextFileCommaDelimiter = True
                TextFileSpaceDelimiter = False
                TextFileColumnDataTypes = Array(1)
                TextFileTrailingMinusNumbers = True
                Refresh BackgroundQuery:=False
                Files(i) = .Parent.Name
                i = i + 1
            End With
        End With
        strFile = Dir
    Loop

    numOfFiles = i - 1
    chartName = "Chart 1"

    For j = 1 To numOfFiles
        strFile = Files(j)
        Sheets(strFile).Select
        Plot_y = Range("B1", Selection.End(xlDown)).Rows.Count
        Plot_x = Range("C1", Selection.End(xlDown)).Rows.Count

        Sheets("GraphDisplay").Select
        If j = 1 Then ActiveSheet.ChartObjects(chartName).Activate
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(j).Name = strFile
        ActiveChart.SeriesCollection(j).XValues = Sheets(strFile).Range("C1:C" & Plot_x)
        ActiveChart.SeriesCollection(j).Values = Sheets(strFile).Range("B1:B" & Plot_y)
        ActiveChart.SeriesCollection(j).MarkerStyle = -4142
        ActiveChart.SeriesCollection(j).Smooth = False
    Next j

    ActiveSheet.ChartObjects(chartName).Activate
    ActiveChart.Axes(xlValue).DisplayUnit = xlMillions
    ActiveChart.Axes(xlValue).HasDisplayUnitLabel = False
End Sub

Answer 1

Not for points

I was planning to post this as a comment (and hence do not select this as an answer. All credit to @Aprillion) but the comment would not have formatted the code as this post would have done.

Whenever you add a series as Aprillion mentioned you have to also add one more line. I just tested this with small piece of data and it works.

'<~~ You have to call this everytime you add a new series
ActiveChart.SeriesCollection.NewSeries 
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$B$1:$B$6"
'<~~ You have to call this everytime you add a new series
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "=Sheet1!$A$1:$A$6"

Also since there is a huge difference between your Series 1 Data and Series 2 data (as per the snapshot), the 2nd series will be very close to X Axis.

Hope this is what you wanted?

FOLLOWUP

Is this what you are trying?

Dim files(1 To 20) As String
Dim numOfFiles As Integer
Dim chartName As String, shName as String

Sub Time_Graph()
    Dim strPath As String, strFile As String, strChart As String
    Dim i As Long, j As Long, n As Long

    strPath = "C:\PortableRvR\report\"
    strFile = Dir(strPath & "*.csv")

    i = 1

    Do While strFile <> ""
        With ActiveWorkbook.Worksheets.Add
            shName = strFile
            ActiveSheet.Name = Replace(shName, ".csv", "")
            With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _
                Destination:=.Range("A1"))
                .Name = Replace(strFile, ".csv", "")
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = False
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = True
                .TextFileSpaceDelimiter = False
                .TextFileColumnDataTypes = Array(1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
                files(i) = .Parent.Name
                i = i + 1
            End With
        End With
        strFile = Dir
    Loop

    numOfFiles = i - 1
    chartName = "Chart 1"

    For j = 1 To numOfFiles
        If n = 0 Then n = j Else n = n + 2
        strFile = files(j)
        Sheets(strFile).Select
        Plot_y = Range("B1", Selection.End(xlDown)).Rows.Count
        Plot_x = Range("C1", Selection.End(xlDown)).Rows.Count

        Sheets("GraphDisplay").Select

        If j = 1 Then ActiveSheet.ChartObjects(chartName).Activate

        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(n).Name = strFile & " - Col B Values"
        ActiveChart.SeriesCollection(n).XValues = "=" & strFile & "!$C$1:$C$" & Plot_x
        ActiveChart.SeriesCollection(n).Values = "=" & strFile & "!$B$1:$B$" & Plot_y

        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(n + 1).Name = strFile & " - Col A Values"
        ActiveChart.SeriesCollection(n + 1).XValues = "=" & strFile & "!$C$1:$C$" & Plot_x
        ActiveChart.SeriesCollection(n + 1).Values = "=" & strFile & "!$A$1:$A$" & Plot_y

        ActiveChart.SeriesCollection(j).MarkerStyle = -4142
        ActiveChart.SeriesCollection(j).Smooth = False
        ActiveChart.SeriesCollection(n + 1).MarkerStyle = -4142
        ActiveChart.SeriesCollection(n + 1).Smooth = False
    Next j

    ActiveSheet.ChartObjects(chartName).Activate
    ActiveChart.Axes(xlValue).DisplayUnit = xlMillions
    ActiveChart.Axes(xlValue).HasDisplayUnitLabel = False
End Sub

Answer 2

you can add 2 series for every file (j and j+1 inside for j = 1 to 2*numOfFiles step 2) and repeat everything for j+1 series except:

ActiveChart.SeriesCollection(j).Values = Sheets(strFile).Range("A1:A" & Plot_y)
ActiveChart.SeriesCollection(j+1).Values = Sheets(strFile).Range("B1:B" & Plot_y)