[archive] Retrieving Information from Excel

[Migrated content. Thread originally posted on 01 August 2008]

I want to allow my users to create their own quote spreadsheets in Excel. I want to then open their spreadsheet from my application and fill in certain pieces of information. There are two issues I have to deal with, first, every spreadsheet could be different, so the information I am filling in has to be done by cell names instead of static cell locations (like A1 or B2, they are going to name the cells). This is working just fine. My second problem is that they might not use all the information available. I might have 10 fields available, but they are only using 5 of them, so I need to be able to check the spreadsheet and see if that cell name exists on the spreadsheet. If I don't check first, when I attempt to modify the cell, it crashes, which is expected I guess, but I don't know how to check the spreadsheet to cell if a "cell name" exists on the spreadsheet. If I bring up the spreadsheet, I can use a GoTo to get to it, but I need to be able to return a code to my program as to whether a cell with that name is on the spreadsheet or not. Any ideas?

TIA,
Rebekah

A few comments.
According to Excel VBA docs, if you do not intend to focus the cursor (and thus make it visible), you should use the Range::Select method rather than GoTo. Like:

INQUIRE MyWorkSheet Range("MyCell") IN MyRange


Further, I am pretty sure that if you try to select a cell that does not exist, it throws an exception. This probably crashes the application because there is no exception handler.
I suggest you throw in an exception handler (DECLARATIVES) and see what you get.

[Migrated content. Thread originally posted on 01 August 2008]

I want to allow my users to create their own quote spreadsheets in Excel. I want to then open their spreadsheet from my application and fill in certain pieces of information. There are two issues I have to deal with, first, every spreadsheet could be different, so the information I am filling in has to be done by cell names instead of static cell locations (like A1 or B2, they are going to name the cells). This is working just fine. My second problem is that they might not use all the information available. I might have 10 fields available, but they are only using 5 of them, so I need to be able to check the spreadsheet and see if that cell name exists on the spreadsheet. If I don't check first, when I attempt to modify the cell, it crashes, which is expected I guess, but I don't know how to check the spreadsheet to cell if a "cell name" exists on the spreadsheet. If I bring up the spreadsheet, I can use a GoTo to get to it, but I need to be able to return a code to my program as to whether a cell with that name is on the spreadsheet or not. Any ideas?

TIA,
Rebekah

I am using the Select Method to modify the cell, but I was not doing an Inquire first, nor did I have an exception handling routine in the declaratives section. Putting in the exception routine in the declaratives does keep it from quitting the program when the cell I am looking for is not in the spreadsheet. So, I can work with that, but it led to another question.

I am doing this through a perform until loop. Basically, I am starting at the beginning of a file and reading next through the file, checking the spreadsheet for the cell, and putting in the data if the cell name exists. I am performing the check for name/update routine until the end of the file. Without the exception handling, the program bombs when the cell name doesn't exist. With the exception handling, it does not bomb, but it seems to loose it's place in the perform until loop. It just drops through lines of code. So, I have had to insert GoTo statements to catch it when it drops through. Is there a reason the exception routine would make it loose it's place in the perform until loop?

Here is my declarative:

OBJECT-EXCEPTION-HANDLING SECTION.
USE AFTER EXCEPTION ON OBJECT.
OBJECT-EXCEPTION-HANDLER.
CALL "C$EXCEPINFO" USING ERROR-INFO.

I have other declaratives in this same program, this is the last one in the declaratives section. I left out the optional parameters, because I don't really care what the error number is, it was not any that were defined in the activex.def file anyway, I just don't want it to quit on an error.

Thanks,
Rebekah

[Migrated content. Thread originally posted on 01 August 2008]

I want to allow my users to create their own quote spreadsheets in Excel. I want to then open their spreadsheet from my application and fill in certain pieces of information. There are two issues I have to deal with, first, every spreadsheet could be different, so the information I am filling in has to be done by cell names instead of static cell locations (like A1 or B2, they are going to name the cells). This is working just fine. My second problem is that they might not use all the information available. I might have 10 fields available, but they are only using 5 of them, so I need to be able to check the spreadsheet and see if that cell name exists on the spreadsheet. If I don't check first, when I attempt to modify the cell, it crashes, which is expected I guess, but I don't know how to check the spreadsheet to cell if a "cell name" exists on the spreadsheet. If I bring up the spreadsheet, I can use a GoTo to get to it, but I need to be able to return a code to my program as to whether a cell with that name is on the spreadsheet or not. Any ideas?

TIA,
Rebekah

