[archive] Acucobol with MS-Excel 97

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

The simples approach, and also the most readable, is to fetch the particular worksheet you want, and modify the cells relative to that:

77 oCurrentWorkSheet HANDLE OF WORKSHEET.

*Get worksheet 1:
INQUIRE oWorkSheets Item(1) IN oCurrentWorkSheet
MODIFY oCurrentWorkSheet Range("A1")::Value =
"This is cell A1, worksheet 1"

*Get worksheet 6:
INQUIRE oWorkSheets Item(6) IN oCurrentWorkSheet
MODIFY oCurrentWorkSheet Range("A1")::Value =
"This is cell A1, worksheet 6"

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

Thanks very much for your help. That works fine.

Matt Cantillon
mpcsoft

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

Thanks very much for your help. That works fine.

Matt Cantillon
mpcsoft

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

Thanks very much for your help. That works fine.

Matt Cantillon
mpcsoft

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

Happy to hear that it was useful.

Another tip, a small digression in fact, but very useful indeed:

In case you are going to transfer a lot of data from your application to an Excel spreadsheet, you might benefit from using what is known as safearrays.

Safearrays benefit from being language independet, fast and flexible. They are indeed very fast. Transfering cell by cell by comparition is almost like the famous tell tale of the hare and the turtle. I would daresay for anyone case where you would transfer more than say, 10 items, be it numbers, text or whatever, using a safearray transfer is the better option.

See next posting for a simple example.

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

A most likely task when using MS Excel as a COM object is to transfer data to a worksheet, and probably not only a cell or two, but a huge chunk of it.

A tedious, time consuming and not at least resource demanding way of doing this is to use a cell by cell transfer.

If you are about to transfer a reasonable chunk of data, perhaps a mix of text and numbers, chances are, you would be much better of using what in the OLE world is known as SAFEARRAYs.

For those who want to do their homework, you can read more about this in the books, see: Book 1, 6.10.2.1 "Passing COBOL data to methods or properties as SAFEARRAYs".

Attached to this post, you will find a sample program excelarray.cbl. To run this, you will need to generete the definition file for excel (e.g. excel.def) and you will need to compile and have available the rand.cbl from the sample directory of your ACUCOBOL-GT installation. Eventually compile and run excelarray.cbl and watch the beauty as it happens.
Of course, having MS Excel available is also an requirement.

This example here, is exercise 6 from the Advanced Windows API, ActiveX and COM training.

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

Many thanks for the sample. Thanks to you I now have the application working. My customer wanted to be able to add data to ODBC databases (in this case, Filemaker Pro) via PALM OS devices. He then wanted to be able to put that data into a complex MS-Excel spreadsheet that does job-cost estimating. The acu4gl-enabled runtime opens the databases, pours the data into the spreadsheet, which does its magic and shows the user a print preview of the results, which he or she can then print. Because of your help, I have this working and I am sure my customer will be delighted. I will probably use your safearrays suggestion to improve the efficiency of the program. I am not sure I would have chosen Filemaker for the databases, but my customer insisted on using it, and it does have its own PALM conduit, so it works well.
I do not think I could have pulled this off with any other platform. Thanks again.

Matt Cantillon
mpcsoft

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

Many thanks for the sample. Thanks to you I now have the application working. My customer wanted to be able to add data to ODBC databases (in this case, Filemaker Pro) via PALM OS devices. He then wanted to be able to put that data into a complex MS-Excel spreadsheet that does job-cost estimating. The acu4gl-enabled runtime opens the databases, pours the data into the spreadsheet, which does its magic and shows the user a print preview of the results, which he or she can then print. Because of your help, I have this working and I am sure my customer will be delighted. I will probably use your safearrays suggestion to improve the efficiency of the program. I am not sure I would have chosen Filemaker for the databases, but my customer insisted on using it, and it does have its own PALM conduit, so it works well.
I do not think I could have pulled this off with any other platform. Thanks again.

Matt Cantillon
mpcsoft

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

Many thanks for the sample. Thanks to you I now have the application working. My customer wanted to be able to add data to ODBC databases (in this case, Filemaker Pro) via PALM OS devices. He then wanted to be able to put that data into a complex MS-Excel spreadsheet that does job-cost estimating. The acu4gl-enabled runtime opens the databases, pours the data into the spreadsheet, which does its magic and shows the user a print preview of the results, which he or she can then print. Because of your help, I have this working and I am sure my customer will be delighted. I will probably use your safearrays suggestion to improve the efficiency of the program. I am not sure I would have chosen Filemaker for the databases, but my customer insisted on using it, and it does have its own PALM conduit, so it works well.
I do not think I could have pulled this off with any other platform. Thanks again.

