How to Automate a second instance of Excel via the IDispatch Interface .

[Jaafar Tribak]

Hi all,

I've been reading on COM and how to connect to automation servers and query their Properties and Methods.

As far as I know, this is done via the IDispatch Interface through which the Server application exposes its Properties and Methods.

This way of handling automation is obviously not the VB way but for the purpose of learning how Automation works under the hood i am trying to achieve this in a C like manner .

So far, I have succeded in actually creating the Server (second excel application) and getting a pointer to the IDispatch Interface but i can't seem to find a way to set the Visible Property of the newly created excel instance which, according to the MSDN documentation, is supposed to be done via the GetIDsOfNames and Invoke Methods of the IDispatch Interface.

This is the code I have so far which successfully creates a second invisible instance of Excel. Does anybody know how to set its Visible Property to TRUE through the Idispatch pointer stored in the VarPtr(obj) variable in the code below ?

Private Declare Function IIDFromString Lib "ole32" ( _
ByVal lpszIID As Long, _
iid As Any) As Long
 
Private Declare Function CoCreateInstance Lib "ole32" ( _
rclsid As Any, _
ByVal pUnkOuter As Long, _
ByVal dwClsContext As Long, _
riid As Any, _
ByVal ppv As Long) As Long
 
Private Declare Sub RtlMoveMemory Lib "kernel32" ( _
pDst As Any, _
pSrc As Any, _
ByVal dlen As Long)
 
Private Const CLSID_EXCELAPP As String = _
"{00024500-0000-0000-C000-000000000046}" [COLOR=seagreen]' Excel COM object GUID.[/COLOR]
 
Private Const IID_DISPATCH As String = _
"{00020400-0000-0000-C000-000000000046}" [COLOR=seagreen]'IDispatch Interface GUID.[/COLOR]
 
Private Const IID_NULL As String = _
"{00000000-0000-0000-0000-000000000000}"
 
Private Type GUID
    data1    As Long
    data2    As Integer
    data3    As Integer
    data4(7) As Byte
End Type
 
Private Const CLSCTX_LOCAL_SERVER As Long = &H4
 
Private Const S_OK As Long = &H0
Private Const E_NOINTERFACE As Long = &H80004002
Private Const REGDB_E_CLASSNOTREG As Long = &H80040154
Private Const CLASS_E_NOAGGREGATION As Long = &H80040110
Private Const E_POINTER As Long = &H80004003
 
Sub CreateNewExcelInstance()
 
    Dim classid   As GUID
    Dim iid       As GUID
 
    Dim obj       As Long
    Dim hRes      As Long
 
 
    [COLOR=seagreen]'   CLSID   (BSTR)   to   CLSID   (GUID)[/COLOR]
    hRes = IIDFromString(StrPtr(CLSID_EXCELAPP), classid)
    If hRes <> 0 Then
    Exit Sub
    End If
 
   [COLOR=seagreen]'   IID   (BSTR)   to   IID   (GUID)[/COLOR]
    hRes = IIDFromString(StrPtr(IID_DISPATCH), iid)
    If hRes <> 0 Then
    Exit Sub
    End If
 
 
   [COLOR=seagreen]'   create   a  new   instance   of   Excel[/COLOR]
   [COLOR=seagreen]'   (Set   EXCELAPP   =   New   excel.application[/COLOR]
    hRes = CoCreateInstance(classid, 0, CLSCTX_LOCAL_SERVER, iid, VarPtr(obj))
 
 
    [COLOR=seagreen]'if hRes = S_OK then An instance of the specified object _[/COLOR]
[COLOR=seagreen]   'class was successfully created.[/COLOR]
    
 
    MsgBox hRes = S_OK  [COLOR=seagreen]'Returns TRUE[/COLOR]
                       [COLOR=seagreen]'(a new excel instance was successfully creaed)[/COLOR]
 
End Sub

Any anyone shading some light on this will be much appreciated.

Regards.

 

[Jaafar Tribak]

In case anyone is interested here is the solution . Basically, just needed to get a reference to the IDispatch via the CopyMemory API function in order to access the Methods/Properties of the newly created excel instance.

