Getting Around Error -41112 in Report Generation Toolkit

[yaHeard]

I have an application that runs tests then writes the data to Excel at the completion of each test. The problem is that the technicians use Excel to record what they did during their shift and if they are in the middle of editing a cell in Excel when a test completes, LabVIEW generates error -41112 when trying to create the output file. Here is the error description:

"Uncommitted changes appear in an active cell of an Excel worksheet. Select the cell and press the Enter key to commit all changes."

Just to be clear, this error only occurs when a cell is in the process of being edited as shown in the screenshot:

I'm writing some code that will check for this specific error when trying to write the output file and if it does, stop the cell from being edited and then retry the data output. The problem that I'm running into is that I cannot figure out a way to command Excel to stop the cell from being edited (esentially hit the ENTER key in Excel). There is a SendKeys method for the Excel Application through ActiveX, but I have not had success in using this method to send the ENTER key to Excel. Changing the active cell does not work either. Any suggestions?

[ben64]

That will not solve your problem but this is caused by the subVI NI_Excel.lvclass:new report subVI.vi where the Automation Open function uses the default value of false for the open new instance input.

Ben64

[WestOfEden]

Hello,

I've been looking into this and haven't been able to find a workaround or anything for it after some research. I think what you're trying to do by have Excel move to a different cell and not be considered editing the cell might be the way to go. I'll see if I can dig up anything else. 

[yaHeard]

Thanks Eden. I have used ActiveX to move which cell is active but it doesn't cancel the user's edit of the original cell. Let me know what you find. Also, say hi to Mr. Cunningham for me.

[Bob_Schor]

Since LabVIEW is using "hooks" into Excel, I can well imagine that if the User is also doing things with Excel, you have a "Too Many Cooks" problem.  I ran into a variant of this -- we use Excel to "program" our Experiments, with each row representing a Trial, and the columns holding the (about 100) variables that characterize the Trial.  We also write a few "Trial summary" values in the last few columns, including pointers into the LabVIEW data files for the Experiment (so we can extract, say, the sampled Analog data collected during that trial).  After one Experiment, my student came and said "Where did our Excel file go?"  I found it in the Recycle bin, of course.  Turned out that before exiting the Program (which saves the Excel file and closes Excel), they manually saved/closed Excel.  Well, the RGT code didn't expect that someone would usurp its role in doing the saving/closing, so it deleted the file thinking it was still open.  I "fixed" this by testing to see if Excel was running (meaning the students had not closed it) and only then doing the Save/Close.

You may need to do something similar.  I'm not sure what you'd look for -- I gather your users are mucking around with the same Workbook that the RGT is using, so you'd need to test, I don't know, "is anyone playing with this Workbook?" (except I have no idea what that call would be).  If so, I'd pop up a big Dialog Box saying "You have 15 seconds to finish because I'm going to close Excel ...".  Darn Users ...

Bob Schor

[ben64]

Try adding the attached vi right before the New Report.vi.

Ben64

[yaHeard]

Thanks Bob,

My current workaround that I put in last week is similar to what you described. On one system I have a dialog popup telling the operator to commit their Excel changes then click a "continue" button and on another system I just save and close the active workbook before writing the new test data. My hope was that there was an even better solution that would keep the active workbook open but not have to wait on any kind of operator interaction (the technicians aren't always at the computer when a test completes). It may just not be possible through the Excel ActiveX properties/methods.

[WestOfEden]

Unfortunately I still haven't been able to find anything about doing that through ActiveX. It might really be the case that there isn't a way to do it that way.

[WestOfEden]

Just as an update, I have been unable to find anything outlining how to do that with ActiveX. Think it's just outside the scope of those ActiveX calls. Glad you were able to find a workaround, even though it might not be as clean as you hoped.

[ben64 · Accepted solution]

I have a similar issue on a test setup and the operators knows that they can't use Excel when a test is running. I tried a few things this weekend and realize that the vi I posted earlyer will not work if someone is actively typing (it works only if a cell is activated for editing but there is no actual typing performed). I then modified a vi to create a workbook and fill some cells in a new application windows. I ran it while editing manually another workbook and since the workbook was created in another application window I didn't get any error and the new workbook was created as expected.

I wrote a post in the LabVIEW Idea Exchange asking to add an optional "open in new instance" boolean input to the New Report.vi. You can kudo this idea in the LabVIEW Idea Exchange forum.

Ben64