Iterating through COM objects and selecting appropriate one

MrGeltS

New Member
Joined
Oct 9, 2015
Messages
2
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.:

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

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

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,179
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum!

WMI is one way. Here is another.
Code:
' 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

New Member
Joined
Oct 9, 2015
Messages
2
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
Rich (BB code):
 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?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,806
Messages
5,655,413
Members
418,198
Latest member
jad1984

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