Excel OLE Sample

[DanM]

There is a Word OLE sample in the Acucobol code sample section of the website, but did not see an Excel OLE sample, so I thought I'd submit one. A zip file is attached with source and object. You will of course need MS Office and/or Excel installed. If you want to modify source and recompile, you will need to use the AXDEFGEN utility to generate your own EXCEL.DEF copy file(too large to fit into the attached zip file). Choose the Microsoft Excel Object Library under the Library tab. Please read the notes at top of the source code.
Hope this sample is helpful. :)

[larryt]

Originally posted by DanM
There is a Word OLE sample in the Acucobol code sample section of the website, but did not see an Excel OLE sample, so I thought I'd submit one. A zip file is attached with source and object. You will of course need MS Office and/or Excel installed. If you want to modify source and recompile, you will need to use the AXDEFGEN utility to generate your own EXCEL.DEF copy file(too large to fit into the attached zip file). Choose the Microsoft Excel Object Library under the Library tab. Please read the notes at top of the source code.
Hope this sample is helpful. :) : :confused:

Thanks for the sample. I've generated excel.def and compiled excel-ole.cbl under Win98 and got compile error "Too few parameters: 7 required, 1 found". How to change the improper the number of optional paremeters?

[DanM]

Larry,
The notes at the top of the source mention this. In the Excel.def file, locate the WorkBook class(search for CLASS @Workbook). Then, under this class, look for the SaveAs method(not _SaveAs). On the last last line of this method you'll see the word "optional" and a number. Change this number to the number of paramaters listed minus 1. For instance, if optional says 5 and you see 12 paramters listed then change optional to 11.

Example fix for Office XP (I commented out the optional 5 and inserted optional 11):
* SaveAs
METHOD, 1925, @SaveAs,
"VARIANT" @Filename, TYPE 12,
"VARIANT" @FileFormat, TYPE 12,
"VARIANT" @Password, TYPE 12,
"VARIANT" @WriteResPassword, TYPE 12,
"VARIANT" @ReadOnlyRecommended, TYPE 12,
"VARIANT" @CreateBackup, TYPE 12,
"XlSaveAsAccessMode" @AccessMode, TYPE 3,
"VARIANT" @ConflictResolution, TYPE 12,
"VARIANT" @AddToMru, TYPE 12,
"VARIANT" @TextCodepage, TYPE 12,
"VARIANT" @TextVisualLayout, TYPE 12,
"VARIANT" @Local, TYPE 12
OPTIONAL 11
* OPTIONAL 5

[gforseth]

You should be careful modifying the content of the generated definition file, this in particular because what you believe is an error might not be so, but just a different implementation.
Take for instance the method SaveAs, which in the excel.def file actually exists for these various classes:

Chart (optional 8)
WorkSheet (optional 8)
WorkBook (optional 4)
Module (optional 8)
DialogSheet (optional 8)

Hence, depending on the different purpose, they have different requirements. Add to this, that some (like Chart, Worksheet and Workbook) have both an implementation class (typically same name but prefixed with '_', e.g. Workbook and _Workbook) and a definition class and you may not be correcting the right place. Hence, like in your case, where the class SaveAs is inherited from is really WorkBook, changing the .def file will get it through the COBOL compiler, ut in the second instance it may cause an exception when the class itself is being executed.

Note that I am not saying DanM's suggestion has to be wrong, just providing some background information.

Finally another good thing to know in regards of methods with optional parameters, is the naming of them. Like in this case, Assuming that you have an instance name myWorkbook, executing the SaveAs could be done like this:

MODIFY myWorkBook SaveAs(
BY NAME Filename "myfile.xls",
BY NAME FileFormat xlExcel7,
BY NAME Password "",
BY NAME WriteResPassword 0,
BY NAME ReadOnlyRecommended 1,
BY NAME CreateBackup 0).

It is particularly good practise to use naming when there are a huge number of optional parameters that appear similar, to identify which you are addressing.

Also note that optional parameters come at the end, thus, if you have 8 optional parameters out of 10, the first two are mandatory.

[DanM]

Gisle,
This was a workaround at the time for a bug in axdefgen, which I hope will be fixed in 6.0.1. True, you could pass the required paramaters instead and avoid changing the excel.def entirely.
You make a very good point about staying away from editing .def files, and I also certainly do not promote changing the .def file every time there is a problem. But in this case, I felt it was a very simple change and it worked well in making the SaveAs simpler to use as was intended by all the optional paramaters.
I made the change you suggested to the excel-ole.cbl sample, this way the optional paramater compiler error issue can be more easily avoided. Thanks for the tip. Much appreciated!

