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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,114
Office Version
  1. 2016
Platform
  1. Windows
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 ?

Code:
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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,114
Office Version
  1. 2016
Platform
  1. Windows
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.

Code:
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

New Member
Joined
Sep 25, 2017
Messages
32
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 !
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,114
Office Version
  1. 2016
Platform
  1. Windows
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 ?
 
Last edited:

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,114
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,707
Messages
5,597,678
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top