excel - 使用 vbscript 在 excel 中创建散点图

Question

我正在尝试使用 VBScript 创建包含以下数据的散点图:

x 轴:A1 到 F1，y 轴:A2 到 F2

到目前为止我发现的代码是:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()

Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,1) = 80
objWorksheet.Cells(1,2) = 82
objWorksheet.Cells(1,3) = 85
objWorksheet.Cells(1,4) = 91
objWorksheet.Cells(1,5) = 178
objWorksheet.Cells(1,6) = 91

objWorksheet.Cells(2,1) = 5
objWorksheet.Cells(2,2) = 7
objWorksheet.Cells(2,3) = 8
objWorksheet.Cells(2,4) = 9
objWorksheet.Cells(2,5) = 10
objWorksheet.Cells(2,6) = 11

Set objRange = objExcel.Range("A1:F1")
objRange.Select

Set colCharts = objExcel.Charts
colCharts.Add()

Set objChart = colCharts(1)
objChart.Activate

objChart.ChartType = 72

我面临的问题是我找不到定义 y 轴的方法，即 Range(A2:F2)。

你能帮忙吗？提前致谢。

Answer 1

附加以下行:

objChart.SetSourceData objExcel.Sheets(2).Range("A1:F2")

编辑:要添加另一个系列，请尝试以下操作:

Function GetAddress(sheet, range)
  GetAddress = "=" & sheet.Name & "!" & sheet.Range("A5:F5").Address(,,-4150)
End Function

Set series = objChart.SeriesCollection.NewSeries
series.XValues = GetAddress(objWorksheet, "A4:F4")
series.Values  = GetAddress(objWorksheet, "A5:F5")