title: VBScript for Excel: How do I choose the source data (.SetSourceData)?
body: <p>I've searched for an answer to this question on both Google and here, without any success. If it's been asked before, then I apologized.</p>
  
  <p>I'm trying to automate some administration tasks using VBScript. The purpose of this particular script is to take some usage statistics from a text file (with text and number columns) and make a line chart out of the data. Creating an excel file and loading the data works fine, but I'm having trouble with creating a chart: I don't understand how to choose the source data, and I keep running into syntax errors. There's plenty of info on the Internet on how to do this in VBA, and it's obvious from a recorded macro. But I just can't do it with VBScript. Here's my code (some file names and such have been changed for privacy reasons):</p>
  
  <pre><code>Set objFSO = CreateObject("Scripting.FileSystemObject")
  
  ' Create an instance of Excel (keep program hidden) and open text data file
  Set objExcel = CreateObject("Excel.Application")
  
  With objExcel
  .Visible = False
  .WorkBooks.OpenText("Datafile.txt")
  End With
  
  ' Name the current worksheet
  Set objWorksheet = objExcel.Worksheets(1)
  
  
  ' Name constants for use in chart creation
  xlLine = 4
  xlPrimary = 1
  xlCategory = 1
  xlValue = 2
  xlColumns = 3
  
  
  ' Define chart properties
  Set objChart = objExcel.Charts.Add()
  With objExcel.ActiveChart
      .ChartType = 1
      .HasTitle = True
      .ChartTitle.Characters.Text = "usage"
      .Axes(xlCategory, xlPrimary).HasTitle = True
      .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
      .Axes(xlValue, xlPrimary).HasTitle = True
      .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "units"
      .ChartType = xlLine
      .HasLegend = False
      .SetSourceData Source:="objWorksheet".Range("B2:B10"), Plotby:=xlColumns
  
  End With
  </code></pre>
  
  <p>Using this code I manage to create an excel file with the data properly arranged in columns, and a separate sheet with an empty chart in it (having the above properties).</p>
  
  <p>But the ".SetSourceData" line is giving me errors. I'm just not sure how to use it in VBScript. I'm very new to VBScript, so please forgive any errors in syntax or understanding of the code. Maybe I'm doing something fundamentally wrong? </p>
  
  <p><em>edit</em></p>
  
  <p>I'm using Excel 2003.</p>
  
  <p>Cheers.</p>