Problems in VB.net land with Excel charting!

[korcutt]

Howdy All,
I've been tsked with producing an XY scatter Chart in Excel via a VB.net application.

I have succesfully started Excel and imported the data to chart. I have also created a chrat and set it's type to xlXYScatterSmoothNoMarkers, which is what I want.

I have having MAJOR problems setting the XValue Range and the Value Range of the chart via code... I've tried lokking at and imulating the macro capture of doing this, but when I get over into the .net land it does NOT seem to work!!!!

Dim oXL As Excel.Application
      Dim oWB As Excel.Workbook
      Dim oSheet As Excel.Worksheet
      Dim oRng As Excel.Range
 
      ' Start Excel and get Application object.
      oXL = CreateObject("Excel.Application")
      oXL.Visible = True
 
      ' Get a new workbook.
      'oWB = oXL.Workbooks.Add
      oWB = oXL.Workbooks.OpenXML(job.Item("JobDir").InnerText & "\" & "vps.submitter." & job.Item("id_job").InnerText & ".xml")
 
 
      oSheet = oWB.ActiveSheet
      Dim rowIndex As Long
      Dim DisplCol As Long
      Dim ForceCol As Long
 
      Dim startRowIndex As Long
      Dim endRowIndex As Long
      Dim TLMAXRow As Long
 
      'Misc. code used here to set the column... (ie remove from this example...)
       DisplCol = 2
 
      'Misc. code used here to set the row... (ie remove from this example...)
      TLMAXRow = 33
 
      'Misc. code used here to set the row... (ie remove from this example...)
      startRowIndex = 3
 
      'Misc. code used here to set the row... (ie remove from this example...)
      endRowIndex = 83
 
      Dim NewCol As String
      Dim NewColRange As String
 
      'the ColumnLetter function is one I wrote to convert a number to a Letter for the Column, ie. 3 = "C"
      NewCol = ColumnLetter(DisplCol + 1)
      NewColRange = NewCol & ":" & NewCol
 
      'MsgBox("NewCol : " & NewCol & vbCrLf & "NewColRange: " & NewColRange)
 
      oSheet.Range(NewColRange).Insert()
 
      'Misc. code used here to set the column... (ie remove from this example...)
      ForceCol = 3
 
 
      Dim NewChart As Excel.Chart
 
      NewChart = oWB.Charts.Add
      NewChart.Activate()
 
      NewChart.ChartType = XlChartType.xlXYScatterSmoothNoMarkers
 
      Dim XValRange As Range
      Dim ValRange As Range
 
      XValRange = oSheet.Range(NewCol & startRowIndex, NewCol & endRowIndex)
      ValRange = oSheet.Range(ColumnLetter(ForceCol) & startRowIndex, ColumnLetter(ForceCol) & endRowIndex)
 
 
      Dim serCollect As Excel.Series
      serCollect = NewChart.SeriesCollection(1)
 
      Dim vrange As String
      Dim xrange As String
 
      vrange = "=" & oSheet.Name & "!R" & startRowIndex & "C" & ForceCol & ":R" & endRowIndex & "C" & ForceCol
      MessageBox.Show(vrange)
 
      xrange = "=" & oSheet.Name & "!$" & NewCol & "$" & startRowIndex & ":$" & NewCol & "$" & endRowIndex
      MessageBox.Show(xrange)
 
 
      'Heres where the "FUN" starts...  I can't get either of the following lines to work!!!
      serCollect.Values = vrange
      serCollect.XValues = xrange
 
      'I have also tried:
      'serCollect.Values = ValRange
      'serCollect.XValues = XValRange
      ' To NO aval, that didn't work either...
 
      ' And I tried:
      'serCollect.Values = ValRange.value
      'serCollect.XValues = XValRange.value
      '  Guess what.... that didn't work either...

So I've tried a few different ways to do this with strings and ranges, neither WORK!!!

Any Ideas out there on how to set the series XValues and Values out there????

[DKenny]

Quick questions
1/ How many columns in your XML file?

2/ If more than 2, then how do you determine which 2 columns contain the chart data?

3/ Are all rows in the xml file used in the chart?

4/ If not, then how do you determine which rows contain the chart data?

[korcutt]

Ok,
I was trying to save the forum at looking at misc. code... here's the full code to find the various colums and rows of where the data is...

I do know that in row 2 are my column headings... and that in column "E" is where I will find the word "TLMAX"

In my first post I said I had remove some of the code (For simplicity sakes!)
Sorry I wasn't clear on that and for any confusion as a result!

foundResults = oSheet.Range("2:2").Find(what:="displacement", LookIn:=XlFindLookIn.xlValues, lookat:=XlLookAt.xlPart, MatchCase:=False)
      If Not foundResults Is Nothing Then
         DisplCol = foundResults.Column
         'MsgBox("Displacement Column: " & DisplCol)
      Else
         MsgBox("Not Found")
      End If
 
 
      foundResults = oSheet.Range("E:E").Find(what:="TLMAX", LookIn:=XlFindLookIn.xlValues, lookat:=XlLookAt.xlWhole, MatchCase:=False)
      If Not foundResults Is Nothing Then
         TLMAXRow = foundResults.Row
         'MsgBox("TLMAX Row: " & TLMAXRow)
      Else
         MsgBox("Not Found")
      End If
 
      foundResults = oSheet.Range("A:A").Find(what:="0", LookIn:=XlFindLookIn.xlValues, lookat:=XlLookAt.xlWhole, MatchCase:=False)
      If Not foundResults Is Nothing Then
         startRowIndex = foundResults.Row
         'MsgBox("Start Row Index  Row: " & startRowIndex)
      Else
         MsgBox("Not Found")
      End If
 
 
      foundResults = oSheet.Range("A:A").End(XlDirection.xlDown)
      If Not foundResults Is Nothing Then
         endRowIndex = foundResults.Row
         'MsgBox("End Row Index  Row: " & endRowIndex)
      Else
         MsgBox("Not Found")
      End If

So that's the code to go out and find the extents of the data
I want the displacement column to be my x values (in this case column 2)
and the Y Values are in the "Force" column (in this case 3)

The data starts on row 3 and ends on row 83 (in this case... the next file will be different and the code hould find the extents properly)

[DKenny]

OK
Try this.
You are doing way too much to find the range for the chart. All you really need are the Starting Column number, the starting row number and the ending row number.

I have included an example file.

Sub BuildScatterChart()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim lStartCol As Long
Dim lStartRow As Long
Dim lEndRow As Long
Dim rngData As Range
Dim chtScat As Chart
    
    ' Start Excel and get Application object.
    Set oXL = New Excel.Application
    oXL.Visible = True
    
    ' Get a new workbook.
    'oWB = oXL.Workbooks.Add
    Set oWB = oXL.Workbooks.OpenXML(job.Item("JobDir").InnerText & "\" & "vps.submitter." & job.Item("id_job").InnerText & ".xml")
    
    
    oSheet = oWB.ActiveSheet
 
    'Find the starting column number by looking for
    ' "displacement" in Row 2
    lStartCol = oSheet.Rows(2).Find(what:="displacement", _
                                        LookIn:=XlFindLookIn.xlValues, _
                                        lookat:=XlLookAt.xlPart, _
                                        MatchCase:=False).Column
                                        
    'Find the starting Row number by looking for
    ' a value of 0 in column A
    lStartRow = oSheet.Columns(1).Find(what:="0", _
                                        LookIn:=XlFindLookIn.xlValues, _
                                        lookat:=XlLookAt.xlWhole, _
                                        MatchCase:=False).Row
    
    'Find the ending Row number by looking for
    ' a "TLMAX" in column E
    lEndRow = oSheet.Columns(5).Find(what:="TLMAX", _
                                        LookIn:=XlFindLookIn.xlValues, _
                                        lookat:=XlLookAt.xlWhole, _
                                        MatchCase:=False).Row
    
    'Set the Data range to the range between:
        'The cell that is the intersection of the starting Row and Column
    'and
        'The cell that is the intersection of the ending Row and the next Column
    With oSheet
        Set rngData = .Range(.Cells(lStartRow, lStartCol), .Cells(lEndRow, lStartCol + 1))
    End With
    
    'Add a chart
    Set chtScat = ThisWorkbook.Charts.Add
    
    With chtScat
        .ChartType = xlXYScatterSmoothNoMarkers
        
        'set the datasource for the chart to the range defined above
        .SetSourceData rngData
        
        'Do what ever else you need with the chart
        .HasLegend = False
        .HasTitle = False
    End With
End Sub