Matt Cantillon
mpcsoft

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

I have my application running properly except for one small problem, and that is that the Excel task is not being terminated properly. I have destroyed all the handles, just out of curiosity, I bring up the windows xp task box, and there is an EXCEL.EXE still showing there. What could I be doing improperly. This seems pretty straightforward. Any ideas would be appreciated.

Thanks,

Matt C

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

I am pleased to hear that you have this extensive scenario, including handheld, Acu4GL and MS Excel all cooperating. I would very much enjoy it, if you would take the time to write me some lines explaining how all of these cooperate. Their purpose and goal.

As for your current problem. Thing is, with COM objects, the COM engine you are invoking, in this case Excel, works outside of the runtime, so anytime you fetch an interface to a COM object, there is a counter that is incremented. Upon exit of your application, when you close Excel. Excel is checking its counters to see if anyone is still using it. As you have learned the hard way now, there is. This is a very common thing to forget. The rule of thumb is that for each interface you get (that is, each variable of type USAGE HANDLE OF somecomobject) you *must* execute a DESTROY.

Like in the original sample I gave you here in which you were performing an INQUIRE to get the oCurrentWorkSheet. When you are done with that worksheet, *before* you assign the next worksheet, you must do a destroy DESTROY oCurrentWorkSheet to decrement the counter.

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

Thanks again for all your help. I realized later that I was not destroying everything, and once I did so, Excel is gone! I would be happy to describe my application for you. I can e-mail you (is it gforseth@acucorp.com ?) if you like, or I can post it here for you.

Thanks again,

Matt Cantillon/mpcsoft.

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

Thanks again for all your help. I realized later that I was not destroying everything, and once I did so, Excel is gone! I would be happy to describe my application for you. I can e-mail you (is it gforseth@acucorp.com ?) if you like, or I can post it here for you.

Thanks again,

Matt Cantillon/mpcsoft.

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

Thanks again for all your help. I realized later that I was not destroying everything, and once I did so, Excel is gone! I would be happy to describe my application for you. I can e-mail you (is it gforseth@acucorp.com ?) if you like, or I can post it here for you.

Thanks again,

Matt Cantillon/mpcsoft.

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

Gforseth had asked for an explanation of the application that I have developed marrying several different technologies, Palm devices, ODBC databases, Excel and COBOL.

My customer is a commercial roofing contractor in Oklahoma who also uses my Construction accounting system, They had developed two things themselves. One was an excellent excel spreadsheet that would take raw data and make the necessary computations for a roofing cost estimate. The second was a set of databases that would keep and maintain the raw data for these cost estimates. This second was done in Filemaker Pro, which has a Palm OS conduit. They had no way to tie the two together, and they also wanted to collect the data on Palm OS (sometimes). In addition, they wanted to ultimately make this a part of their web site so prospective customers (school districts and county governments mostly), could get an idea of the cost of roof replacement. Using an Acu4GL-enabled ACUGT runtime, I was able to read their databases and submit the data into the spreadsheet, which would compute the estimate and produce an excellent print preview. The program allows the user to choose the estimate to be submitted to excel, and then reads all the data for that estimate, submits it to the appropriate cells in excel, which does the number crunching and print preview.

The customer can thus maintain an extensive library of old estimates, which allows them more precision in estimating new jobs, and provides customers a seamless way to estimate their own costs.

I hope this explains the app clearly. I am delighted with what I can do with this development platform.

Matt Cantillon

[Migrated content. Thread originally posted on 30 September 2003]

I am working on a COBOL program that will manipulate a somewhat complex EXCEL spreadsheet. My problem is that I cannot seem to format a MODIFY statement that will change the active sheet. There are 6 sheets in this Excel spreadsheet and I need to move from sheet to sheet filling cells with data. I have been using the excellent EXCEL example that I found on this forum, which has helped alot, but doesn't address this particular issue. In my program I have created the same handles and variables used in the sample. I have tried every combination I can think of, and still it will not compile, so some help would be appreciated. I am sure I am missing some simple concept that will make this all simple.

Thanks!


Matt Cantillon/mpcsoft

Thank you Matt,

this was just what I wanted. I can clearly see the competitive advantage this combination of technologies gives your customer. Great work!

Thanks again for sharing this with us.

Gisle