How to toggle checkbox control in template-driven Excel spreadsheet?

[bob_miller]

I am developing a report generation routine for a DAQ LabVIEW application
running a small public water system. Every month critical operating data
must be organized and reported to the state health department. They provide
an Excel template for these forms, which I am populating from LabVIEW.

After giving up on the MS document express VIs (they are apparently incompatible
with a multi-sheet template unless you are only populating the default current
sheet of the template, a restriction I only figured out after hours of "jiggling"
and rooting in the bowels of the block diagrams), I have managed to populate
almost everything in the form using the basic report generation and Excel-specific
VIs.

However, the one item in the spreadsheet I have not figured out how to write are
the checkbox controls embedded in the template. These checkboxes do not appear to
control anything; they just provide a convenient way in the state form to record an
answer for each of a short list of questions.

Anyone know how to toggle, or better, check/uncheck these from LabVIEW? I can modify
the template if necessary to provide names or other hooks for these objects; I just
haven't a clue what to actually do.

I am developing the VI in LV 7.1 DS-PCE, so I have all of the latest add-on goodies
at my disposal.

Bob

[Scott Y]

When it is not clear where best to start with automating a feature of Excel in LabVIEW, using Excel macros can be very helpful. If you record a macro and toggle the state of your checkbox's you will be able to more closely examine how Excel automates this action.

After the macro is recorded you can edit the macro to see which ActiveX function calls you would have to use from LabVIEW to accomplish the same task. Alternatively, you can record the macro, and automate the playback from inside LabVIEW. The two links below will give you more information on how to implement one of these options.

Example Program: Run Excel Macro from LabVIEW


Using ActiveX to Copy an Excel Range to a LabVIEW Table

Scott Y.
NI

[Michael_Munroe]

You don't specify which version of Excel you are using. With Excel 2003, you can specify a linked cell for each checkbox either on the current sheet or another sheet. Set the linked cell to TRUE for checked and FALSE for unchecked.

[bob_miller]

Thanks Scott and Michael.

I discovered the ability in Excel '03 to link a spare cell to a control while waiting
for a response here. This works fine; I was going to reply to my own post and explain just
that.

However, Scott's advice holds open the possibility of being able to drive the controls
directly without modifying the state's template. Since I return the completed form
as an excel spreadsheet, the state health department can see (if they notice) that I've
"hacked" the template (it is provided password-secured; I had to clipboard-copy the
template into new pages to thwart the password). While this is certainly not illegal
(paper copies are still acceptable), I don't want to appear to be trying to slip
something by them if I can avoid it. With the last online submission I attached a note
explaining the hacks; I'd like not to have to do this every time.

So I will play with Scott's ideas next chance I get and report back what I find.

Bob