How do I customize Excel plot area and axis settings using Report Generation Toolkit?

[Bob_Schor]

Did you even try the demo I converted to LabVIEW 2011 for you?  True, it doesn't show the additional Excel Set Graph Colors function in it, but I expect that you know how to add that in and to add in test colors to demonstrate that this code works. These functions do use ActiveX, and hence have the "limitations" that have been mentioned, but it is so easy to code, and it works.  It is also easy to document, to explain, and to remember, six months later, what you were trying to do.

Bob Schor

[ben64]

---

@murchak wrote:

Thanks Ben.

What is the procedure for creating a Graph.Axes object from scratch so I that I can connect it to the Variant to Data type input?

Sorry my OO programming skills are pretty weak.

---

From scratch: From the front panel, right-click and go to Controls->Refnum and drop an Automation Refnum control on the front panel. Right-click the Automation Refnum: Select ActiveX class -> Browse ... select type library: Microsoft Graph 14.0 Object Library version 1.2 (versions may be different for you) and select Axes from the Objects list.

Finally have a look at my signature at the bottom of this post 🙂

Ben64

[aputman]

• Drop an Automation Open VI onto the diagram (Ctrl + Space to use quick drop).
• Create a control for the refnum
• Right click the control -> Select Active X Class -> Browse
• Set the type to Microsoft Excel XX.X Object Library (XX is a version number, 2010 Excel is 14.0)
• Select ChartObjects

[murchak]

Hi Bob.

Absolutely! It would not help me if I didn't read the responses by the gracious community members. I found it instructive!

I opened up your demo and discovered a few things.

1) The colors are still limited because RGT uses MsGraph as another member explained to me.

2) With XYScatter, I cannot attach the legend text to the row headers. For example if I plot two curves and my legend is A, B, then the graph shows A, XYgraph2 for the legend. Then I read the warning from LabVIEW that says in case of using xlXYScatter as curve type, you cannot connect legend to the row header. If I use xlLine, then I can attach the legend to the row header, but it treats my x-axis as category, not numerical. In other words, if my x-axis is 1, 2, 5, 8, 9, then these values would be equally spaced.

3) If I don't use XYScatter, then the y-axis label does not appear next the y-axis.

I am still exploring the problem. One member suggested to use Excel.Chart object which I don't see how to create. I am still working on the problem. 

[aputman]

Here is the code from that post (back-saved to 2011).  

Please avoid resurrecting old posts if possible.  Keep your questions in the topic at hand so that the forum isn't cluttered up with old posts.  

[murchak]

Hi aputman. 

I have been playing with your Chart.vi. It is really instructive. So thank you. I have also been browsing through msdn.microsoft.com and now I have a better understanding of the ChartObject model. One question that is still lingering is how to choose my own custom legend. In your example, (see the slightly modified version of it attached), the plot that is generated uses the titles of y-axis columns as entries in the plot legend.

For me, the way I look at my data, is that the column titles are really the y-axis titles and the legend could be something entirely different.

For example, suppose I plot weight vs. height for a sample of males and females on the same plot.  My data would have 3 columns: column 1 is height (x-axis), columns 2 and 3 would be weight (for men and women). So now I can plot weight vs. height on the same plot and color the two curves according to sex. So my legend would say "male" and "female" and not weight (which is the title of my y-axis data). 

So, I looked at ActiveX classes Legend, LegendKey, LegendEntry and LegendEnties, and I cannot find any legend property that allows me to "WRITE" my own custom legend. Is this possible to do this, or should I reformat my data and accept the default behavior where legend is the column title?

[aputman]

The method I use for exploring the rabbit hole that is ActiveX is recorded macros.  On the Developer tab (if not visible, it can be shown in Options->Customize Ribbon), start recording a Macro.  And then create a chart with the customize legend as desired.  Stop the macro and open up the code in Visual Basic editor.  It will show you what you need to code in Labview to have the same appearance.  

Try that and see what you can come up with.  

[murchak]

I did exactly what you recommended and it is a great way to walk through the maze. However, I am getting a type mismatch error when I connect the plot selection area to Variant to Data vi with ChartFormat as type input.

See the attached image and the VI.

[aputman · Accepted solution]

Remove the Select method.

[murchak]

That took care of it. But I don't understand why. Thank you!