Adding comments to Excel sheet when sheet starts out hidden. (Excel Report toolkit)

[twescott]

Hi Everyone,

I'm using the Report Generation toolkit to write data to an excel workbook.

LabVIEW creates the report based on an excel template that contains hidden sheets for it to place the data in.

The report starts by adding the data to the hidden sheet (with no problem). However, I also add comments to the sheet indicating information about the test that was run to generate the data. No I can successfully unhide the sheet by getting the report's activex references using the toolkit vi for that purpose. however, when i attempt to add the comment i get an error.

This error does not occur if the template sheets are not hidden prior to report generation.

The error also does not occur if an existing excel window is open prior to starting report generation.

Finally, the error does not occur if i break right before the activex command to add the comment, and manually click the title bar of the report excel window before I unpause LabVIEW. I don't click inside the excel window, i merely click the title bar of the window.

My question is how do i get the comment to write without having to do any of those three things. My limited understanding of activex and what im seeing makes me think there must be some way to "activate" the already open excel report window, via activex nodes. In effect, simulating my "click" of the title bar, but i cant find a property that seems to work.

My source code is on a separate network which i cant move anything off of, so I cant show you that, however if necessary and my description doesnt give enough information i can spend some time and create a mock up that approximates the actual code.

[Bob_Schor]

If you can effectively open the hidden sheet (I assume you open it by name or by Sheet Index) and can "add data to the hidden sheet" (I presume by doing an Append Table or Excel Easy Table), I can't understand why you can't write a Comment (say as another Table consisting of a single cell).

This is one of those cases where the code should unambiguously tell us what you were trying to do, and we could then say "Here's how to do it".  Since your code is not easily accessible to you (a strange situation, indeed), maybe you could look at an older example that has Data, Graphs, and what one might call "comments" written to Excel (but not on a Template, and not hidden, but I'm not sure that's really the problem here).  Go to the first page of the Forum and search for "Revised Excel Example" -- choose the obvious "match" (from 2014).

Bob Schor

[twescott]

To clarify,

My goal is to add the comment in the form of an excel comment, rather than in its own cell. (in regular use this is the result of right clicking a cell and selecting "Add Comment".

Adding data to cells  works fine even if they are hidden, however the "Range.AddComment" node only works if the sheet targeted by the range reference is unhidden.

I attempted to just unhide the sheet with the worksheet reference and property node, however that's where I reached the issue which caused me to post here.

I have found a work around by just keeping the all of the template tabs unhidden prior to report generation and then just hiding the ones I want hidden at the end of generation before I save. That said, I am still curious as to if there's a way to select the report window in such a manner that I can unhide the sheet, and add the comment in loop.

[Bob_Schor]

Thank you for the clarification.  Yes, to get at deep Excel properties other than data-in-cells and data-related things such as Graphs, you probably need to use ActiveX or do VBA-type stuff.  The RGT is really meant to get data in and out of Excel (where it can be manipulated by those more comfortable with the Excel model of data processing than with LabVIEW), and NI doesn't directly support all of Excel's Bells and Whistles.  You found one such.

Bob Schor