Iterating through COM objects and selecting appropriate one

[MrGeltS]

Hello everyone. First time posting here! Thanks in advance for your help.

A colleague of mine uses a tool from a company called MicroHedge (the tool is called Valdi Risk Analytics), and queries data from this tool from an instance of Excel.

I am assisting him in trying to find out how to access several instances of MicroHedge, and aggregate data through the various instances in one of several instances of Excel.

I'm trying out some code in VBA to help connect to this tool. After installing the program from MicroHedge, and setting the appropriate VBA references, the code below allows me to access the first instance of Microhedge through VBA.:

Sub MicroHedgeTest()
 
Dim mhPos As MHPosition
Dim month As MHMonth
Dim mhApp As MHLib.MicroHedge

Set mhApp = GetObject(, "microhedge.application")
Set Book = mhApp.GetBook("")
If Book.Count > 0 Then
     'Do something based on the book.
End If

However, I would like to iterate through each open instance of MicroHedge. Using the GetObject(...) method appears to only choose the first instance.

The generic question for my issue is, how would one iterate through a list of COM objects, select the appropriate object, then assign a variable of a specific class to access the object?

I can iterate through the different COM objects open, and find multiple instances of MicroHedge, which are indicated by the different colItems with name "MH4.exe" using the following code:

Dim mhPos As MHPosition
Dim month As MHMonth
Dim mhApp As MHLib.MicroHedge


Dim strComputer As String
strComputer = "."


Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Process", , 48)


For Each objItem In colItems
    
    If objItem.Name = "MH4.exe" Then
        Set mhApp = objItem
        Set Book = mhApp.GetBook("")
        If Book.Count > 0 Then
            'Do something based on the book.
        End If
        
    End If


Next

This code breaks at the line:

Set mhApp = objItem

where I get an error of "Run-time error '13': Type mismatch".

I hope I am only off by setting mhApp correctly. However, I'd surely appreciate any help in getting this to work correctly.

 

[Kenneth Hobson]

Welcome to the forum!

WMI is one way. Here is another.

' http://www.excelforum.com/excel-programming/799090-determine-number-of-instances-of-excel-running.html
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function GetWindow Lib "user32" ( _
ByVal hwnd As Long, ByVal wCmd As Long) As Long

Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" ( _
ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long

Private Const GW_HWNDFIRST = 0
Private Const GW_HWNDNEXT = 2

Function ExcelCount() As Long
  'http://www.eggheadcafe.com/microsoft/Excel-Programming/31869454/determine-instances-of-excel-open.aspx
  Dim hWin As Long
  Dim nXLinsts As Long
  Dim sBuff As String '* 7
  Const CXL As String = "XLMAIN"
  
  hWin = FindWindow(CXL, vbNullString) ' normally incl app.caption
  
  hWin = GetWindow(hWin, GW_HWNDFIRST)
  
  sBuff = Space(7)
  Do
    hWin = GetWindow(hWin, GW_HWNDNEXT)
    GetClassName hWin, sBuff, 7
    If Left$(UCase$(sBuff), 6) = CXL Then nXLinsts = nXLinsts + 1
  Loop Until hWin = 0
  
  ExcelCount = nXLinsts
End Function

Sub Test_ExcelCount()
  MsgBox ExcelCount
End Sub

 

[MrGeltS]

Hi Kenneth,

Thank you for your reply. Sorry for the delay in responding, but I was away from my work computer all weekend. I don't have much experience with dealing with COM objects in my VBA programming, so please excuse me if I sound too much like a newbie..

I am still missing something here. I've run the code that you've provided. Using the GetClassName Function, I don't actually see any applications that have a class of "mircrohedge.application", which is the class name passed through to the GetObject function used in my initial email, for which I am able to set

 Set mhApp = GetObject(, "microhedge.application") 

.
https://msdn.microsoft.com/en-us/library/e9waz863(v=vs.90).aspx

GetClassName does appear to return a class name from Microhedge objects, as GetClassName finds two objects with class names: "MH:MAIN_CLASS". But, I am still not sure how to access those objects so that I can control them. (Instead of just counting the instances.) I see the variable hWin has very different values. Would I be able to access those objects using that long?