VBScript for Excel: How do I choose the source dat

问题:

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.

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):

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

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).

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?

edit

I'm using Excel 2003.

Cheers.

回答1:

As documented in the info section for the vbscript tag you cannot use named parameters in VBScript. Also, you put the variable name objWorksheet in double quotes. That would make it a literal string "objWorksheet" instead of a variable holding the worksheet object. Change the line

.SetSourceData Source:="objWorksheet".Range("B2:B10"), Plotby:=xlColumns

to this:

.SetSourceData objWorksheet.Range("B2:B10"), xlColumns

---

On a more general note, you should use the Const keyword for defining constants:

Const xlLine     = 4
Const xlPrimary  = 1
Const xlCategory = 1
Const xlValue    = 2
Const xlColumns  = 3

otherwise they'll be regular (mutable) variables.