Error -2147352573 in Excel Solution

[Paul567]

I encountered this problem recently and fought with it for a while and finally figured it out. I wanted to make note of this issue so that others can hopefully learn from my mistakes. Not sure if this is the best place for it but hey, why not.

TL;DR

• If you are getting error -214735257 you have some kind of version incompatibility between Excel and the Report Generation Toolkit. Even if your version of the Report Generation Toolkit it supposed to work with your version of Excel, its not.
• Isolate exactly where your problem is occuring, what specific VI are you calling that is causing this problem? For whatever reason, the Report Generation Toolkit is not playing nice with Excel and is bugging out when trying to do that action. Find out what the problem is and find a way to not have to call that VI.
• Create a template to do the majority of the work. Put in all your shading, merge all your cells that you need to merge, apply borders and even put functions in the spreadsheet itself to reduce the amount of work you need to do with LabVIEW.
• Do not use  Excel Easy Text.vi. This VI does way too much and can possibly cause compatibility problems. Instead use Append Report Text.vi. All you do is change the value of a cell at a given position. Nothing more, nothing less and you are unlikely to cause an incompatibility error as a result.

Background

I was building a fairly simple data logging and control application for a customer. The software was supposed to collect data and generate a report to an Excel spreadsheet. No real problem right? I built the application and was able to simulating in my office before deploying it to the customers machine. We hit a snag when the computer that would be running the code didn't have Excel on it (whoops) we aranged for their IT guys to come in and install it for them and we could handle any troubelshooting remotely. I was confident in my code as I tested it with simulated data and knew it would operate correctly... famous last words.

The Errors

Once the customer got Excel up and running and tried to use the software I deployed he got  error when generating his report. Since the report generation code was easly isolated from the rest of the program I was able to mock up a test application to isolate the problem better and we found that we were getting undefined error -2147352573. Searching through the ni.com the most likely candidate of this problem was incompatibility between their version of Excel (2007) and the version of the Report Generation Toolkit I deployed on their system (2013). The problem with this, was that Report Generation Toolkit 2013 is supposed to work with Excel 2007. Further investigation showed that the code was specifically erroring out while trying to apply the background colour to a cell.

The Solution

To resolve the issue I did the following:

1. Modified my template to do the majority of the work. All cells that needed to be coloured or given a border were done so in the template. Cells that would be numbered had their formatting type and percision set. I even included functions in several cells to perform basic calculations in the spreadsheet itself to reduce the amount of work I had to make LabVIEW perform. One cool thing I did was I placed a graph on my spreadsheet and had the data from the spreadsheet come from cells that I would populate using LabVIEW. This way I didn't have to fight with inserting a graph; I only needed to write text to cells.
2. Stop using Excel Easy Text.vi. This VI does a lot, it merges cells, apply borders and adjusts the font of the cells. Using this VI was bad because it a) called functions that were breaking my application and b) broke the template I was trying to use by formatting the cells in ways I didn't want to be formatted. Using Append Report Text.vi only changes the text of the cell preventing your template from getting wrecked.

I hope that helps people. I know the real anwser of "why is this happening" isn't really getting solved but as I mentioned this wasn't on my machine, it wasn' an environment that I could try everything or isolate all the conditions. My solution is more of a work around and comes from me spending (probably more) time with the customer remotely trying to solve the issue.

If anyone has any other questions please feel free to let me know.