Excel Chart Data Update

[sumit.sinha]

Hi All,

We have a requirement in which we have to refresh the chart data. The source data of that chart will increment every week and we have to always select the last 16 rows of the data. We have to rely on the Code stage in Blue Prism to implement it and we tried many things under code stages but still not able to achieve it. Can anyone please help me us to understand that where we are missing.

Thanks.

 

[VJR]

Hi sumit.sinha,

On your sheet can you tell what is-
i. "Gráfico 18"
ii. Chart(13)
iii. Range("AV140:AY152") - If you have to always select the last 16 rows, where is the code that will always select the changing rows?
Also these are only 13 rows.
iv. What is happening right now with the existing code? - Is it correctly selecting AV140:AY152
v. What do you want to do next?

 

[sumit.sinha]

Hi VJR,

Please find below my reply
i. "Gráfico 18" - [It is the name of the chart (graph)]
ii. Chart(13) -[The index of the chart is 13.]
iii. Range("AV140:AY152") - If you have to always select the last 16 rows, where is the code that will always select the changing rows?
Also these are only 13 rows. - [For testing purpose we have given 13 rows as of now which we will change to 16 and make it dynamic as well but the issue is that even after changing the range the chart is not getting updated.]
iv. What is happening right now with the existing code? - Is it correctly selecting AV140:AY152 - [No, it is not selecting that and the data in the graph is not getting refreshed from the code stage]
v. What do you want to do next? - [We need a help here to develop a code which will at least refresh the data as per the range that we have given in the code stage]

 

[VJR]

Hi VJR,

Please find below my reply
i. "Gráfico 18" - [It is the name of the chart (graph)]
ii. Chart(13) -[The index of the chart is 13.]
iii. Range("AV140:AY152") - If you have to always select the last 16 rows, where is the code that will always select the changing rows?
Also these are only 13 rows. - [For testing purpose we have given 13 rows as of now which we will change to 16 and make it dynamic as well but the issue is that even after changing the range the chart is not getting updated.]
iv. What is happening right now with the existing code? - Is it correctly selecting AV140:AY152 - [No, it is not selecting that and the data in the graph is not getting refreshed from the code stage]
v. What do you want to do next? - [We need a help here to develop a code which will at least refresh the data as per the range that we have given in the code stage]

Sheets = Excel.ActiveSheet
'Sheets is a keyword in Excel and may possibly be causing issues.
'Use sheet instead as below.
sheet = excel.ActiveSheet

Remove these lines and check out the below.
Chart = Excel.ActiveChart
Chart = Sheets.ChartObjects("Gráfico 18").Chart.ChartArea.select
Chart(13).SetSourceData (Source := Sheets("Tables").Range("AV140:AY152"))

sheet.ChartObjects("Gráfico 18").Chart.SetSourceData (Source:=sheet.Range("A1:B5"))
Change the above range according to your data.
Post back with your findings.

 

[sumit.sinha]

Hi VJR,

It works if the chart and the data for the chart is in the same worksheet.
But in our case the chart is in the first sheet i.e. Sheet1 and the data for the chart is coming from a different sheet i.e. Sheet3.
Can you please help us in understanding how to implement this scenario.

Thanks.

 

[VJR]

Hi VJR,

It works if the chart and the data for the chart is in the same worksheet.
But in our case the chart is in the first sheet i.e. Sheet1 and the data for the chart is coming from a different sheet i.e. Sheet3.
Can you please help us in understanding how to implement this scenario.

Thanks.

You can set the sheets as per your chart and data.

Dim Datasheet as Object
Dim Chartsheet as Object

sw = GetWorkbook(Handle, Source_Workbook)

Datasheet = sw.Worksheets("Sheet1") 'Sheet1 with data
Chartsheet = sw.Worksheets("Sheet2") 'Sheet2 with chart

Chartsheet.ChartObjects("Chart 1").Chart.SetSourceData (Source:= Datasheet.Range("A1:B4"))

 

[sumit.sinha]

Hi VJR,

Thanks a ton for your inputs , it has worked exactly as per our requirements.

Thanks,
Sumit

 

[ADITYA_RPA]

@VJR please give me the picture of input and out to passed in the you code of chart Update.

thanks in advance

 

[ADITYA_RPA]

Sheets = Excel.ActiveSheet
'Sheets is a keyword in Excel and may possibly be causing issues.
'Use sheet instead as below.
sheet = excel.ActiveSheet

Remove these lines and check out the below.
Chart = Excel.ActiveChart
Chart = Sheets.ChartObjects("Gráfico 18").Chart.ChartArea.select
Chart(13).SetSourceData (Source := Sheets("Tables").Range("AV140:AY152"))

sheet.ChartObjects("Gráfico 18").Chart.SetSourceData (Source:=sheet.Range("A1:B5"))
Change the above range according to your data.
Post back with your findings.

Hi VJR, also need that code
please give the run able code with input and output it will help me more
thank you in advance

 

[ADITYA_RPA]

You can set the sheets as per your chart and data.

Dim Datasheet as Object
Dim Chartsheet as Object

sw = GetWorkbook(Handle, Source_Workbook)

Datasheet = sw.Worksheets("Sheet1") 'Sheet1 with data
Chartsheet = sw.Worksheets("Sheet2") 'Sheet2 with chart

Chartsheet.ChartObjects("Chart 1").Chart.SetSourceData (Source:= Datasheet.Range("A1:B4"))

getting an error
Could not execute code stage: Object reference not set to an instance of an object.