I am using the Select Method to modify the cell, but I was not doing an Inquire first, nor did I have an exception handling routine in the declaratives section. Putting in the exception routine in the declaratives does keep it from quitting the program when the cell I am looking for is not in the spreadsheet. So, I can work with that, but it led to another question.

I am doing this through a perform until loop. Basically, I am starting at the beginning of a file and reading next through the file, checking the spreadsheet for the cell, and putting in the data if the cell name exists. I am performing the check for name/update routine until the end of the file. Without the exception handling, the program bombs when the cell name doesn't exist. With the exception handling, it does not bomb, but it seems to loose it's place in the perform until loop. It just drops through lines of code. So, I have had to insert GoTo statements to catch it when it drops through. Is there a reason the exception routine would make it loose it's place in the perform until loop?

Here is my declarative:

OBJECT-EXCEPTION-HANDLING SECTION.
USE AFTER EXCEPTION ON OBJECT.
OBJECT-EXCEPTION-HANDLER.
CALL "C$EXCEPINFO" USING ERROR-INFO.

I have other declaratives in this same program, this is the last one in the declaratives section. I left out the optional parameters, because I don't really care what the error number is, it was not any that were defined in the activex.def file anyway, I just don't want it to quit on an error.

Thanks,
Rebekah

[Migrated content. Thread originally posted on 01 August 2008]

I want to allow my users to create their own quote spreadsheets in Excel. I want to then open their spreadsheet from my application and fill in certain pieces of information. There are two issues I have to deal with, first, every spreadsheet could be different, so the information I am filling in has to be done by cell names instead of static cell locations (like A1 or B2, they are going to name the cells). This is working just fine. My second problem is that they might not use all the information available. I might have 10 fields available, but they are only using 5 of them, so I need to be able to check the spreadsheet and see if that cell name exists on the spreadsheet. If I don't check first, when I attempt to modify the cell, it crashes, which is expected I guess, but I don't know how to check the spreadsheet to cell if a "cell name" exists on the spreadsheet. If I bring up the spreadsheet, I can use a GoTo to get to it, but I need to be able to return a code to my program as to whether a cell with that name is on the spreadsheet or not. Any ideas?

TIA,
Rebekah

I presume you have the full body of it:

PROCEDURE DIVISION.
DECLARATIVES
OBJECT-EXCEPTION-HANDLING SECTION.
USE AFTER EXCEPTION ON OBJECT.
OBJECT-EXCEPTION-HANDLER.
CALL "C$EXCEPINFO" USING ERROR-INFO.
END DECLARATIVES.
MAIN-LOGIC.
   ThisWillGenerateException
   MyNextStatement

When I run this, I always return to the next statement (MyNextStatement) after the failing statement (ThisWillGenerateException), do you say that when you return from the declaratives you come back to execute ThisWillGenerateException again?

[Migrated content. Thread originally posted on 01 August 2008]

I want to allow my users to create their own quote spreadsheets in Excel. I want to then open their spreadsheet from my application and fill in certain pieces of information. There are two issues I have to deal with, first, every spreadsheet could be different, so the information I am filling in has to be done by cell names instead of static cell locations (like A1 or B2, they are going to name the cells). This is working just fine. My second problem is that they might not use all the information available. I might have 10 fields available, but they are only using 5 of them, so I need to be able to check the spreadsheet and see if that cell name exists on the spreadsheet. If I don't check first, when I attempt to modify the cell, it crashes, which is expected I guess, but I don't know how to check the spreadsheet to cell if a "cell name" exists on the spreadsheet. If I bring up the spreadsheet, I can use a GoTo to get to it, but I need to be able to return a code to my program as to whether a cell with that name is on the spreadsheet or not. Any ideas?

TIA,
Rebekah

I was using a routine as follows:

OPEN DATA-FILE.
START DATA-FILE.
READ DATA-FILE NEXT RECORD.
PERFORM LOAD-SPREADSHEET THRU
LOAD-SPREADSHEET-X UNTIL END-OF-DATA-FILE.
REST OF MAIN-LOGIC.
EXIT-PROGRAM.

LOAD-SPREADSHEET.
INQUIRE hExcelWks Range("CELL-NAME") IN hExcelRng.
IF ERROR-GENERATED
GO TO LOAD-SPREADSHEET-X
END-IF.
MODIFY hExcelWks Range("CELL-NAME")::Value = CELL-VALUE.
LOAD-SPREADSHEET-X.
READ DATA-FILE NEXT RECORD.