In the sample I changed this statement:
MODIFY EX-WORKBOOK @SaveAs(WKBK-NAME).

To this statement:
MODIFY EX-WORKBOOK @SaveAs(
BY NAME @Filename WKBK-NAME,
BY NAME @FileFormat @xlNormal,
BY NAME @Password NULL,
BY NAME @WriteResPassword NULL,
BY NAME @ReadOnlyRecommended 0,
BY NAME @CreateBackup 0
BY NAME @AccessMode @xlNoChange).

[larryt]

Thanks both for explanations.
This is the first time I am trying to use Excel and probably I am missing something, i.e. I've changed the demo program excel-ole.cbl as sugested and it compiled (ccbl32 ver.5.1.0.3) OK with no errors.
When I run it (wrun32 ver.5.1.0.3) it did nothing. I debugged it, it actually was executing every line but no real effect. I manualy created an Excel file and hardcoded the value for WKBK-NAME and uncommented lines for "Open existing sheet logic". Same, no effect at all. Your excel-ole.acu object works fine, asking first for the name then invokes the Excel, updating the sheet. I have Office 97 on Win98.
Is the posted excel-ole.cbl source coresponding to the posted excel-ole.acu object ?
Thanks again
:confused:

[DanM]

Yes, the object and source match. Did you download the latest version that I posted this morning? I made a change implementing Gisles' suggestion.
Unfortunately, I don't have a pc with office 97 to test this.

[larryt]

Yes Dan I've downloaded your latest excel-ole.cbl.
In the mean time I've discovered that my by-default compile switch "-n" was causing "the problem".
When compiled without it, the object runs fine.
Now let me play a little bit more with this Acu-Excel capabilities.
Best regards:)

[gforseth]

If you have an older version of Excel, than what has been used for the examples. You should generate the definition file on your computer rather than using the one from here, to ensure you have a def file that matches your installed version.
The definition file should normally fit fine to future versions, but a definition file for a future version may not necessarily fit to an older version.

Another note about the '@' prefix. Just in case you don't know, this prefix is optional and is intended to be used in cases where you have a clash with COBOL verbs. For instance, a common clash is the word FONT. Then if you modify a control, and want to make sure that the action you take is done on the control you prefix it with the '@'. If the property or method you are accessing do not cause a clash, you can omitt the prefix entirely. Which certainly will improve readability.

Thanks to DanM, I had forgotten about that bug (really???) :-)

[DanM]

I like to always use the '@' symbol for the reason you mentioned, name-clashing. Better safe than sorry, in case more reserved words or .def constants, etc, are added by acucuorp later. And also, it helps my readability because I can easily scan my source code and pickout the .def members that appear throughout. Great for searches in my text editor too. The @ is a nice identifier for me. :)
The bug I mentioned has to do with an improper number of optional paramaters being generated by axdefgen when one of the paramaters in the list is not a variant type. I worked with acucorp support on this and they confirmed it. For some reason though, I can't find it in the online knowledge base. I guess it was not added. Would be nice if I could access my customer support history on the acucorp support site.;)

MSDN office xp developer doc for WorkBook SaveAs method here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/html/xlmthsaveas.asp

[DanM]

Gisle, I found the email on this:


Hi Dan,

Development has responded to the subject ID by issuing ECN2444 which modifies the AxDefGen dll, and will be available in version 6.01, the first maintenance release of 6.0.

They also suggest to do as you did to work around this problem; manually edit the copy book, count the number of parameters and increase the number following the word OPTIONAL. If the word OPTIONAL is missing, insert a new line after the last parameter definition and type the word OPTIONAL followed by a sufficiently large number.

Thanks again for your report,

Mark Smith
AcuCorp Technical Support
Please send your support issues to support@acucorp.com to ensure proper handling.

[Peter_Wan]

There is a Word OLE sample in the Acucobol code sample section of the website, but did not see an Excel OLE sample, so I thought I'd submit one. A zip file is attached with source and object. You will of course need MS Office and/or Excel installed. If you want to modify source and recompile, you will need to use the AXDEFGEN utility to generate your own EXCEL.DEF copy file(too large to fit into the attached zip file). Choose the Microsoft Excel Object Library under the Library tab. Please read the notes at top of the source code.
Hope this sample is helpful. :)

Thanks Dan for the very useful sample pgm.. It's exaclty the jump start I needed to know how to Interop between Acucobol & excel...

Thanks for keeping the post up this long.. 6 years and it still has some meaning to some programmer out there!!

Cheers

[blacky]

Hi Dan,

Your samples have been extremely useful - so thanks for doing them. :D

