Excel Automation Interface Function Workbooks_Open error status

[anon0000]

I have a test code that uses Automation Interfaces for Excel9 and the WorkBooks_Open function returns a non-zero status after initializing the COM server, creating the COM object for Excel9 and establing a pointer to an existing workbook through $Application_GetWorkBooks.

I had the same issue with the AutoDice.f90 sample until I rebuilt the Excel COM module. The only arguments to the function are the pointer to application instance and the path to the *.xls workbook (which is a char*, so there are no BSTR issues). I know the path is correct, but the AUTOINVOKE($OBJECT, 682, invokeargs) call in the WorkBooks_Open function always returns status = -2147352573. Without any documentation, I am having an issue understanding the error. Any help would be greatly appreciated.

[Paul_Curtis]

Quoting - anon0000

I have a test code that uses Automation Interfaces for Excel9 and the WorkBooks_Open function returns a non-zero status after initializing the COM server, creating the COM object for Excel9 and establing a pointer to an existing workbook through $Application_GetWorkBooks.

I had the same issue with the AutoDice.f90 sample until I rebuilt the Excel COM module. The only arguments to the function are the pointer to application instance and the path to the *.xls workbook (which is a char*, so there are no BSTR issues). I know the path is correct, but the AUTOINVOKE($OBJECT, 682, invokeargs) call in the WorkBooks_Open function always returns status = -2147352573. Without any documentation, I am having an issue understanding the error. Any help would be greatly appreciated.

Your description sounds ok; here is what works for me (tbuf contains a path, and is null-terminated):

[cpp]! Object Pointers
INTEGER(INT_PTR_KIND()) excelapp
INTEGER(INT_PTR_KIND()) workbooks
INTEGER(INT_PTR_KIND()) workbook
INTEGER(INT_PTR_KIND()) worksheet


! Create an Excel object
CALL COMCREATEOBJECT ("Excel.Application", excelapp, status)
IF (excelapp == 0) RETURN
CALL $Application_SetVisible (excelapp, TRUE2)
CALL $Application_SetScreenUpdating (excelapp, FALSE2)

! Get the WORKBOOKS object
workbooks = $Application_GetWorkbooks (excelapp, $STATUS = status)
IF (status /= 0) RETURN

! Open a new spreadsheet from the template file
workbook = Workbooks_Open (workbooks, tbuf, $STATUS = status)
IF (status /= 0) RETURN

! Get the worksheet
worksheet = $Workbook_GetActiveSheet (workbook, status)
IF (status /= 0) RETURN 
[/cpp]

[dannycat]

You are right in saying that there is a lack of documentation here, particularly for FORTRAN users who generally have to do lot more research (sometimes resorting to trial and error) to utilise COM objects/automation features that are relatively straightforward for VB and VC developers. I believe there are opportunities for a willing author to take up the baton fromNorman Lawrence (Visual Fortran - A guide to Creating Windows Applications)and provide more insight into the more advanced aspects of programming using FORTRAN.

I digress, back to your issue - I remember having a similar problem a couple of years ago when updating to a later version of Excel. I finally fixed it by replacing the Workbooks_Open function name with Workbooks__Open. You should have this interface in the Excel interfaces generated by Module Wizard although I've listed it below.

!Workbooks__Open return type is POINTER(p, INTEGER(INT_PTR_KIND()))
INTEGER(INT_PTR_KIND()) FUNCTION Workbooks__Open($OBJECT, Filename, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, $STATUS)
!DEC$ ATTRIBUTES DLLEXPORT :: Workbooks__Open
IMPLICIT NONE

