Search code examples
excelvbscriptchartsscatter

Create a scatter in excel using vbscript

I am trying to use VBScript to create a scatter with the following data:

x axis: A1 to F1, y axis: A2 to F2

The code I 've found so far is: 

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

The problem I am facing is that I cannot find a way to define the y axis which is Range(A2:F2).

Could you assist? Thanks in advance.

Solution

  • Append the following line:

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

    Edit: To add another series try this:

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