ActiveX to Automate Excel - Bug/Anomoly Report

[peter_flores]

Hello world!

I am having a very strange issue controlling MS Excel through ActiveX from LabVIEW. I feel like it is likely not a rare task yet I find little to no helpful documentation from the MS side or NI side :/ I am a CLD and am working this project with a CLA and it has cost us over 40 hours of painstaking troubleshooting so far! I hope you can help. Here it goes...

I am using the following setup:

Windows 7 Pro

LabVIEW 2015 SP1

Office 2016

 

I have created a simple LV class to create excel reports which works without an issue on my computer. The problem occurs when I run the code on another PC - Excel crashes ("2016-12-06_1125") and needs to restart, and usually then feeds an error back to LabVIEW("2016-12-06_1152"). This crash is asynchronous to the LabVIEW calls, meaning that a different VI will report the error each time. The problem occurs in Dev Environment and as an EXE.

 

I have been developing and validating on these two separate systems during the last 3 months of building this project without an issue. The only difference between systems has arisen with the Excel automation. I have now run the project on 5 or 6 machines where 2 exhibit the problem repeatedly. On the rest, I have seen the behavior, but remedy it by updating the ActiveX reference as described below.

 

 

Other systems:

Works: Win 7, LabVIEW 2015 SP1, Office 2013, .NET 4.6.1;           Win 10, Office 2016, LabVIEW 2015 SP1, .NET 4.6.2

Does not work: Win 10 (.NET 4.6.2), LabVIEW 2015 SP1, Office 2016;     Win 10, LabVIEW 2015 SP1, .NET 4.6.1, Office 2016

 

"Version 1.3"

 

A strange behavior that must be related, but I have not been able to exactly correlate it with good or bad behavior is shown in the other two screenshots attached. "Version 1.9" shows the .NET library that should be selected. Occasionally, this appears as shown in "Version 1.3", which looks like garbage - it is hard to tell what library that eve is supposed to be and why it would switch on it's own. We have tried setting this as a constant, control, typedef, with no luck in locking it down and keeping the "Version 1.3" behavior at bay.

 

Related Post?

In doing a lot of googling and research, I have not seen much info on this. Though this post looks like it is describing a similar behavior, though not exactly the same. 

 

Thank you in advance for your help! This is holding up delivery of a $50k LabVIEW job that is scheduled to be delivered by Dec 15 and is looking unlikely because of this bug/anomaly, please help! (you know, the last 10% rule, ahhhh!)

 

[hooovahh]

Well the first thing I noticed is you are trying to control Office 2016 with LabVIEW 2015 which the report generation toolkit doesn't support, so it likely hasn't been tested properly by NI.  Sorry I've never seen this or have any suggestions other than to try it out with LabVIEW 2016, and the updated report generation toolkit.  Of course I realize it might not be that easy since there maybe other dependencies, and setting up a test for this setup might be a huge problem.  But I'm guessing you won't get a whole lot of support from NI on this issue due to the documented incompatibility.

[Neil Pate]

I do not have an answer to your problem but can only share something that happened recently which is probably not related to your issue but I will say anyway. I have an Office 365 subscription and one of the more recent updates broke the report generation toolkit as the ActiveX interface to Excel changed. I was able to manually relink the ActiveX component in the code and everything worked fine on my PC after that, but the executable would then not run on my target machine which had an older version of Excel on it.

[peter_flores]

@hooovahh Thank you! I have seen that and noted it. We are actively working on testing in LabVIEW 2016. To note, is that we are not using RGT (is it normal to think this TK is not done very well and is to be avoided?). Specifically, I need to copy/paste sheets in excel and did not see this as a supported function in RGT. It seems odd to me this TK is not very full featured and seems to be antiquated - do many people use it? Or is it common to go it alone and do your own ActiveX implementation... And this behavior did occur using LabVIEW 2015 to control Office 2013. I am fairly confident it is not a new issue to Office 2016, but I should know for sure today or tomorrow when I run with LabVIEW 2016... Thanks!

