VbScript Union two ranges in excel

[edTech]

I have a VbScript that I am creating a graph in Excel. My issue is that I am trying to set the range as two separate columns, but I assuming I need to do a Union, but it is not working. I get an error in the Union line and the SetSource line. Here is my code:

Set rng1 = objReadWS.Range("B4", objReadWS.Range("B4").End(xlDown))
Set rng2 = objReadWS.Range("D4", objReadWS.Range("D4").End(xlDown))

output = objReadXL.Union(rng1, rng2)

Set objchart = objReadXL.Charts.Add()
objReadXL.ActiveChart.SetSourceData output , 2

Any Ideas?

thank you

 

[Joe4]

I am not all that familiar with the method you are using, however, since "ouput" appears to be a range object, wouldn't you need to use the "Set" command on that also?

 

[RoryA]

Also better to use objchart rather than objReadXL.ActiveChart. If this is VBS rather than VBA, I assume you have declared your own xldown constant?

 

[edTech]

Also better to use objchart rather than objReadXL.ActiveChart. If this is VBS rather than VBA, I assume you have declared your own xldown constant?

Good afternoon RoryA,
I have the code working, but it takes too long to process. I am trying to find a better way of doing it. Here is my current code for creating the graph. Yes, I have defined the constant as you can see in the code. Any advice on how to change the code better would be awesome.

'---define objects----
Set objReadXL = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")

'----define constance----
Const xlup = -4162
Const xlDown = -4121
Const xlToRight = -4161
Const xlToLeft = -4159
Const xlNone = -4142
Const xlCellTypeBlanks = 4

Sub ProcessingFullRunFile()
  'Make excel spreadsheet visible
  objReadXL.Visible = true
 
  'Set variable definitions
  Set objReadWB = objReadXL.Workbooks.Open(file2)
  Set objReadWS = objReadWB.Sheets("Sheet1")
  'Set objchart = objReadXL.Charts.Add()
 
  With objReadWS
 
   'Find the last non-blank cell in a single row or column
   lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
  
   'Find the last non-blank cell in row 1
   lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
  
   'select the range and loop each cell thats blank
   'With .Range(.Cells(2, 2), .Cells(lastrow, lastCol))
   With .Range("B2:C" & lastrow)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
    .Value = .Value
   End With
  End With
 
  Set Source = objReadWS.Range("B4:D" & lastrow)
 
  Set rng1 = objReadWS.Range("B4", objReadWS.Range("B4").End(xlDown))
  Set rng2 = objReadWS.Range("C4", objReadWS.Range("C4").End(xlDown))
  Set rng3 = objReadWS.Range("D4", objReadWS.Range("D4").End(xlDown))
  Set rng4 = objReadWS.Range("J4", objReadWS.Range("J4").End(xlDown))
 
  objReadXL.ActiveChart.HasTitle = True
  objReadXL.ActiveChart.ChartTitle.Characters.Text = "Race"
  objReadXL.ActiveChart.SetSourceData Source, 2
  objReadXL.ActiveChart.Location 2, "Sheet1"
  objReadXL.ActiveChart.ChartType = 63
  objReadXL.ActiveChart.SeriesCollection(1).xValues = rng1
  objReadXL.ActiveChart.SeriesCollection(1).Values = rng2
  objReadXL.ActiveChart.SeriesCollection(2).xValues = rng1
  objReadXL.ActiveChart.SeriesCollection(2).Values = rng3
  objReadXL.ActiveChart.SeriesCollection(3).xValues = rng1
  objReadXL.ActiveChart.SeriesCollection(3).Values = rng4
  objReadXL.ActiveChart.Parent.Width = 1000
  objReadXL.ActiveChart.Parent.Height = 500
  objReadXL.ActiveChart.Parent.Left = 100
  objReadXL.ActiveChart.Parent.Top = 100
  
    Set objReadXL = Nothing
    Set objReadWB = Nothing


End Sub

 

[RoryA]

If I were you, I would only make the Excel instance visible at the end, after doing all the processing.