Net Express reading a spreadsheet

+2

Chris Glazier
Moderator
3697 replies
Forum|Forum|13 years ago
February 23, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

Yes, there is a way of using the Exception Manager class to register a callback for OLE Exceptions, (see code below) but the error that you are getting is a Run-time system error and not an OLE Exception. A 114 error is not a recoverable error as it is usually caused by something going terribly wrong.

What do you mean by "invalid cell"?
Is the object returned by the getCell method a valid object?
What is the data description for Cell-Value?

I cannot reproduce this error as I can use getValue on cells that have not yet been initialized and it still works OK.

What product, version number and wrappack level are you using?
Can you reproduce this error at will?

The following is the Excel demo provided with Net Express 5.1 with code modified to setup an exception handler to trap OLE exceptions:


      *> This is required for OLE applications
      $set ooctrl( P)

      ****************************************************************
      * Copyright (C) 1997-1999 Micro Focus International Ltd.
      * All Rights Reserved.
      ****************************************************************
      * The program demonstrates the use of OLE Automation to        *
      * send messages to objects in another application. This        *
      * example uses Microsoft Excel.                                *
      *                                                              *
      * The Excel interface is documented in the file vbaxl8.hlp     *
      * shipped with Microsoft Excel 97 and vbaxl9.chm shipped       *
      * with Microsoft Excel 2000.                                   *
      *                                                              *
      * REQUIREMENTS: Microsoft Excel 97 or above needs to be        *
      * installed.                                                   *
      *                                                              *
      * $(#) V1.0                                                    *
      ****************************************************************

       class-control.
           MSExcel is class "$OLE$Excel.Application"
           entrycallback is class "entrycll"  *> Callback class
           exceptMgr is class "exptnmgr"      *> Exception manager class
           olesup is class "olesup"           *> OLE support class
           oleExceptMgr is class "oleexpt".    *> OLE exception class

       working-storage section.

       01 ExcelObject          object reference.
       01 WorkBooksCollection  object reference.
       01 WorkBook             object reference.
       01 Cell                 object reference.
       01 CellRange            object reference.
       01 ChartsCollection     object reference.
       01 Chart                object reference.
       01 osException          object reference value null.
       01 lnkNullReference     object reference value null.
       01 LoopCount            pic xx comp-5.
       01 myvalue              pic 9.

      *> Required for re-entrancy (callback entry point below)
       local-storage section.
       *> Parameters for exception callback
       linkage section.
       01 lnkErrorNumber  pic x(4) comp-5.
       01 lnkErrorObject  object reference.
       01 lnkErrorText    object reference.
       procedure division.
      *>   Create a new instance of Microsoft Excel
           invoke MSExcel "new" returning ExcelObject

      *> 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
           invoke ExcelObject "setVisible" using by value 1

      *>   Get the collection of WorkBooks
           invoke ExcelObject "getWorkBooks"
               returning WorkBooksCollection

      *>   Add a new WorkBook to the collection
           invoke WorkBooksCollection "Add"
               returning WorkBook

      *>   Now loop, filling in the cells A1, B1 and C1 with
      *>   the numbers 1, 2 and 3 respectively
           perform varying LoopCount from 1 by 1
                   until LoopCount > 3
               invoke ExcelObject "getCells" using by value 1
                                                   by value LoopCount
                                             returning Cell
               invoke Cell "setValue" using by value LoopCount
               invoke Cell "finalize" returning Cell
           end-perform

      *>   Select the range of cells that we have just populated
           invoke ExcelObject "getRange" using z"A1:C1"
                                         returning CellRange
           invoke CellRange "Select"

      *>   Get the collection of charts
           invoke ExcelObject "getCharts" returning ChartsCollection

      *>   Add a new chart to the collection
           invoke ChartsCollection "Add" returning Chart

      *>   Set the chart type to be 3D-Column
           invoke Chart "setType" using by value -4100

      *>   Close the WorkBook, discarding the contents
           invoke WorkBook "Close" using by value 0

      *>   Finalize all objects
           invoke Chart "finalize" returning Chart
           invoke ChartsCollection "finalize" returning ChartsCollection
           invoke CellRange "finalize" returning CellRange
           invoke WorkBook "finalize" returning WorkBook
           invoke WorkBooksCollection "finalize"
               returning WorkBooksCollection

      *>   Exit Excel
           invoke ExcelObject "Quit"
           invoke ExcelObject "Finalize" returning ExcelObject

           stop run.


       callback section.
           entry "onOleException" using by reference lnkErrorObject
                                        by reference lnkErrorNumber
                                        by reference lnkErrorText.
           display "Excel had returned an error..."
           display "The COBOL exception number was: " lnkErrorNumber
           display "The exception occured on:"
           invoke lnkErrorObject "display"
           display " "
           if lnkErrorText not = null
              invoke lnkErrorText "display"
              display " "
           end-if
           stop "Press a key to continue"
           exit program returning lnkNullReference.

D

Dominique Sacre
Author
Rocketeer
19312 replies
Forum|Forum|13 years ago
February 23, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

I added the logic to be the same as yours. I think. I stipped down the program so that it can be run from the animator.


      **************************************************************
      *> This is required for OLE applications
      $set ooctrl( P)
      *
      *
       SPECIAL-NAMES.
           CONSOLE IS CRT.
       FILE-CONTROL.
      *
      *
      $set CONSTANT CLASS-WRK-FLG(1)

       CLASS-CONTROL.
           Association        is class "associtn"
           Behavior           is class "behavior"
           CharacterArray     is class "chararry"
           CobolPicX          is class "picx"
           Dictionary         is class "dictinry"
           EntryCallBack      is class "entrycll"
           ExceptMgr          is class "exptnmgr"
           OleExceptMgr       is class "oleexpt"
           OleSup             is class "olesup"
           OleVariant         is class "olevar"
           OrderedCollection  is class "ordrdcll"
           SortedCollection   is class "srtdclln"
           ValueSet           is class "valueset"
           MSExcel is class "$OLE$Excel.Application"
          .
      *
       DATA DIVISION.
       FILE SECTION.
      *
       WORKING-STORAGE SECTION.
       01  IX                  PIC 9(04).
       01  IX2                 PIC 9(04).
       01  I-Row               Pic 9(06).
       01  I-Col               Pic 9(04).
      *
       01  SAVE-LINK-EXTRA        PIC X(600).
      *                                Count for read next/prev
       01 ExcelObject          object reference.
       01 WorkBooksCollection  object reference.
       01 WorkBook             object reference.
       01 Cell                 object reference.

       01  Cell-Value          pic x(50).

       01 osException      object reference value null.
       01 lnkNullReference object reference value null.

       local-storage section.

       LINKAGE SECTION.
       01 lnkErrorNumber  pic x(4) comp-5.
       01 lnkErrorObject  object reference.
       01 lnkErrorText    object reference.
      *
       PROCEDURE DIVISION.
      *
       PRINT-REPORT.
           invoke MSExcel "new" returning ExcelObject

           *> 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

      *>   Make Excel visible
      *    invoke ExcelObject "setVisible" using by value 1

      *>   Get the collection of WorkBooks
           invoke ExcelObject "getWorkBooks"
               returning WorkBooksCollection

           invoke WorkbooksCollection "Open" using
                               "C:\\rnd60\\Invoice_00003309DE_021112.csv"
               returning Workbook.

           PERFORM VARYING I-COL FROM 1 BY 1 UNTIL I-COL > 8
               Perform Read-Excel
           END-PERFORM.
      *>   Exit Excel
           invoke ExcelObject "Quit"
           invoke ExcelObject "Finalize" returning ExcelObject.
       GET-DETAIL-99.
      *
       READ-EXCEL.
           invoke ExcelObject "getCells" using by value 1
                                               by value I-COL
                                           returning Cell.
           invoke Cell "getValue" returning Cell-value.
           invoke Cell "finalize" returning Cell.
      *
      ******************
      **  END OF JOB  **
      ******************
      *
       ENDJOB.
           Stop Run.
      *
       callback section.
       entry "onOleException" using by reference lnkErrorObject
                                    by reference lnkErrorNumber
                                    by reference lnkErrorText.
           display "Excel had returned an error..."
           display "The COBOL exception number was: " lnkErrorNumber
           display "The exception occured on:"
           invoke lnkErrorObject "display"
           display " "
           if lnkErrorText not = null
               invoke lnkErrorText "display"
               display " "
           end-if
           stop "Press a key to continue"
           exit program returning lnkNullReference.

Here is what I am trying to read. It is actually a CSV file. But i also did this from an Excel file.


Jane Doe,Company Name,- A102NM,Address 2,City,State,Zip,US

The 3rd field is the one that is having the issue. Although, with this version of the program, it won't even read the first column and that contains valid data.

The error starts now when I invoke ExcelObject "getCells". The exception number is 0000065537. The text returned is "an instance of the class orderedcollection

Norman

D

Dominique Sacre
Author
Rocketeer
19312 replies
Forum|Forum|13 years ago
February 23, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

I added the logic to be the same as yours. I think. I stipped down the program so that it can be run from the animator.


      **************************************************************
      *> This is required for OLE applications
      $set ooctrl( P)
      *
      *
       SPECIAL-NAMES.
           CONSOLE IS CRT.
       FILE-CONTROL.
      *
      *
      $set CONSTANT CLASS-WRK-FLG(1)

       CLASS-CONTROL.
           Association        is class "associtn"
           Behavior           is class "behavior"
           CharacterArray     is class "chararry"
           CobolPicX          is class "picx"
           Dictionary         is class "dictinry"
           EntryCallBack      is class "entrycll"
           ExceptMgr          is class "exptnmgr"
           OleExceptMgr       is class "oleexpt"
           OleSup             is class "olesup"
           OleVariant         is class "olevar"
           OrderedCollection  is class "ordrdcll"
           SortedCollection   is class "srtdclln"
           ValueSet           is class "valueset"
           MSExcel is class "$OLE$Excel.Application"
          .
      *
       DATA DIVISION.
       FILE SECTION.
      *
       WORKING-STORAGE SECTION.
       01  IX                  PIC 9(04).
       01  IX2                 PIC 9(04).
       01  I-Row               Pic 9(06).
       01  I-Col               Pic 9(04).
      *
       01  SAVE-LINK-EXTRA        PIC X(600).
      *                                Count for read next/prev
       01 ExcelObject          object reference.
       01 WorkBooksCollection  object reference.
       01 WorkBook             object reference.
       01 Cell                 object reference.

       01  Cell-Value          pic x(50).

       01 osException      object reference value null.
       01 lnkNullReference object reference value null.

       local-storage section.

       LINKAGE SECTION.
       01 lnkErrorNumber  pic x(4) comp-5.
       01 lnkErrorObject  object reference.
       01 lnkErrorText    object reference.
      *
       PROCEDURE DIVISION.
      *
       PRINT-REPORT.
           invoke MSExcel "new" returning ExcelObject

           *> 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

      *>   Make Excel visible
      *    invoke ExcelObject "setVisible" using by value 1

      *>   Get the collection of WorkBooks
           invoke ExcelObject "getWorkBooks"
               returning WorkBooksCollection

           invoke WorkbooksCollection "Open" using
                               "C:\\rnd60\\Invoice_00003309DE_021112.csv"
               returning Workbook.

           PERFORM VARYING I-COL FROM 1 BY 1 UNTIL I-COL > 8
               Perform Read-Excel
           END-PERFORM.
      *>   Exit Excel
           invoke ExcelObject "Quit"
           invoke ExcelObject "Finalize" returning ExcelObject.
       GET-DETAIL-99.
      *
       READ-EXCEL.
           invoke ExcelObject "getCells" using by value 1
                                               by value I-COL
                                           returning Cell.
           invoke Cell "getValue" returning Cell-value.
           invoke Cell "finalize" returning Cell.
      *
      ******************
      **  END OF JOB  **
      ******************
      *
       ENDJOB.
           Stop Run.
      *
       callback section.
       entry "onOleException" using by reference lnkErrorObject
                                    by reference lnkErrorNumber
                                    by reference lnkErrorText.
           display "Excel had returned an error..."
           display "The COBOL exception number was: " lnkErrorNumber
           display "The exception occured on:"
           invoke lnkErrorObject "display"
           display " "
           if lnkErrorText not = null
               invoke lnkErrorText "display"
               display " "
           end-if
           stop "Press a key to continue"
           exit program returning lnkNullReference.

Here is what I am trying to read. It is actually a CSV file. But i also did this from an Excel file.


Jane Doe,Company Name,- A102NM,Address 2,City,State,Zip,US

The 3rd field is the one that is having the issue. Although, with this version of the program, it won't even read the first column and that contains valid data.

The error starts now when I invoke ExcelObject "getCells". The exception number is 0000065537. The text returned is "an instance of the class orderedcollection

Norman

D

Dominique Sacre
Author
Rocketeer
19312 replies
Forum|Forum|13 years ago
February 23, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

I found the issue why it would not read the first cell. The field "I-Col" needed to be defined as a "Comp-5". I now get the exception error number of 0000065539 on the correct cell but the message is the same. There also does not seem to be a way that the main section of the program knows that the error has occured.

Norman

D

Dominique Sacre
Author
Rocketeer
19312 replies
Forum|Forum|13 years ago
February 23, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

I found the issue why it would not read the first cell. The field "I-Col" needed to be defined as a "Comp-5". I now get the exception error number of 0000065539 on the correct cell but the message is the same. There also does not seem to be a way that the main section of the program knows that the error has occured.

Norman

D

Dominique Sacre
Author
Rocketeer
19312 replies
Forum|Forum|13 years ago
February 23, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

I found the issue why it would not read the first cell. The field "I-Col" needed to be defined as a "Comp-5". I now get the exception error number of 0000065539 on the correct cell but the message is the same. There also does not seem to be a way that the main section of the program knows that the error has occured.

Norman

+2

Chris Glazier
Moderator
3697 replies
Forum|Forum|13 years ago
February 23, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

The problem is that the value in the cell begins with a "-" character so Excel is treating it as if it were numeric because by default all cells are category General which means it can contain anything.

I got this to work by creating an Excel spreadsheet and changing the format of the column to Text instead of General.

I got it to work in the csv file by enclosing the value in single quotes, e.g. '- A102NM'.

I believe that there is a way to programatically set the format of a cell to Text but off the top of my head I cannot remember what it is.

When I leave the value as is I get the error 65539 also but the ErrorObject actually contains the OLE error 80020005 - Type Mismatch.

You can create a flag in working-storage that can be set in the error handler so that when you return to the main program you can tell that an error has occurred.

D

Dominique Sacre
Author
Rocketeer
19312 replies
Forum|Forum|13 years ago
February 24, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

I have been able to get it to work. But it does not return the value of the cell, but I can report the issue to the operator. If you run across the method to change the data to text and then read it correctly I would be appreciative.

Norman

D

Dominique Sacre
Author
Rocketeer
19312 replies
Forum|Forum|13 years ago
February 24, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

I have been able to get it to work. But it does not return the value of the cell, but I can report the issue to the operator. If you run across the method to change the data to text and then read it correctly I would be appreciative.

Norman

D

Dominique Sacre
Author
Rocketeer
19312 replies
Forum|Forum|13 years ago
February 24, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

I have been able to get it to work. But it does not return the value of the cell, but I can report the issue to the operator. If you run across the method to change the data to text and then read it correctly I would be appreciative.

Norman

+2

Chris Glazier
Moderator
3697 replies
Forum|Forum|13 years ago
February 24, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

OK I found a way to get the value of the invalid cell without going through the exception handler.

The code to change the format of the cell to text is:


  invoke ExcelObject "getRange" using z"A1:D1"
                                returning CellRange
  invoke cellrange "setnumberformat" using z"@"

but unfortunately this does not work because Excel would have already populated the cell with the error text:
#NAME?, which means that it does not recognize the value that was in the field.

In order to get the actual value you need to read the formula for the cell.
I got the following to work and an exception is not generated.


            invoke ExcelObject "getCells" using by value 1
                                                by value I-COL
                                          returning Cell.

            invoke cell "getText" returning cell-value.
            if cell-value = z"#NAME?"
               invoke cell "getFormula" returning cell-value
            end-if
            invoke Cell "finalize" returning Cell.

D

Dominique Sacre
Author
Rocketeer
19312 replies
Forum|Forum|13 years ago
February 27, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

That took care of it. Thanks for the help.

Norman

D

Dominique Sacre
Author
Rocketeer
19312 replies
Forum|Forum|13 years ago
February 27, 2012

[Migrated content. Thread originally posted on 22 February 2012]

I have several Net Express programs that read Excel spreadsheets and they do it quite well. The only issue that I have is when it reads an invalid cell. The program crashes.

I receive the following error:

Execution of the program EXPTNMGR has been interupted. This program is not animatable, but you can view the call/perform stack. The cause of the interupt was: 114 Attempt to access item beyond bounds of memory.

Are there any error handling routines for this?

The actual statements are as follows:

invoke ExcelObject "getCells" using by value I-Row *> This statement works fine
by value I-Col
returning Cell.
invoke Cell "getValue" returning Cell-Value. *> This is where I get the above message
invoke Cell "finalize" returning Cell. *> Does not get to this statement

The actual value in the cell is "- SUITE A102NM" (quotes not part of cell value)

Any ideas?

NormanG

That took care of it. Thanks for the help.

Norman

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register:

Scanning file for viruses.

This file cannot be downloaded