Built in Excel Broken Vi's

[DavidBoyd]

There's a well-worn path of developers on these forums who get stymied by the moving target that is MS Office/Excel's ActiveX interface.  I've seen similar problems where the Office 365 release and the standard Excel 2016 release will have slightly differing ActiveX invoke node parameter lists, breaking the VI, which can be fixed by a right-click->relink->save operation.  That works, but only if you don't need to move the code back and forth between Excel environments.

(Note that all these fixes require you to re-save a VI that lives under LabVIEW's <vi.lib> path, which is always replaced when the next release of LabVIEW is installed, so keep that in mind!)

A further refinement to the right-click->relink node->save method, when you need it to load unbroken in the alternate environment, is to surround the offending node in a conditional compilation structure as you're fixing and saving it.  When you get to the other environment, duplicate the broken case, fix that, and save once more.  Now, at least the VI should load in either environment and find the first unbroken case, without being broken.

It would be great if NI/Emerson would do this to support the community on subsequent releases, but since Microsoft Office ActiveX is always getting tweaked, it may be an endless effort.  (Actually, ActiveX in general is getting too ancient, so the RGT is aging along with it.)

Good luck!

Dave

[spforster]

Morning David,

I've had a look at the Vi's this morning regarding what you have said, but there is no option to relink or event link when I right click on the property node.  Also I've now got the same version of office on both computers.  Is there any way in telling if ActiveX is playing up at all and therefore causing this issue?

Thanks

Simon

[DavidBoyd]

Simon,

Does that specific VI have a broken wire, in which the source is a variant (purple), but the destination would be expecting an ActiveX refnum (green wire)?

If that's so, the relink method isn't going to work (that sort of fix is for the different problem of methods varying in their number of parameters).

I'm less certain, but the variant-into-refnum may be solveable with a Variant To Data cast node inserted.  It would require a constant of the refnum type as well, to type the V2D node's output.

Dave

(edit: adding a screen capture, sorry it's not a snippet, of what I'm suggesting as a fix)

[Bob_Schor]

I've done a fair amount of "Excel with LabVIEW", but I don't recognize the code you are using in your examples.  I've been happily using the Report Generation Toolkit to do useful things with reading and writing Excel Workbooks, but I'm unfamiliar with the functions that you are showing.

How are you interacting with Excel?  Are you using the RGT?  Are you using it to "burrow" even deeper (where I fear to tread) into Microsoft arcana?  

Bob Schor

[DavidBoyd]

Bob,

I think the VI originally screen grabbed is one of the very old ones a layer or two deeper than the user-facing parts of the RGT.  It's always appeared to me that NI rewrote the class-based RGT overtop the older release, and much of the really-does-stuff VIs are buried.  Most can be identified by their salmon-colored VI icons.  Unfortunately, since much real ActiveX interfacing is captured down there, those are the VIs which break when Microsoft changes the ActiveX interface.

The image I posted prior, may fix the present problem, but no guarantees; and bonus points if (in the alternate environment), it just silently casts an ActiveX Font refnum  into another Font refnum.  If not, the conditional-compilation selector frame method could still make the VI work across environments.

Dave

[spforster]

Hi David,

It's all very well changing the Vi to work how it is suppose to.  But surely it should work straight out of the box as its part of a downloadable set of Vi that have been added to the computer to interact with Excel, and it's not the only Vi that is having issue.  Several of the other NI_ReportGenerationToolkit Vi are not working also.

Thanks for the continued support on this matter.

Simon

[Yamaeda]

I've had to relink and even rewire some functions a couple of times. Office sometimes changes their function calls and RGT is pretty old. It might be a good idea to save them as a local copy in your project instead of saving over the ones in vi.lib.

[rolfk]

---

@DavidBoyd wrote:

A further refinement to the right-click->relink node->save method, when you need it to load unbroken in the alternate environment, is to surround the offending node in a conditional compilation structure as you're fixing and saving it.  When you get to the other environment, duplicate the broken case, fix that, and save once more.  Now, at least the VI should load in either environment and find the first unbroken case, without being broken.

It would be great if NI/Emerson would do this to support the community on subsequent releases, but since Microsoft Office ActiveX is always getting tweaked, it may be an endless effort.  (Actually, ActiveX in general is getting too ancient, so the RGT is aging along with it.)

---

The problem is that LabVIEW compiles the ActiveX IDispatch MethodID into the VI and doesn't retain the actual MethodName in the compiled code. Every time the IDispatch interface of an ActiveX method changes, the ActiveX standard requires the Dispatch IDs to be changed. Using the MethodID rather than the name when calling a class method is more performant but ties the execution to a specific API version.

Microsoft occasionally changes API methods in the ActiveX component, specifically the Save method is frequently changed to add additional optional parameters in newer versions.

Best would be if LabVIEW also retained the MethodName in the compiled resource and when calling the Method through the MethodID fails, to relookup the MethodID from the MethodName and retry the call with the new MethodID. The IDispatch interface used here allows exactly that. The additional optional parameters are not a problem since the IDispatch interface specifically allows for optional parameters to not be present in the Call. It would require a compiled resource format change as the compiled code for each ActiveX method and property would also need to store the MethodName/PropertyName in addition to the ID. This would have been a very simple change back in the days, as the LabVIEW binary file format was changing with each new version anyhow and there was no backwards or forward compatibility that had to be observed. Today it would require more significant work since we have all those version compatibility requirements to allow compiled VIs to be called in more recent environments but still.

Unfortunately as you say, ActiveX is a legacy technology. The latest Visual Studio installations don't even support creating proper ActiveX components without doing some rather low level trickery. So this limitation, that could have been fixed with a few engineering hours 20 years ago, will stay there for eternity.

[spforster]

Hi Rolf,

Many thanks for that explanation. Is there any why I can check to see if it is Active X that is causing the problem?  I still don't understand why the Vi works on one of our new computers but not the other one.

Thanks again,

Simon

[DavidBoyd]

Thanks, Rolf.

I'm grateful that you have that detailed level of understanding of how LabVIEW calls ActiveX; I wasn't aware of the details of MethodName => MethodID => IDispatch.  I'm fairly certain that a few years ago,I was able to "fix" broken linkage (where a VI loaded broken because of an added parameter), by the conditional compilation trick, but perhaps I was fooling myself.

I suppose in an ideal world there would be a complete RGT rewrite by now, assuming that everything needed to interact with Word and Excel was available via .NET, but I suppose that's a fever dream.  A little more delirium would include having compatibility to "drop in" the new release directly in place of the old.

But then, I'd have much more use of a .NET-based refresh of the Database Connectivity Toolkit, so that might be my first fever dream 🙂

Dave