Reading Excel spreadsheet with divide by zero cell

[Migrated content. Thread originally posted on 16 September 2011]

I have a Net Express program that writes values to a spreadsheet and then reads a particular cell in the spreadsheet that contains the results of a formula. Occasionally the formula will have a divide by zero error because of some value or some other issue where the data may be bad. When this happens the program locks up or crashes, in either event it is a problem.

Is there a way to identify this type of issue and not have the program crash? The code for reading the cell is as follows.
invoke ExcelObject "getCells" using by value 23
by value 1 returning Cell.
invoke Cell "getValue" returning Cell-value.
invoke Cell "finalize" returning Cell.
inspect Cell-value replacing all x"00" by " ".
move Cell-value to d-Ret-Value.

NormanG

If this is an OLE Exception that is occurring then you could set up an Exception handler in your code so that it will be called whenever an OLE Exception is raised.

Looks like:

*> Exception handling: Register a callback to be used
*> whenever an exception is raised on the OLE exception class
invoke entrycallback "new" using z"onOleException"
returning osException
invoke exceptmgr "register" using oleExceptMgr
osException

In Net Express look at the example project in C:\\Program Files (x86)\\Micro Focus\\Net Express 5.1\\Examples\\Net Express IDE\\COMDEMOS\\WORD.

Program WORD5.CBL uses this approach.

[Migrated content. Thread originally posted on 16 September 2011]

I have a Net Express program that writes values to a spreadsheet and then reads a particular cell in the spreadsheet that contains the results of a formula. Occasionally the formula will have a divide by zero error because of some value or some other issue where the data may be bad. When this happens the program locks up or crashes, in either event it is a problem.

Is there a way to identify this type of issue and not have the program crash? The code for reading the cell is as follows.
invoke ExcelObject "getCells" using by value 23
by value 1 returning Cell.
invoke Cell "getValue" returning Cell-value.
invoke Cell "finalize" returning Cell.
inspect Cell-value replacing all x"00" by " ".
move Cell-value to d-Ret-Value.

NormanG

Chris,

I finally got back to this. I took your suggestion and went to the WORD5.cbl program and I can get that to work. What I want to do, so that a command prompt window does not appear, is to string a message together and write it to a file or a report. The issue that I have is with the "invoke" statements. They are not my strong points.

One of the statements 'invoke lnkErrorText "display" ' appears to display the message on the command prompt window, what I would want it to do is to return it to a text field which I can then string with other data and extract/print out.

Actually, is there any references to where to find all these commands and their usage?

NormanG

[Migrated content. Thread originally posted on 16 September 2011]

I have a Net Express program that writes values to a spreadsheet and then reads a particular cell in the spreadsheet that contains the results of a formula. Occasionally the formula will have a divide by zero error because of some value or some other issue where the data may be bad. When this happens the program locks up or crashes, in either event it is a problem.

Is there a way to identify this type of issue and not have the program crash? The code for reading the cell is as follows.
invoke ExcelObject "getCells" using by value 23
by value 1 returning Cell.
invoke Cell "getValue" returning Cell-value.
invoke Cell "finalize" returning Cell.
inspect Cell-value replacing all x"00" by " ".
move Cell-value to d-Ret-Value.

NormanG

Please look at the following article in our knowledgebase:

Extracting the error message text from an OLE Exception object

Thanks.

[Migrated content. Thread originally posted on 16 September 2011]

I have a Net Express program that writes values to a spreadsheet and then reads a particular cell in the spreadsheet that contains the results of a formula. Occasionally the formula will have a divide by zero error because of some value or some other issue where the data may be bad. When this happens the program locks up or crashes, in either event it is a problem.

Is there a way to identify this type of issue and not have the program crash? The code for reading the cell is as follows.
invoke ExcelObject "getCells" using by value 23
by value 1 returning Cell.
invoke Cell "getValue" returning Cell-value.
invoke Cell "finalize" returning Cell.
inspect Cell-value replacing all x"00" by " ".
move Cell-value to d-Ret-Value.

NormanG

That was exactly what I needed. Now I just hope it stops the crashing.

NormanG

[Migrated content. Thread originally posted on 16 September 2011]

I have a Net Express program that writes values to a spreadsheet and then reads a particular cell in the spreadsheet that contains the results of a formula. Occasionally the formula will have a divide by zero error because of some value or some other issue where the data may be bad. When this happens the program locks up or crashes, in either event it is a problem.

Is there a way to identify this type of issue and not have the program crash? The code for reading the cell is as follows.
invoke ExcelObject "getCells" using by value 23
by value 1 returning Cell.
invoke Cell "getValue" returning Cell-value.
invoke Cell "finalize" returning Cell.
inspect Cell-value replacing all x"00" by " ".
move Cell-value to d-Ret-Value.

NormanG

Chris,

You helped me on another issue a few months ago. The issue was that my program was crashing because of a stacking issue. You gave me some code to display that information and I cannot find it now. The message displayed in this format - "Behavior Stack - ###### Cumulate Behavior Count". Do you recall what that code was? Could you post it here?

NormanG

[Migrated content. Thread originally posted on 16 September 2011]

I have a Net Express program that writes values to a spreadsheet and then reads a particular cell in the spreadsheet that contains the results of a formula. Occasionally the formula will have a divide by zero error because of some value or some other issue where the data may be bad. When this happens the program locks up or crashes, in either event it is a problem.

Is there a way to identify this type of issue and not have the program crash? The code for reading the cell is as follows.
invoke ExcelObject "getCells" using by value 23
by value 1 returning Cell.
invoke Cell "getValue" returning Cell-value.
invoke Cell "finalize" returning Cell.
inspect Cell-value replacing all x"00" by " ".
move Cell-value to d-Ret-Value.

NormanG

Chris,

You helped me on another issue a few months ago. The issue was that my program was crashing because of a stacking issue. You gave me some code to display that information and I cannot find it now. The message displayed in this format - "Behavior Stack - ###### Cumulate Behavior Count". Do you recall what that code was? Could you post it here?

NormanG

[Migrated content. Thread originally posted on 16 September 2011]

I have a Net Express program that writes values to a spreadsheet and then reads a particular cell in the spreadsheet that contains the results of a formula. Occasionally the formula will have a divide by zero error because of some value or some other issue where the data may be bad. When this happens the program locks up or crashes, in either event it is a problem.

Is there a way to identify this type of issue and not have the program crash? The code for reading the cell is as follows.
invoke ExcelObject "getCells" using by value 23
by value 1 returning Cell.
invoke Cell "getValue" returning Cell-value.
invoke Cell "finalize" returning Cell.
inspect Cell-value replacing all x"00" by " ".
move Cell-value to d-Ret-Value.

NormanG

Never mind, I found it.

NormanG