what can cause an invalid Excel worksheet reference

[loveac]

Hello Fellow LabVIEW users,

I am having difficulty with supporting an application that has been in use by our customer for several years.  The customer says that occasionally the test data does not properly get populated into the Excel data sheet.   I am supporting this application in LV 7.1 but it appears that it was written in LV 6.0.3 which I do not have but I may be able to install if I need to change something.  The gist of the problem is that occasionally and intermittantly, the test data sheet will have some of the data written properly into the data sheet and then the rest will not be written leaving the "marker" values present.  I have run some tests using the vi which writes the data and if the data is simply not in the array, you get blank spaces in the test record where the "marker" values were in the template test data sheet.  I have been able to reproduce the appearance of the problem by simply closing the Excel workbook during the write operation but the customer says that the operator has not seen the Excel application or workbook close when this happens.  These observations lead me to beleive that the worksheet refnum is somehow being changed or corrupted.  Is there anything I can do at my desk to cause the worksheet refnum to become corrupted?  I ask this because the likely hood of me catching this problem at the customer's site is very low unless I camp out there for a long time.  If I canconfirm that the worksheet refnum is being corrupted, is there anything I can do to either prevent it or at least detect it and throw an error?  Attached is the vi that is used to write the test data to the excel worksheet. 

Thanks Everyone,

Tony

[loveac]

Hello? any body home?

*Bump*

Thanks,

Tony

[Ben]

I took a look earlier today but my machine with LV 7.1 did not have any extra tool kits installed so your VI was broke for me.

The first VI that gets the Excel ref... is tht part of the tool kit?

Post a screen shot of that VI for us to take a look at.

Just trying to help,

Ben

[loveac]

Hello Ben,

Thanks for trying to help.  Here are the first 3 vi's that open Excel, the workbook, and the sheet.

Thanks,

Tony

[Ben]

If you are certain that there are NO other functions that could be using Excel at the time this issue happens then it is probably the user closing the work-sheet.

Here is a thread where I had to protect 48 workbooks from the user closing Excel.

THe "interactive" property solved my challenge.

I hope that helps,

Ben

[loveac]

Thanks Ben,

I asked the customer if the operator might have "accidently" closed the worksheet or Excel and he said "absolutely not".  Apparently, the worksheet is visible to the operator as it is being filled out and the operator does not touch the system until the report is ready to print.  No other process of mine should be doing anything with excel at that time so maybe some fluke in Windows or excel itself????  I do not know what version of excel is on the machines but this project is vintage 2002-2004.  Any other thoughts???

Thanks, 

Tony

[Matthew_Kelton]

Tony,

I don't have the toolkits installed on this machine, but did notice that Report - Get Cell Value doesn't use the error cluster.  If an error occurs in it, it will be ignored.  Also, if the VI which calls this VI doesn't handle the error, you may never get notified of it.  Based on your explained behavior, an error in the Report - Get Cell Value will cause the output to be an empty string and skip cells.

[Michael_Munroe]

One other condition that can result in your described behavior is the operator pressing any charater key on the keyboard including the space bar when Excel is the top window.  This puts Excel into the "cell edit" mode and all subsequent active-X calls are ignored until ESC or return is pressed on the keyboard.  I had the same problem with one customer and used a cell write followed by a read-verify to catch the condition.  The solution for is to use a windows SENDKEY call to put an ESC into the Excel Application.  For simplicity, I wrote a macro in MS Word to send the key to Excel.  The Excel Sendkey call cannot be used in this state.

Sub AutoOpen()

On Error Resume Next
AppActivate "Microsoft Excel", False
SendKeys "{esc}", True
ThisDocument.Application.Quit False

End Sub

[loveac]

Michael,

I tried your solution here at the office and I could not get it to exhibit the behavior of not responding to active-X calls when the keyboard is pressed.  I have Excel 2007 on my machine but I knew that the customers machine had an older version so I tried it when I was at the customer's site this morning on another matter.  To my surprise, pressing the space bar during the spreadsheet write produced the described behavior on Excel 97!  I wonder which version is the last to have this behavior?  I have told the customer to treat it as a training issue for the short term and we will address the code shortly when they are ready  for us to do the work. (Old system, tight budgets).  Do you think that keeping Excel minimized while I write to it and then bring it to the front when the write is done would solve the problem as well?  The operator does not really need to see the report being filled out, they just have to know when it is done.

Thanks,

Tony

[Matthew_Kelton]

I wouldn't even have Excel VISIBLE while you create the worksheet.  After you have created it, you can make Excel visible and display the report.  Look at the link Ben has in his reply, you will see there is a property for Excel being visible.  I never show Excel when I am using it.