VBScript for Excel：如何选择源数据（.SetSourceData）？ [英] VBScript for Excel: How do I choose the source data (.SetSourceData)?

问题描述

我在谷歌和这里都搜索过这个问题的答案，没有任何成功。如果之前有人问过，那么我道歉。

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.

我正在尝试使用VBScript自动执行某些管理任务。此特定脚本的目的是从文本文件（带有文本和数字列）中获取一些使用情况统计信息，并从数据中创建折线图。创建一个excel文件并加载数据工作正常，但我在创建图表时遇到问题：我不明白如何选择源数据，并且我一直遇到语法错误。有关如何在VBA中执行此操作的互联网上有大量信息，从录制的宏中可以看出这一点。但我无法用VBScript做到这一点。这是我的代码（由于隐私原因，一些文件名已被更改）：

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

使用这段代码，我设法创建一个excel文件，其中数据正确排列在列中，另一张表中包含空图表（具有上述属性）。

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

但是.SetSourceData行给了我错误。我只是不确定如何在VBScript中使用它。我对VBScript很新，所以请原谅语法错误或理解代码。也许我在做一些根本错误的事情？

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?

编辑

我正在使用Excel 2003。

I'm using Excel 2003.

干杯。

推荐答案

如info部分的 vbscript 标记您无法在VBScript中使用命名参数。此外，您将变量名称 objWorksheet 放在双引号中。这将使它成为文字字符串objWorksheet而不是保存工作表对象的变量。更改行

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

到此：

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

---

更一般地说，您应该使用 Const 关键字来定义常量：

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

否则它们将是常规（可变）变量。

otherwise they'll be regular (mutable) variables.

这篇关于VBScript for Excel：如何选择源数据（.SetSourceData）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！