How to prevent Excel from popping up the active report when opening any Excel file manually

[Teo7]

Hi, I have come across a strange problem with Excel while generating an Excel report in the background using the Report Generation toolkit. 

After using the "New Report.vi" in LabVIEW to create a new Excel report and while LabVIEW is populating that report in the background, if you try to open any file in Excel by double clicking on it, Excel opens the file but unfortunately it also opens a second window showing the active report template that LabVIEW is currently populating.

If you then manually close the Excel window showing the active report, LabVIEW can no longer generate the report and all data is lost!

This would cause a disaster if it happened on the production line because the UUTs are  very expensive and they may only be tested once. The test takes quite some time too.

I have searched for a solution unsuccessfully. Does anyone have a clue how I could prevent Excel from opening the active report template?

Then thhe issue occurs the error returned by the "Append Table to Report.vi is as follows:

Error -2146827864 occurred at Unknown System Error in NI_ReportGenerationToolkit.lvlib:Excel_Insert_Table.vi->NI_Excel.lvclass:Append Text Table to Report.vi->NI_report.lvclass:Append Text Table to Report (wrap).vi->PAMAS report-Write header diameters.vi->PAMAS report-Record dataset.vi->PAMAS filewrite loop logic.vi->PAMAS Engine.vi

This error code is undefined. Undefined errors might occur for a number of reasons. For example, no one has provided a description for the code, or you might have wired a number that is not an error code to the error code input.

Additionally, undefined error codes might occur because the error relates to a third-party object, such as the operating system or ActiveX. For these third-party errors, you might be able to obtain a description of the error by searching the Web for the error code (-2146827864) or for its hexadecimal representation (0x800A01A8).

I am using Excel 2016 and LabVIEW 2013SP1

[Hatef.fouladi]

why you do not create some back up files in text or spreadsheet file ?

[Bob_Schor]

We've seen something similar.  I can certainly believe that if LabVIEW is "running" Excel to interact with Excel Data Files, and the User also opens files with Excel, the two instances of Excel can interact with each other in ways that have nothing to do with LabVIEW.  In our case, we told the Users "Hands off Excel while running the LabVIEW Program.  We did find a "fix" for a related problem -- if the User (at the end of the Program) closes Excel manually before LabVIEW goes to close the file, the LabVIEW code will delete the just-created Excel file (oops!).  We wrote a "Close Excel Safely" that checks if Excel is currently running -- if it is, it will execute the Save Report and Close Report, otherwise it does nothing.

Bob Schor

[SmithGo]

Another idea would be to collect all the data until the VI/main loop is done running, run whatever sort of data analysis you're doing to it, then send all the data and results to another vi that creates the Excel report.  That way the Excel file is opened and populated at one time.  Unless you're doing a lot to the Excel file, the user shouldn't have time to mess with it before everything is done.  That's what I do with Word reports using the same kit.

[Sam_Sharp]

Having suffered through this before - my advice is that you do NOT use the report generation toolkit for creating log files / writing to it as a test is happening. Use it for generating reports once your data is complete (i.e. open/write/close as one step).

[Gregory]

Most of the users at my company want the data to automatically save at the end of the test, which I do. But, I always add a "save data" button that they can press again in case they entered an invalid filename or get some error.

For very long tests I will write the values to a CSV file or something during the test, and then afterwards I can use the RGT to make it look nice.

[Bob_Schor]

Some comments on the recent responses:

• I absolutely agree one should not use Excel for Log messages.  Buried in one of the Templates that ships with LabVIEW is some Error Log code from NI.  Basically you have a fixed Error Log filename, and every time you get an error, you do the following:  Open the File, go to the end, and write your Error Message, along with a TimeStamp.  There is also a "Max File Size parameter -- when the Error Log exceeds the Max size, a copy of the second half is made, the original is discarded, and the second half becomes the new Error Log.  I've used a Log Size of 100000 bytes, which has been more than adequate ...
• In our case, we open an Experiment-specific Excel Workbook at the beginning of the Experiment, as it contains (in its rows) the "instructions" (or "settings") for each Trial.  There are also a few columns at the end that record "End-of-Trial" information, such as Success or Failure, Times, number of Samples and Events saved to disk, etc.  The only interaction the User has with the Workbook is to determine which Trial to do next (we typically start with Trial 1 and automatically do them in sequence, but the User can stop the sequence and say "Now do Trial 13").
• When all the Trials have been done, the User is asked "End Experiment?"  If the answer is Yes, the program closes the Workbook.  As I noted earlier, if the User had closed the Workbook "by hand", the normal RGT code would delete the just-closed-and-saved file, so I wrote a "Close File Safely" that does not ask Excel to close the file if Excel isn't running, fixing this problem.

Bob Schor

[Theo_K]

"if the User had closed the Workbook "by hand", the normal RGT code would delete the just-closed-and-saved file, so I wrote a "Close File Safely" that does not ask Excel to close the file if Excel isn't running, fixing this problem."

Hi Bob and thanks for your reply. I am not sure I understand how your mechanism worked. If the user closes the Excel report manually, the RGT cannot continue building that report, so I am not sure I understand how your suggestion can help overcome this specific problem. Each report needs to be self-contained and cannot be split over two or more files either. Can you please clarify?

[Theo_K]

Hi Sam,

Would that not defeat the purpose?

We are using the RGT for the convenience and scalability it offers. If the customer decided to change their report layout all we would need to do is point to the new Excel cell where a value or an array of values should be inserted.

We are using a rather involved report format, therefore saving the data in a temporary CSV file would add a layer of significant complexity.

Certain values in the report can also be changed/overwritten retrospectively before the report is closed, which makes the temp CSV file approach even less appealing.

I can see that a few of the people who replied, have suggested the same thing (i.e. saving the data in a temporary file). This sounds like a painful workaround (perhaps the only workaround) to a rather critical RGT bug that renders the RGT completely useless for building up a report live during a test. RGT should be able to handle the problematic case, or even better the Excel report that is being populated should not be made visible in Excel under any circumstances. Would you not agree?

[Bob_Schor]

I will be happy to clarify.  I just created a simple Excel file, Test.xlsx, with the numbers 1, 2, 3 in Column A, and nothing else.  Here is the code ...

Notice the Event loop with a Stop button -- this just delays the Save Report and Dispose Report.  When I run this, Excel opens, I can see that my Excel file has 1, 2, 3, and the data being read into the array Data also has 1, 2, 3.  I can run this multiple times, and it works.

So now I demonstrate what I was saying -- I run the same program, it pauses with Excel open waiting for me to push the Stop button.  Instead, I close Excel by clicking the X in the upper right hand corner.  I then push Stop, which tries to Save Report, but it fails (some obscure Error message).  OK, so what?  Well, now my Excel file, which I've read multiple times, is missing -- it has been deleted (deleted!).  That is the problem I was describing, which I fixed by creating a function that refused to call the Save Report if (as was the case here) Excel is not running (because the Stupid User had manually closed Excel, causing LabVIEW to "punish" this bad behavior by deleting the Excel .xlsx file).

Hope this clarifies things for you.  Bottom line -- if LabVIEW is using the RGT or otherwise "using" Excel to open Excel files, then LabVIEW, not the User, must be the one to also close Excel.  Warn your users (or, better yet, open Excel minimized so the User never "sees" the Workbook).

Bob Schor