Excel Automation WorkSheets.Add method

[anon0000]

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.

[Michael8]

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

[anon0000]

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.

[anon0000]

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.

[Steven_L_Intel1]

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

[anon0000]

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

[Steven_L_Intel1]

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

[anon0000]

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.

[Steven_L_Intel1]

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.