Excel Automation WorkSheets.Add method

Excel Automation WorkSheets.Add method

I am having difficulties understanding how the COM automation fuction FUNCTION Worksheets_Add($OBJECT, ...) works. I have a test code that opens an Excel instance and then creates a new workbook. To add a new worksheet to this workbook, I use the following:

WorkSheet = Worksheets_Add(WorkBooks, $STATUS=status)
call $WorkSheet_Activate(WorkSheet, $STATUS=status)
WorkSheet = $Workbook_GetActiveSheet(WorkBook, $STATUS=status)
call $WorkSheet_SetName(WorkSheet, 'SheetName', $STATUS=status)

When I try "WorkSheet = WorkSheets_Add(WorkBook, $STATUS=status)", the function returns an error. It would make sense to me to add a worksheet to the current WorkBook and not the WorkBooks collection.

Also, when I try to add a second sheet to the WorkBook, it always overwrites the first, no matter how I call the WorkSheets_Add function. I have tried using the 'After' argument assigned to the IDispatch pointer to the previous worksheet, but this does not seem to work. I think the error relates to the above code and the first and second worksheets are not members of the same workbook. Also note, I have created the WorkBooks collection but not the WorkSheets collection at this point. ??

I have made a surprising amount of progress in only a couple of hours at my first attempt at automating Excel through COM with IVF-10, but the process has been frusturating from a seemingly lack of documentation / information on the routines the Module Wizard creates. The F90VB documentation discussed in other posts has some high-level information but cannot help at the detailed level since most of these routines are hidden from the user.

Thanks in advance for any assistance or thoughts.

9 posts / 0 new
Last post
For more complete information about compiler optimizations, see our Optimization Notice.

Hello:

In the past, when using the Worksheets_Add function, I've passed the "worksheets" object as the first argument. Like you mentioned, there seems to be very little documentation about this, so I can't answer why "Workbooks" (somewhat) worked for you, but not "workbook". All I can suggest is to try passing "worksheets", as this has worked for me.

I hope that's helpful.

Michael

As I expected, the missing WorkSheets collection was the culprit. The following pseudocode outlines the process:

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
call COMInitialize()
call COMCreateObject()

WorkBooks = $Application_GetWorkbooks()
WorkBook = Workbooks_Add()
WorkSheets = $Application_GetWorksheets() <--- new code to generate WorkSheets collection

! first worksheet
WorkSheet = Worksheets_Add()
call $WorkSheet_Activate()
WorkSheet = $Workbook_GetActiveSheet()
call $WorkSheet_SetName()

! populate cells with data...

! add second worksheet
WorkSheet = Worksheets_Add(WorkSheets, After="Pointer to first worksheet", ...)
call $WorkSheet_Activate()
WorkSheet = $Workbook_GetActiveSheet()
call $WorkSheet_SetName()

! populate cells with data...

! save & exit
call $Workbook_SaveAs(WorkBook, "file name as BSTR", ...)
call $Application_Quit()

! release COM objects with ComReleaseObject()
call COMUninitialize()
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

While running some trial-and-error tests, it appeared that if a new worksheet is created under the Workbooks collection, the new sheet is created under a new workbook, (e.g. Book1) and is not a part of the intended WorkBook.

With the test code functioning as I intended/expected, I tried to add the methods to a larger project by adding the appropriate code and regenerating the COM automation interface through the module wizard. The compiler returns an error in a class ID generated by the module wizard:

TYPE (GUID), PARAMETER :: CLSID_QueryTable = &
GUID(#59191DA1, #EA47, #11CE, & <---
...

The errors are all for the indicated line above
1) Syntax error, found IDENTIFIER 'DA1' when expecting one of: .EQV. .NEQV. .XOR. .OR. .AND. .LT. < .LE. <= .EQ. == .NE. /= .GT. > ...
2) This symbol must be a defined parameter or an argument of an inquiry function that evaluates to a compile-time constant. [DA1]
3) This name does not have a type, and must have an explicit type. [DA1]

I have tried to use the module generated for the test code in the larger project with the same results (because the CLSID_QueryTable value is identical). If I comment the line out, I get an error about the generic routine COMCREATEOBJECT not having a specific routine. I do not think there error is directly caused by the CLSID becuase it works just fine in the test code.

Please attach the actual generated .f90 (ZIP it if it is large.)

Steve - Intel Developer Support

I rebuilt identical modules for the test code and larger project to provide minimal functionality, with the same results.

Attachments: 

AttachmentSize
Download EXCEL9.f90870.5 KB

The source compiles fine for me with 11.1 Update 4. Would you please attach the buildlog.htm showing the compile error?

Steve - Intel Developer Support

I am using 10.1.4160.2005 with VS2005. One difference between the projects is /fpp, if I disable FPP, the synatx error disappears and I am left the error:

Error: There is no matching specific subroutine for this generic subroutine call. [COMCREATEOBJECT]

I can get around this by calling COMCreateObjectByProgID without /fpp to compile and debug. Also, since the generic interface to COMCreateObject seemed to disappear, I checked with other installations of the same IVF build and the IFCOM module was different. Does the use of COM Automation alter the include IFCOM or did I inadvertantly edit it?

Any thoughts are greatly appreciated.

Ok - I can reproduce the error in 10.1 with /fpp. 11.1 is fine. I can imagine that the preprocessor got confused by the # syntax for the constants.

The error you got indicates that the generic interface is there, but you have a call that doesn't match any of the signatures. IFCOM is installed as part of the compiler and does not get altered once installed.

Steve - Intel Developer Support

Leave a Comment

Please sign in to add a comment. Not a member? Join today