One thing I can't get working though is adding a new worksheet to the end of the workbook.

Here's what I've tried so far:-
This inserts a new worksheet at the start of the workbook:-MODIFY EX-WORKBOOK @Worksheets::@Add()This crashes Excel:-MODIFY EX-WORKBOOK @Worksheets::@Add(,3,,)
I've even tried passing the number of existing sheets as a variant created by C$SETVARIANT - didn't help.

Can you offer some guidance please?

Thanks,

Ian

[Frosti]

i didn't tested it, but when i create a macro i use
WorkBook.Sheets.AddAfter...


when i search in the excel def-file i didn't find AddAfter so i used the "ActiveX DEF Utility" to find out whats possible with "Sheets".

And there i get this sample:


* This program is generated by AxDefTool.
* This is an example for the syntax of ActiveX.
* For more details, read the specific ActiveX Manual.

special-names.
copy "S:\MUFFROHR\ACU\COPY\excel.def".
.

working-storage section.

* Attention: The field definition may be different.


77 H-Sheets handle of @Sheets.

* @Before: VARIANT
77 W-Var-Before usage handle.
* @After: VARIANT
77 W-Var-After usage handle.
* @Count: VARIANT
77 W-Var-Count usage handle.
* @Type: VARIANT
77 W-Var-Type usage handle.

* returning: IDispatch*
77 W-Var-IDispatch handle.

procedure division.
main.

*Create the ActiveX
create @Sheets
license-key " "
handle in H-Sheets
.

*Setup for variables
call "C$SETVARIANT" using "A", W-Var-Before
call "C$SETVARIANT" using "A", W-Var-After
call "C$SETVARIANT" using "A", W-Var-Count
call "C$SETVARIANT" using "A", W-Var-Type

*Code for Method
modify H-Sheets @Add (
* by name Before W-Var-Before
* by name After W-Var-After
* by name Count W-Var-Count
* by name Type W-Var-Type
)
Returning W-Var-IDispatch
.


hope this helps a little bit :)

[gforseth]

The Add method of the class WorkSheets has 4 parameters, for which case all of them are optional.

This means that unless you use them all, you have to identify them by name when you use them.

Also, in ACUCOBOL-GT optional parameters are left out by omitting them, not by leaving an empty slot in a row of comma's.

Here is an example of using the Add command:

IDENTIFICATION DIVISION.
PROGRAM-ID. ExcelAddSheet.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SPECIAL-NAMES.
COPY "MSEXCEL.def".
.
DATA DIVISION.
WORKING-STORAGE SECTION.
77 olExcel HANDLE OF APPLICATION.
77 olWrkBk HANDLE OF WORKBOOK.
PROCEDURE DIVISION.
Main.
CREATE Application OF Excel
HANDLE IN olExcel.
MODIFY olExcel @Visible = 1.
MODIFY olExcel Workbooks::Add()
GIVING olWrkBk.
*Add 3 worksheets
MODIFY olWrkBk Worksheets::Add(BY NAME count 3).
DESTROY olWrkSh.
MODIFY olWrkBk @Close(BY NAME SaveChanges 0).
DESTROY olWrkBk.
MODIFY olExcel Quit().
DESTROY olExcel.
GOBACK.

[blacky]

Thanks for your replies.

I tried this:- INQUIRE EX-WORKBOOK @Worksheets::Count()
IN WS01-NUM-WORKSHEETS.
MODIFY EX-WORKBOOK
@Worksheets::@Add(BY NAME after WS01-NUM-WORKSHEETS)
GIVING EX-WORKSHEET.But it crashed by program as well. Since the number of worksheets will continually grow, how can I add at worksheet to the end of the workbook?

Thanks,

Ian

[Frosti]

i looked at it and played a bit with the code in my excel test-source :)

Sample from my WS:

01 excel-objects.
03 xls-app handle of application of excel.
03 xls-book handle of workbook of excel.
03 xls-sheets handle of worksheets of excel.
03 xls-sheet handle of worksheet of excel.
03 xls-range handle of range of excel.
03 inq-range handle of range of excel.


the trick is:
you must inquire the sheet after you will insert the new table.

for example inquire sheet number 3:
inquire xls-sheets ITEM(3) xls-sheet.

yet you can insert after sheet 3 a new table:
modify xls-app SHEETS::@ADD(BY NAME after xls-sheet).


and sorry for my bad english :)

[gforseth]

Good work Frosti!

[blacky]

Thanks Frosti! That is exactly what I needed. Thanks also to Gisle for pointing out the "BY NAME" syntax.

It works for me now. :D