How to update an Excel Named Range or

Help
,
1

Hi, RPA Devs!

I have a Excel workbook into which I’m appending a new data row at a specific time once per day. I use an embedded Excel Line Chart to visually display this time series. Here’s my problem … How can up update the Chart Data Range attribute of the Excel Chart each time my Bot runs and appends a new row to the data series?

I don’t see an explicit UiPath Activity for updating Excel Charts in either the uiPath.Excel.Activities or the UiPathTeam.Excel.Extensions.Activities packages. The only solution I can see at this juncture, is to create an Excel Macro and pass in the new row as a parameter, or via the Excel Macro, reselect the range of data that is used to define what is displayed in the chart.

I.e. if the old Chart Data Range was: =‘TimeSeriesValues’!$A$1:$E$5, run a macro which reselects a contiguous data range and increments the last row in the range to:
=‘TimeSeriesValues’!$A$1:$E$6. i.e. change the last row from 5 to 6 to account for the newly appended row.

Another approach, have the Chart Data Range simply reference an Excel Named Range … but again, there is no explicit Activity in UiPath Studio Community Edition 2019.10.1 to define or update an existing Named Range.

Thanks in advance for your ideas and alternate solutions :slight_smile:

2 Likes
2

I came up with a literal hack to do this:

  1. Open file explorer and enable “File name extensions” from the “View” menu.
  2. Navigate to your xlsx file and rename it from .xlsx to .zip (e.g. if it was MyExcelBook.xlsx change it to MyExcelBook.zip)
  3. extract the .zip file, which will produce a folder with the same name but wihtout the zip (you will re-zip the file later).
  4. Open the extracted zip file and navigate to xl/charts/chart1.xml (if there is more than 1 chart then you will need to look at the others to find the right chart. Open the xml file using notepad.
  5. Inside, look for the text “<c:f>”. The text following that will be your data reference.
  6. Change it accordingly and then save the xml file, overwriting the original.
  7. go back to the root folder that was created when you extracted the .zip earlier, right click on it, and click on “send to” > “Compressed (zipped) folder”.
  8. change the .zip of the file to .xlsx.
  9. open the file and I’m your uncle, the chart’s data source is changed.

Not the best way, but the h4X0r way :laughing:

3

Also, if Excel can’t open the file then use 7zip to re-zip the folder. Just change the type to “zip” and the compression method to “store”.

4

Tom M … thank you kindly for applying your problem solving skills to my issue. :slight_smile:

I ended up solving the problem myself by writing a little Excel VBA code which uses the Excel Selection object’s End method. This solution approach allows you to divorce yourself from having to know the specific column and row dimensions of the Data Range that you want to use with your Excel Chart object.

So once you append a new data row to your Excel Worksheet, you need to use the UiPath Execute Macro Activity to execute the following Excel VBA Macro. Lastly, be sure you Save your workbook out as a Macro-enabled Excel Workbook … i.e. file type: “*.XLSM”

Sub UpdateChartDataRange()
’
’ UpdateChartDataRange Macro
’ 2020-01-28, TRKolankiewicz, This macro is executed by a UiPath Robot. It’s
’ purpose is to update the Chart Data Range attribute of the Excel Chart object
’ located in the same Excel Workbook, by redefining the range so that it
’ includes the newly appended data row by UiPath.

’ Assumptions/Constraints:
’ 1. The Range to be included in the chart begins in cell “A1”.
’ 2. The Range is contiguous

'Range object holds reference to the updated data range to be used in the chart.
'This range must be contiguous cells, with all of them containing data since
'we're using the Selection.End() method to select all cells to the right and then down.
Dim rngChartRange As Range

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Set rngChartRange = Range(Selection, Selection.End(xlDown))

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
Application.CutCopyMode = False
ActiveChart.SetSourceData Source:=rngChartRange

'Lastly, just move the active cell back home.
Range("A1").Select

End Sub

1 Like
5

Great that sounds very robust! haha

6

Thanks again for your time, Tom D!
-Tom K. :slight_smile:

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.