[peter_flores]

@Neil Pate thanks! So, you are describing a one-time break and fix? When you say manually relink the ActiveX component, is that similar to my screenshots Version 1.3 and Version 1.9 above? Also, interested in your thoughts on the RGT as noted above... Thanks!

[hooovahh]

Well the report generation toolkit could be much better, and has several functions from older revisions of office that still work but are inefficient, and limiting.  That being said I usually end up doing a combination of using the report generation functions, and then use the Get ActiveX Reference, which allows you to do as you please.  I think you can even write companion classes that could extend the functionality of the RGT.  So I wouldn't say the tookit should be avoided, just know its limitations, and don't expect that you only need the functions provided.  Sorry for my assumption that you were using it.

[peter_flores]

Understood... makes sense, thank you! @hooovahh

[Neil Pate]

1 hour ago, peter_flores said:

@Neil Pate thanks! So, you are describing a one-time break and fix? When you say manually relink the ActiveX component, is that similar to my screenshots Version 1.3 and Version 1.9 above? Also, interested in your thoughts on the RGT as noted above... Thanks!

I did not need to do as you documented, it was sufficient for me to just re-select the broken method call inside the offending RGT VI . If memory serves me correctly it was the "SaveAs" method of the _Workbook object. All I had to do was reselect the method and it then was no longer broken. I think I noticed that the new method had an additional input.

But as I mentioned, although this worked fine on my PC it was broken on the target PC which had a much older version of Excel, so in a panic I uninstalled Office 365 and installed Office 2010 and everything then worked fine.

[peter_flores]

Quote

49 minutes ago, Neil Pate said:

I did not need to do as you documented, it was sufficient for me to just re-select the broken method call inside the offending RGT VI . If memory serves me correctly it was the "SaveAs" method of the _Workbook object. All I had to do was reselect the method and it then was no longer broken. I think I noticed that the new method had an additional input.

 

Ahh yes,I did notice this behavior and seems to be explained by this thread: 

The more and more I dig and test, the more I am thinking this is a corrupt office install on a machine, maybe affecting the registry, and unrelated to these other issues. The behavior was mostly isolated to one machine, though I have seen it on others - but this could be after the faulty machine flipped or corrupted something in the ActiveX? Don't know, I am kind of reaching here...

 

 

[peter_flores]

Update, crash is exhibited in LabVIEW 2016 with Office 2016 and LabVIEW 2015SP1 with Office 2013.

 

Thanks!

[Gribo]

Did you check this link: http://www-01.ibm.com/support/docview.wss?uid=swg21574387

 

[peter_flores]

16 hours ago, Gribo said:

Did you check this link: http://www-01.ibm.com/support/docview.wss?uid=swg21574387

I did not, thanks for sharing, I will review... Silly question - What does LabVIEW and Excel have to do with IBM?

[Gribo]

It is the same error code.

[Neil Pate]

Peter, have you tried accessing the Excel worksheet by index instead of name? 

[gsussman]

While I can't offer a specific solution to your current problem, the issue of integration with Microsoft Office components has been one of those thorns in the side of developers for many years. The problems you are seeing with incompatibilities between versions of Excel are not something that is likely to go away after delivery. Corporate IT departments are continually pushing new patches and versions from Microsoft which will from time to time break your interface.

As a general rule I do my absolute best to sway the customer away from direct integration between MS Office tools and the operational LV code. It has always lead to heartache and support issues. Every....Single....Time.

[peter_flores]

8 minutes ago, gsussman said:

While I can't offer a specific solution to your current problem, the issue of integration with Microsoft Office components has been one of those thorns in the side of developers for many years. The problems you are seeing with incompatibilities between versions of Excel are not something that is likely to go away after delivery. Corporate IT departments are continually pushing new patches and versions from Microsoft which will from time to time break your interface.

As a general rule I do my absolute best to sway the customer away from direct integration between MS Office tools and the operational LV code. It has always lead to heartache and support issues. Every....Single....Time.

