Excel : Updating Chart/graph of one workbook with data source from another workbook

[THAKUR_RANVEERS]

Hi , 

Can we update the existing chart/graph of one excel workbook using the data source from another workbook.  Is it possible to do it from blue prism code stage / excel vbo ? Pls guide.
Thanks in advance. 

Best Regards, 
Ranveer

------------------------------
THAKUR RANVEER SINGH
RPA DEVELOPER
Asia/Kolkata
------------------------------

[EmersonF]

Hi, Thakur!
Could you give more details about the scenario?

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
Recife
081988869544
------------------------------

[THAKUR_RANVEERS]

Hello Emerson,

Hope your doing well !

We have two execl templates - Final reports

First report : where we write all the data in different sheets of same excel workbook. 

Second report: It has only charts ( no data/ tables ), here we have to update the charts using the data source of First workbook ( data which we have entered earlier in_@ first excel report).

Best Regards, 
Ranveer











------------------------------
THAKUR RANVEER SINGH
RPA DEVELOPER
Asia/Hyderabad
9849000203
------------------------------

[michaeloneil]

Hi Thakur

The simple answer is yes it is possible to change the datasource for your chart to use a data set from another spreadsheet. This would require a new vbo to be created to do this. I did something similar for charts where I wanted to change the xvalue range and chart series range but the data source will be a little different. Below is the code for doing this, although its untested, this should provide you a starting point for your development and might require just some tweaks to get it working. Hope this helps 🙂

Dim sw, dw As Object
Dim ss, ds 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.Charts(Chart_Name).SetSourceData(Source:=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

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

[THAKUR_RANVEERS]

Thank you so much Michael !  Will check and try to make it work.

------------------------------
THAKUR RANVEER SINGH
RPA DEVELOPER
Asia/Hyderabad
9849000203
------------------------------

[michaeloneil]

No problem let me know how it goes 🙂

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

[EmersonF]

Perfect solution Michel, I had the idea to do the same, but using a macro, I believe it would be interesting also to create a new action just to "Force" the update of the connection between the two files.
This can be done through the code below.

ActiveWorkbook.RefreshAll

This will cause all template connections to be updated. This ends up helping to refresh the data and even find out if there are any errors in the external data source that prevents the data from being uploaded

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
Recife
081988869544
------------------------------

[THAKUR_RANVEERS]

Hi Michael ,Emerson,

I've made some changes to the above code, It is giving some error "Invalid Index".
Please find the screenshot attached .

Here is the code; as there are two workbooks I have used two handles, Please guide if any others changes are required.

------------------------------------------------------------------------------------------------------

Dim ss1,ss2,sw1,sw2 As Object


Try

sw1 = GetWorkbook(CInt(Handle1), Source_Workbook1)
ss1 = sw1.Worksheets(Source_Worksheet1)
sw2 = GetWorkbook(CInt(Handle2), Source_Workbook2)
ss2 = sw2.Worksheets(Source_Worksheet2)


''ss2.ChartObjects(Chart_Name).Chart.SetSourceData(Source:=ss1.Range(Source_Range))
ss2.ChartObjects(Chart_Name).Activate()

sw2.Charts(Chart_Name).SetSourceData(Source:=sw1.Sheets(ss1).Range(Source_Range))

sw2.RefreshAll

Success = True
Message = Chart_Name & " successfully updated"

Catch e As Exception
Success = False
Message = e.Message
Finally
sw1 = Nothing
ss1 = Nothing
sw2 = Nothing
ss2 = Nothing



End Try

-------------------------------------------------------------------------------------------------------------------------------

------------------------------
THAKUR RANVEER SINGH
RPA DEVELOPER
Asia/Hyderabad
------------------------------

[michaeloneil]

Hi Thakur

This is telling you the data range you're providing is incorrect. This is the part that can be a little tricky you will need to set the source data to look however excel needs it to be. In this case it might look something like this ='C:\Users\myfolders\[MyExcelFile.xlsx]WorksheetName'!$E$3/'C:\Users\myfolders\[MyExcelFile.xlsx]WorksheetName'!$F$3

E3 and F3 will be the data startrange and data end range. You would try in excel to record a macro first when you change the data source and this will let you see how the sourcedata reference looks when you change it to reference a different workbook. 

@EmersonF The refresh is a good idea but i think in this case Thakur wants to change where the data is being referenced in the graph completely and get the info from a different workbook so refreshing the data would change this.​

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

[EmersonF]

@Thakur  I believe that the shortest way to work would be to follow @Michael ONeil advice, do the work manually by recording a macro, then see what steps are needed and take as reference the paths he performs. ​​​​

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
Recife
------------------------------