How do I link Excel Chart titles and legend to particular Excel cells using ActiveX and/or Report Generation Toolkit?

[murchak]

The code below generates an Excel worksheet with a data table in it, and an Excel chart plotting the data. Once the Excel file is generated, the user can alter the numbers in the table and correspondingly the Excel chart gets updated, which is what I expect.

Similarly, how can I make it so that the legend, instead of saying Series1, shows whatever text appears in cell B1 (in this case Y). In fact, the user should be able to put anything in B1, and the legend should get updated accordingly. 

Can this be generalized to x-axis, y-axis, and the chart title as well? Ultimately what I would like is for all the titles and the legend to be dynamic in the sense that after the chart is generated, the user should be able to alter cell contents in order to update the chart titles and the legend.

I did play with the _Chart Wizard with no luck. I know in my code below I haven't included axes titles, but if I can link the legend to a particular cell content, then I can apply the same concept to take care of the axes titles too. I should mention that I have read quite a few helpful posts on these fora about Excel charts and formatting, but I haven't found any that addresses my issue. I just want the user to alter the content of a cell, and I want the legend to reflect that change automatically just like it happens with the data. I tried this manually with Excel directly and it works. I recorded the macro and looked at it, and it shows FullSeriesCollection.Name = "Sheet1:B1". I tried to do the same with my code, but it doesn't work. Once the chart is generated, its legend is completely dissociated from any cell whatsoever.

[ben64 · Accepted solution]

---

@murchak wrote:

Similarly, how can I make it so that the legend, instead of saying Series1, shows whatever text appears in cell B1 (in this case Y). In fact, the user should be able to put anything in B1, and the legend should get updated accordingly. 

Can this be generalized to x-axis, y-axis, and the chart title as well? Ultimately what I would like is for all the titles and the legend to be dynamic in the sense that after the chart is generated, the user should be able to alter cell contents in order to update the chart titles and the legend. 

---

Yes this can be done. It is not very well known that Excel charts title can be modified dynamically. The trick is to use formulas instead of plain text to set the chart title, axis name, .. Make sure that the Application Calculation mode is set to automatic and that the formula contain the sheet reference (Sheet1 in my example, note that the current sheet name can also be retrieve dynamically)

Here is an example using your code. For the sake of the example I'm setting the series name the same as the Chart Title. As a side note you should close the reference in the reverse order that they were opened (and I'm guilty of not doing it in my example!).

Ben64

[murchak]

Thank you Ben! Out of curiosity I just wired "=Sheet1!$B$1" into the name and it worked just fine without using calculation and formulas. But I am glad you pointed out that to me. I'll leave this thread open for a couple of more days just to make sure my final application runs just fine, then I'll accept your reply as solution. Thank you so much. 

BTW, yes I do close all the references and the code I posted was stripped of all those and other bulky stuff just so I can post a simpler PNG. -:)

[ben64]

---

@murchak wrote:

Thank you Ben! Out of curiosity I just wired "=Sheet1!$B$1" into the name and it worked just fine without using calculation and formulas.

---

It is probably set to Automatic in your Excel Formulas Options. But if you distribute your code to an installation where it is not set to Automatic the Application Calculation property node will take care of it.

Ben64

[murchak]

Hi Ben,

Last time you mentioned that the axes titles can be set to Excel cell values - for example 'Sheet1'!$B$1.

What if I wanted the x-axis title to be a combination of two or more Excel cells? For example, I would like the x-axis for my Excel chart to be equal to A1&'-'&B1.

I tried the Concatenate Strings function to wire 'Sheet1'!$A$1'&'Sheet1'!$B$2 into the x-axis title, but the chart shows nothing for the x-axis title. If I just wire 'Sheet1'!$A$1 into it, then I see the title, and if I change the value of A1 cell, the chart updates the title accordingly. 

[ben64]

For a Series Name Excel expects to see a reference to a single cell or range of cells and not a normal formula. You will have to read the content of cell Sheet1!$A$1 and Sheet1!$B$1, concatenate in LabVIEW and write the result to the Series Name property.

Ben64

[murchak]

Thanks Ben.

I found a workaround using your idea. I basically used Excel Insert Formula.vi from the Report Generation Toolkit to insert a concatenation formula in the desired cell and then used that cell as a reference to define my x-axis.