Software Archive
Read-only legacy content
17061 Discussions

COM (Excel) Interfacing

Intel_C_Intel
Employee
454 Views
Hello,
Armed with the sample program "Autodice" I began working on an application to build an EXCEL workbook. I've been able to successfully call several automation interface routines that include "variant" arguments either using direct examples or by itteratively finding a working combination. I've finally gotten stuck on how to pass the correct information for the "After" argument in the "Worksheets_Copy($OBJECT, Before, After, $STATUS)" I'm assuming some combination of Varaint%VT=VT_x and Variant%VU%x_VAL=something will work, but I haven't stumbled upon it. Does anyone have a working example of this call, or suggestions? Also, where can I get more information on what variant types and/or unions are utilized by a particular automation interface routine? The module created by the Wizard only has sketchy information.
Thanks in advance for any help,
Jack M. O'Leary
0 Kudos
6 Replies
canaimasoft
Beginner
453 Views
Jack,

I suggest you check our f90VB User Manual (http://www.canaimasoft.com/f90VB/OnlineManuals). It has a complete introduction to OLE/COM data types from the point of view of Fortran programmers. Obviously the manual is geared to f90VB users (which by the way would make life easier for the kind of things you are trying to do), nevertheless there are chapters explaining BStrings, Safe Arrays and Variants.

best regards,

Marco A. Garcia
Canaima Software
P.O. Box 13162
La Jolla, CA. 92039
U.S.A.
e-mail:mgarcia@canaimasoft.com
Tel/Fax: (619) 233-6831
http://www.canaimasoft.com
Developers of f90SQL the Database Connectivity Solution for Fortran, and f90VB the Library for Fortran-OLE Automation and Fortran-VB Programming
0 Kudos
Intel_C_Intel
Employee
453 Views
Hi Jack,

The After argument should be an IDispatch pointer to the Worksheet object representing your "After" sheet.
...%VT=VT_DISPATCH and ...%VU%PTR_VAL=objTargetWS.
I threw together an example here.

As for information on what variant types are utilized by automation interfaces, I always first look at the VBA documentation and mentally do the translation. VBA hides the underlying variant type but it can easily surmised. But I guess, "Objects" being dispatch pointers is a tricky one. The VBA way is usually quite a few lines shorter (courtesy of the VB runtime).

If you're doing alot of OLE Automation, some of the pain of creating/using variants can be encapsulated in a utility library. Using f90vb's VariantCreate routine in my sample would reduce the code for variant creation from 11 lines to 3 lines and also eliminate a few temporary variables.

hth,
John
0 Kudos
Intel_C_Intel
Employee
453 Views
This reply is just a big thank-you to John Termine. Your response was most helpful. I'm programming away again.
Jack
0 Kudos
Intel_C_Intel
Employee
453 Views
Just so you are not caught unaware, Excel 97 and Excel 2000 suffer from a serious bug(s) when it comes to programmatically copying or moving worksheets. I believe the bugs are in Excel's handling of something called a CodeName for a sheet. I have fought this bug in my excel VBA macros, and it's a nasty one. Excel crashes trying to access a null memory address.

It always takes multiple calls to a routine copying or moving sheets, but after enough calls (as few as 6 or 7), crash city.

Brian Murphy
Austin, Texas
0 Kudos
Intel_C_Intel
Employee
453 Views
Jack,

A big, 'you're welcome'. :-) Glad it helped.

FWIW, below are some links to MS KB articles that reference the problem that Brian mentioned.

cheers,
John
GO Ravens!!!!

Excel for Windows
Q177634 - XL97: Copy Method of Sheets Object Causes Invalid Page Fault
Q172500 - XL97: Unable to Open File After You Change Code Name of Sheet

Excel for Mac
Q178503 - XL: Unable to Open File After You Change Code Name of Sheet
0 Kudos
durisinm
Novice
453 Views
This post isn't really about Fortran per se, but rather about the PDF file Marco referenced in his January 24 post in this thread.

The file will print 178 pages. To save paper, I like to print 4-up to get four little pages on one sheet of paper. I find that Acrobat Reader will print one page per sheet unless I mark the "Print as image" checkbox in the print driver's Print dialog (under Windows 98 and 2000). I'll get four pages per sheet then, but the quality of the print will be poor; the characters are not sharp and clear.

I want to print out and keep the file for reference, but does anyone know how to make Acrobat Reader print 4-up without sacrificing the print quality?

Mike Durisin
0 Kudos
Reply