topic Amending excel graph data ranges in Product Forum

Amending excel graph data ranges

michaeloneil — Thu, 10 Sep 2020 15:24:00 GMT

Hi

I am trying to create a vbo to change the data range for the XValues of a chart object in excel. I have it doing everything I need except it doesn't recognise the range value when its passed into the object as a parameter. Here's what I have so far

Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet, source, workbook, range As Object

Try

sw = GetWorkbook(CInt(Handle), Source_Workbook)
ss = GetWorksheet(CInt(Handle), Source_Workbook, Source_Worksheet)
sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
workbook = excel.ActiveWorkbook

sheet.ChartObjects(Chart_Name).Activate()
workbook.ActiveChart.FullSeriesCollection(1).XValues = workbook.Sheets(Source_Worksheet).Range(Source_Range)

Success = True
Message = Chart_Name & " successfully updated"

Catch e As Exception
Success = False
Message = e.Message
Finally
sw = Nothing
ss = Nothing
dw = Nothing
ds = Nothing
excel = Nothing
sheet = Nothing
source = Nothing

End Try

If I defined the value specifically it works fine e.g. workbook.ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$A$2:$B$2"

But obviously I would want to pass the range value as a parameter to make it dynamic. I know I'm missing something obvious here but I just cant put my finger on it.

Any suggestions?

------------------------------
Michael ONeil
Technical lead developer
Europe/London
------------------------------

RE: Amending excel graph data ranges

shashank.kumar280 — Fri, 11 Sep 2020 07:06:00 GMT

Hi Michael,

Can you share the error message that you are getting?

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

RE: Amending excel graph data ranges

michaeloneil — Fri, 11 Sep 2020 15:12:00 GMT

Hi

The error I get is:
Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))

I think this is related to the horizontal reference of the range so something in workbook.Sheets(Source_Worksheet).Range(Source_Range) but I'm not sure what part of this its not accepting.

------------------------------
Michael ONeil
Senior Developer
Clydesdale and Yorkshire bank
Europe/London
------------------------------

RE: Amending excel graph data ranges

michaeloneil — Thu, 17 Sep 2020 13:20:00 GMT

For anyone who was reading this and looking to do the same thing with charts I finally got it resolved. The below code works and you can also use the same code to update the chart series by adding the following, please note the series position is dynamic number value since I want to loop through every series to update them.

workbook.ActiveChart.FullSeriesCollection(Series_Position).Name = ("=" & Source_Worksheet & "!" & Source_Range)
workbook.ActiveChart.FullSeriesCollection(Series_Position).Values = ("=" & Source_Worksheet & "!" & Source_Range)

Full code
---------------------------------------------------------------
Dim sw, dw As Object
Dim ss, ds As Object
'Dim activeCell AS Object
Dim excel, sheet, source, workbook As Object

Try

sw = GetWorkbook(CInt(Handle), Source_Workbook)
ss = GetWorksheet(CInt(Handle), Source_Workbook, Source_Worksheet)
sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
workbook = excel.ActiveWorkbook
sheet.ChartObjects(Chart_Name).Activate()
workbook.ActiveChart.FullSeriesCollection(1).XValues = ("=" & Source_Worksheet & "!" & Source_Range)

Success = True
Message = Chart_Name & " successfully updated"

Catch e As Exception
Success = False
Message = e.Message
Finally
sw = Nothing
ss = Nothing
dw = Nothing
ds = Nothing
excel = Nothing
sheet = Nothing
source = Nothing

End Try

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------