INTEGER(INT_PTR_KIND()), INTENT(IN) :: $OBJECT ! Object Pointer
!DEC$ ATTRIBUTES VALUE :: $OBJECT
CHARACTER(LEN=*), INTENT(IN) :: Filename ! BSTR
!DEC$ ATTRIBUTES REFERENCE :: Filename
TYPE (VARIANT), INTENT(IN), OPTIONAL :: UpdateLinks
!DEC$ ATTRIBUTES REFERENCE :: UpdateLinks
TYPE (VARIANT), INTENT(IN), OPTIONAL :: ReadOnly
!DEC$ ATTRIBUTES REFERENCE :: ReadOnly
TYPE (VARIANT), INTENT(IN), OPTIONAL :: Format
!DEC$ ATTRIBUTES REFERENCE :: Format
TYPE (VARIANT), INTENT(IN), OPTIONAL :: Password
!DEC$ ATTRIBUTES REFERENCE :: Password
TYPE (VARIANT), INTENT(IN), OPTIONAL :: WriteResPassword
!DEC$ ATTRIBUTES REFERENCE :: WriteResPassword
TYPE (VARIANT), INTENT(IN), OPTIONAL :: IgnoreReadOnlyRecommended
!DEC$ ATTRIBUTES REFERENCE :: IgnoreReadOnlyRecommended
TYPE (VARIANT), INTENT(IN), OPTIONAL :: Origin
!DEC$ ATTRIBUTES REFERENCE :: Origin
TYPE (VARIANT), INTENT(IN), OPTIONAL :: Delimiter
!DEC$ ATTRIBUTES REFERENCE :: Delimiter
TYPE (VARIANT), INTENT(IN), OPTIONAL :: Editable
!DEC$ ATTRIBUTES REFERENCE :: Editable
TYPE (VARIANT), INTENT(IN), OPTIONAL :: Notify
!DEC$ ATTRIBUTES REFERENCE :: Notify
TYPE (VARIANT), INTENT(IN), OPTIONAL :: Converter
!DEC$ ATTRIBUTES REFERENCE :: Converter
TYPE (VARIANT), INTENT(IN), OPTIONAL :: AddToMru
!DEC$ ATTRIBUTES REFERENCE :: AddToMru
INTEGER(4), INTENT(OUT), OPTIONAL :: $STATUS ! Method status
!DEC$ ATTRIBUTES REFERENCE :: $STATUS
INTEGER(INT_PTR_KIND()), VOLATILE :: $RETURN
INTEGER(4) $STATUS
INTEGER(INT_PTR_KIND()) invokeargs
invokeargs = AUTOALLOCATEINVOKEARGS()
CALL AUTOADDARG(invokeargs, '$RETURN', $RETURN, AUTO_ARG_OUT, VT_DISPATCH)
CALL AUTOADDARG(invokeargs, '$ARG1', Filename, AUTO_ARG_IN, VT_BSTR)
IF (PRESENT(UpdateLinks)) CALL AUTOADDARG(invokeargs, '$ARG2', UpdateLinks, AUTO_ARG_IN)
IF (PRESENT(ReadOnly)) CALL AUTOADDARG(invokeargs, '$ARG3', ReadOnly, AUTO_ARG_IN)
IF (PRESENT(Format)) CALL AUTOADDARG(invokeargs, '$ARG4', Format, AUTO_ARG_IN)
IF (PRESENT(Password)) CALL AUTOADDARG(invokeargs, '$ARG5', Password, AUTO_ARG_IN)
IF (PRESENT(WriteResPassword)) CALL AUTOADDARG(invokeargs, '$ARG6', WriteResPassword, AUTO_ARG_IN)
IF (PRESENT(IgnoreReadOnlyRecommended)) CALL AUTOADDARG(invokeargs, '$ARG7', IgnoreReadOnlyRecommended, AUTO_ARG_IN)
IF (PRESENT(Origin)) CALL AUTOADDARG(invokeargs, '$ARG8', Origin, AUTO_ARG_IN)
IF (PRESENT(Delimiter)) CALL AUTOADDARG(invokeargs, '$ARG9', Delimiter, AUTO_ARG_IN)
IF (PRESENT(Editable)) CALL AUTOADDARG(invokeargs, '$ARG10', Editable, AUTO_ARG_IN)
IF (PRESENT(Notify)) CALL AUTOADDARG(invokeargs, '$ARG11', Notify, AUTO_ARG_IN)
IF (PRESENT(Converter)) CALL AUTOADDARG(invokeargs, '$ARG12', Converter, AUTO_ARG_IN)
IF (PRESENT(AddToMru)) CALL AUTOADDARG(invokeargs, '$ARG13', AddToMru, AUTO_ARG_IN)
$STATUS = AUTOINVOKE($OBJECT, 682, invokeargs)
IF (PRESENT($STATUS)) $STATUS = $STATUS
Workbooks__Open = $RETURN
CALL AUTODEALLOCATEINVOKEARGS (invokeargs)
END FUNCTION Workbooks__Open


I hope this helps.