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!!!!

VB Code:
  1. Dim oXL As Excel.Application

  2.       Dim oWB As Excel.Workbook

  3.       Dim oSheet As Excel.Worksheet

  4.       Dim oRng As Excel.Range

  5.  
  6.       ' Start Excel and get Application object.

  7.       oXL = CreateObject("Excel.Application")

  8.       oXL.Visible = True

  9.  
  10.       ' Get a new workbook.

  11.       'oWB = oXL.Workbooks.Add

  12.       oWB = oXL.Workbooks.OpenXML(job.Item("JobDir").InnerText & "\" & "vps.submitter." & job.Item("id_job").InnerText & ".xml")

  13.  
  14.  
  15.       oSheet = oWB.ActiveSheet

  16.       Dim rowIndex As Long

  17.       Dim DisplCol As Long

  18.       Dim ForceCol As Long

  19.  
  20.       Dim startRowIndex As Long

  21.       Dim endRowIndex As Long

  22.       Dim TLMAXRow As Long

  23.  
  24.       'Misc. code used here to set the column... (ie remove from this example...)

  25.        DisplCol = 2

  26.  
  27.       'Misc. code used here to set the row... (ie remove from this example...)

  28.       TLMAXRow = 33

  29.  
  30.       'Misc. code used here to set the row... (ie remove from this example...)

  31.       startRowIndex = 3

  32.  
  33.       'Misc. code used here to set the row... (ie remove from this example...)

  34.       endRowIndex = 83

  35.  
  36.       Dim NewCol As String

  37.       Dim NewColRange As String

  38.  
  39.       'the ColumnLetter function is one I wrote to convert a number to a Letter for the Column, ie. 3 = "C"

  40.       NewCol = ColumnLetter(DisplCol + 1)

  41.       NewColRange = NewCol & ":" & NewCol

  42.  
  43.       'MsgBox("NewCol : " & NewCol & vbCrLf & "NewColRange: " & NewColRange)

  44.  
  45.       oSheet.Range(NewColRange).Insert()

  46.  
  47.       'Misc. code used here to set the column... (ie remove from this example...)

  48.       ForceCol = 3

  49.  
  50.  
  51.       Dim NewChart As Excel.Chart

  52.  
  53.       NewChart = oWB.Charts.Add

  54.       NewChart.Activate()

  55.  
  56.       NewChart.ChartType = XlChartType.xlXYScatterSmoothNoMarkers

  57.  
  58.       Dim XValRange As Range

  59.       Dim ValRange As Range

  60.  
  61.       XValRange = oSheet.Range(NewCol & startRowIndex, NewCol & endRowIndex)

  62.       ValRange = oSheet.Range(ColumnLetter(ForceCol) & startRowIndex, ColumnLetter(ForceCol) & endRowIndex)

  63.  
  64.  
  65.       Dim serCollect As Excel.Series

  66.       serCollect = NewChart.SeriesCollection(1)

  67.  
  68.       Dim vrange As String

  69.       Dim xrange As String

  70.  
  71.       vrange = "=" & oSheet.Name & "!R" & startRowIndex & "C" & ForceCol & ":R" & endRowIndex & "C" & ForceCol

  72.       MessageBox.Show(vrange)

  73.  
  74.       xrange = "=" & oSheet.Name & "!$" & NewCol & "$" & startRowIndex & ":$" & NewCol & "$" & endRowIndex

  75.       MessageBox.Show(xrange)

  76.  
  77.  
  78.       'Heres where the "FUN" starts...  I can't get either of the following lines to work!!!

  79.       serCollect.Values = vrange

  80.       serCollect.XValues = xrange

  81.  
  82.       'I have also tried:

  83.       'serCollect.Values = ValRange

  84.       'serCollect.XValues = XValRange

  85.       ' To NO aval, that didn't work either...

  86.  
  87.       ' And I tried:

  88.       'serCollect.Values = ValRange.value

  89.       'serCollect.XValues = XValRange.value

  90.       '  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????