Before I put in the exception handling routine in the declaratives section, the program would bomb out when the INQUIRE failed. If the cell-name did not exist in the spreadsheet, the program terminated. After I put in the exception handling, if it gets and error, it immediately drops into the LOAD-SPREADSHEET-X routine, and it reads the next record, but it does not continue in the perform until loop it started, it drops into the coding that comes in the program after the LOAD-SPREADSHEET-X routine. I had to put some other coding in to catch it and return it myself to the loop I am doing so that it can get out of the loop and go back to rest of the program. If the INQUIRE and the MODIFY are successful, the loop is fine. This is just an example of what I am doing. I have several other declaratives for the data files I am working with, and the routine does some other processing, and I have checks to see if I am at the end of the file, etc, but this is an example of where my loop is failing with the exception handling for the Excel object.

Thanks,
Rebekah

[Migrated content. Thread originally posted on 01 August 2008]

I want to allow my users to create their own quote spreadsheets in Excel. I want to then open their spreadsheet from my application and fill in certain pieces of information. There are two issues I have to deal with, first, every spreadsheet could be different, so the information I am filling in has to be done by cell names instead of static cell locations (like A1 or B2, they are going to name the cells). This is working just fine. My second problem is that they might not use all the information available. I might have 10 fields available, but they are only using 5 of them, so I need to be able to check the spreadsheet and see if that cell name exists on the spreadsheet. If I don't check first, when I attempt to modify the cell, it crashes, which is expected I guess, but I don't know how to check the spreadsheet to cell if a "cell name" exists on the spreadsheet. If I bring up the spreadsheet, I can use a GoTo to get to it, but I need to be able to return a code to my program as to whether a cell with that name is on the spreadsheet or not. Any ideas?

TIA,
Rebekah

I will send this issue in to tech support, because I just added more coding to this same program:

READ DATA-FILE RECORD.
PERFORM LOAD-SPREADSHEET.
REST OF MAIN-LOGIC.

And, here the exception-handling for the Excel Object works just fine. It returns to the previous position in the program with no problem. The only difference is that here, I didn't try to use a perform until loop, just a single perform statement and it seems to be fine. I think something in the exception handling causes it to loose it's position in processing the perform until loop.

Thanks,
Rebekah

[Migrated content. Thread originally posted on 01 August 2008]

I want to allow my users to create their own quote spreadsheets in Excel. I want to then open their spreadsheet from my application and fill in certain pieces of information. There are two issues I have to deal with, first, every spreadsheet could be different, so the information I am filling in has to be done by cell names instead of static cell locations (like A1 or B2, they are going to name the cells). This is working just fine. My second problem is that they might not use all the information available. I might have 10 fields available, but they are only using 5 of them, so I need to be able to check the spreadsheet and see if that cell name exists on the spreadsheet. If I don't check first, when I attempt to modify the cell, it crashes, which is expected I guess, but I don't know how to check the spreadsheet to cell if a "cell name" exists on the spreadsheet. If I bring up the spreadsheet, I can use a GoTo to get to it, but I need to be able to return a code to my program as to whether a cell with that name is on the spreadsheet or not. Any ideas?

TIA,
Rebekah

I will send this issue in to tech support, because I just added more coding to this same program:

READ DATA-FILE RECORD.
PERFORM LOAD-SPREADSHEET.
REST OF MAIN-LOGIC.

And, here the exception-handling for the Excel Object works just fine. It returns to the previous position in the program with no problem. The only difference is that here, I didn't try to use a perform until loop, just a single perform statement and it seems to be fine. I think something in the exception handling causes it to loose it's position in processing the perform until loop.

Thanks,
Rebekah

[Migrated content. Thread originally posted on 01 August 2008]

I want to allow my users to create their own quote spreadsheets in Excel. I want to then open their spreadsheet from my application and fill in certain pieces of information. There are two issues I have to deal with, first, every spreadsheet could be different, so the information I am filling in has to be done by cell names instead of static cell locations (like A1 or B2, they are going to name the cells). This is working just fine. My second problem is that they might not use all the information available. I might have 10 fields available, but they are only using 5 of them, so I need to be able to check the spreadsheet and see if that cell name exists on the spreadsheet. If I don't check first, when I attempt to modify the cell, it crashes, which is expected I guess, but I don't know how to check the spreadsheet to cell if a "cell name" exists on the spreadsheet. If I bring up the spreadsheet, I can use a GoTo to get to it, but I need to be able to return a code to my program as to whether a cell with that name is on the spreadsheet or not. Any ideas?

TIA,
Rebekah

Good work! :-)