We have pondered this same thing... It is appearing this is tied to a specific Windows 10 Update that we would have no way to control for... What are common, high-value, alternatives to MS Office reporting? Obviously, there are the TDMS, csv, and HTML (you would need a good graphic/web designer for this, yea?)... Our report has a mixture of pictures, printable pages with header/footer, variable number of worksheets (one per experiment in a batch), and raw, CSV-like data. It is hard for me to think of anything that does all of this at all, let alone with the simplicity of Excel...

Maybe diadem?

[peter_flores]

3 hours ago, Neil Pate said:

Peter, have you tried accessing the Excel worksheet by index instead of name? 

Have not, will do, but this is definitely a more broad issue and we have not been successful in isolating it to a specific function or step. It either all works or crashes at indeterminate functions in LabVIEW. Thanks!

[ShaunR]

This is the main reason ActiveX and .NET are banned from my projects.

HTML is the output of choice, currently. You can even use simple string replace on keywords in report templates for most things. You don't have to be a web developer but if you can palm it off, erm, I mean, outsource it to IT, then that's a bonus. It also means that later, with a bit of javascript, you can make them into "live" reports and interfaces.

[gsussman]

We have used HTML templates as ShaunR mentioned as an effective method of generating reports. One of the nice features of this method is that the customer has the latitude to modify the HTML code as they see fit to configure the page look and feel. We provide a list of "tags" that can be inserted into the HTML that our program just does a direct string replace for the data of interest.

If the requirement is the ability to import data into MS Office or other programs for post processing then CSV or tab delimited text seems to be the least problematic.

Depending on the level of reporting and data logging that needs to be done, data storage into a database with reporting by one of the many Business Intelligence and visualization tools might be the answer. I have one customer who has done some pretty amazing work with Tableau. I have also heard that Microsoft BI is a decent package as well.

[Rolf Kalbermatter]

We recently came across this problem. Not really Report Generation Toolkit related but in our own library to interface to Excel. Microsoft seems to have changed the interface to the Save and SaveAs methods once again in Office 2016. LabVIEW as a statically compiled system implements the interface to ActiveX as a static dispatch interface at runtime. Only at compile time does it reevaluate the method interface to the actually installed type library on the current computer. This is a choice that works fine in most cases and is pretty fast performance wise but fails if someone changes the ActiveX interface of a component and you try to call that component from LabVIEW without wanting to bother about the actual version that is installed on the final target system.

Microsoft however does not provide compatibility methods that support the old interface, since they feel that the ActiveX dynamic dispatch capability, where a caller can find out about and construct the necessary dispatch interface at runtime, makes that unnecessary. Unfortunately ActiveX is considered both by Microsoft and NI as a legacy technology so neither party has much interest to invest any time at all into this beyond keeping it working the same as until now.

Basically there is no easy solution for this. You have to compile an app on a computer that uses the same office version as what the target computer will use. The only way around that is to actually create separate wrappers for the Save methods on two different computers with each their own version of MS office installed and then in your app determine the actual Office version that is installed and then invoking the correct VI dynamically. A possible but painful workaround.

For older Office versions I believe NI already incorporated such a fix into the RGT Toolkit for the save method (and used a somewhat sneaky trick to avoid accidental recompilation of the relevant dynamic VIs during an application build, which would adapt them to whatever Office version is currently installed on the machine) but obviously this hasn't been updated for Office 2016 yet. But it's a maintenance nightmare for sure for them, but the alternative of implementing runtime dynamic dispatch to Active X methods would be a major investment with several possible problems for existing application in terms of performance, and that is very unlikely to happen, since ActiveX is already considered a legacy technology for about a decade.

[viSci]

I have greatly simplified my life by using the XLR8 3rd party toolkit for all my .xlsx needs.

http://sine.ni.com/nips/cds/view/p/lang/en/nid/212056

Edited May 16, 2017 by viSci