Highlighted
Absent Member.
Absent Member.
10147 views

[archive] Excel OLE Sample

[Migrated content. Thread originally posted on 19 December 2002]

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. 🙂
0 Likes
19 Replies
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel OLE Sample

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?
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel OLE Sample

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
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel OLE Sample

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 😎
WorkSheet (optional 😎
WorkBook (optional 4)
Module (optional 😎
DialogSheet (optional 😎

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.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel OLE Sample

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).
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel OLE Sample

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:
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel OLE Sample

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.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel OLE Sample

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:)
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel OLE Sample

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???) 🙂
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel OLE Sample

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
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel OLE Sample

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.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Excel OLE Sample

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
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.