Option Explicit
 
Private Declare Function IIDFromString Lib "ole32" ( _
ByVal lpszIID As Long, _
iid As Any) As Long
 
Private Declare Function CoCreateInstance Lib "ole32" ( _
rclsid As Any, _
ByVal pUnkOuter As Long, _
ByVal dwClsContext As Long, _
riid As Any, _
ByVal ppv As Long) As Long
 
Private Declare Sub RtlMoveMemory Lib "kernel32" ( _
pDst As Any, _
pSrc As Any, _
ByVal dlen As Long)
 
Private Const CLSID_EXCELAPP As String = _
"{00024500-0000-0000-C000-000000000046}" ' Excel COM object GUID.
 
Private Const IID_DISPATCH As String = _
"{00020400-0000-0000-C000-000000000046}" 'IDispatch Interface GUID.
 
Private Type GUID
    data1    As Long
    data2    As Integer
    data3    As Integer
    data4(7) As Byte
End Type
 
Private Const CLSCTX_LOCAL_SERVER As Long = &H4
 
Private Const S_OK As Long = &H0
 
Private oExcelIDisp As Object
 
Sub CreateNewExcelInstance()
 
    Dim classid   As GUID
    Dim iid       As GUID
 
    Dim obj       As Long
    Dim hRes      As Long
 
 
    '   CLSID   (BSTR)   to   CLSID   (GUID)
    hRes = IIDFromString(StrPtr(CLSID_EXCELAPP), classid)
    If hRes <> 0 Then
    Exit Sub
    End If
 
   '   IID   (BSTR)   to   IID   (GUID)
    hRes = IIDFromString(StrPtr(IID_DISPATCH), iid)
    If hRes <> 0 Then
    Exit Sub
    End If
 
 
   '   get a pointer to the IDispatch of a new instance of   Excel
   '   = (Set   EXCELAPP   =   New   excel.application
    hRes = CoCreateInstance(classid, 0, CLSCTX_LOCAL_SERVER, iid, VarPtr(obj))
 
 
    If hRes = S_OK Then

        'get a reference to the new excel application.
        RtlMoveMemory oExcelIDisp, obj, 4
        'display the new application.
        oExcelIDisp.Visible = True
        oExcelIDisp.Workbooks.Add
        'set oExcelIDisp to Nothing.
        RtlMoveMemory oExcelIDisp, 0, 4

    End If
    
 
End Sub

Regards.

 

[hymced]

very interesting Jaafar, thanks for sharing ! but I am still wondering if it is possible to call the Invoke method of the IDispatch interface via VBA, the Object Browser shows it is not natively exposed... as anything else !

 

[Jaafar Tribak]

very interesting Jaafar, thanks for sharing ! but I am still wondering if it is possible to call the Invoke method of the IDispatch interface via VBA, the Object Browser shows it is not natively exposed... as anything else !

Hi,

Not sure what you are trying to do but can you not invoke the methods and Properties of excel once you get a pointer via CoCreateInstance as shown in the above code ?

Or are you refering to calling Methods and Properies via the COM object VTable ?

 

[hymced]

I am not really trying to do anything particular in fact !

I am not referring to methods and properties from the Excel VBA Object Model either, but really about this method : IDispatch::Invoke

Is it possible to call it from VBA ?

https://msdn.microsoft.com/en-us/li...479(v=vs.85).aspx?f=255&MSPPError=-2147217396

 

[Jaafar Tribak]

I am not really trying to do anything particular in fact !

I am not referring to methods and properties from the Excel VBA Object Model either, but really about this method : IDispatch::Invoke

Is it possible to call it from VBA ?

https://msdn.microsoft.com/en-us/li...479(v=vs.85).aspx?f=255&MSPPError=-2147217396

This is a difficult area in VB(A) .. I guess you are referring to whether one can call Methods and Props of some COM objects/Interfaces that are not exposed to VB the standard way.

If that's the case, you may want to take a look here where I asked a similar question and managed to query the excel Interface the COM way... You should be able to apply similar approach to other object models.