Solved

VB.NEt: Getting list of all Open Access applications

Posted on 2014-04-28
13
1,462 Views
Last Modified: 2014-05-01
Some years back, I got some code to work that finds a list of every open instance of Excel.  I would now like to do the same for Access.  The million-dollar question is how to translate the handle into a viable application object.  Without really understanding fully how I did it, I did manage to do this in Excel.  I've attached the code block below.

The procedure uses the declared functions "IIDFromString" and "FindWindowEx", of which I am not fond, as I can not touch, explore, nor discern them.  Somehow, in the code below, the hWinDesk and hWin7 variables magically translate a Main Window Handle into an Excel Application object, something I'd like to replicate with Access.  

I perform this exercise in order to decide whether to open a new Access object or leverage the use of one that is already open.  I wish to compare each Access object currently open with the one I wish to use.  Finding a match, I simply use it; and, not finding a match, I open the desired Access object.  

I'm open to new methods for achieving that objective and especially interested in any technique that, by leveraging existing VB.Net processes, obviates the need for declared functions.  If, however, declared functions can achieve the objective, I will gladly use it and give full credit for the answer.  Thank you, ~Peter Ferber

    Private Declare Function IIDFromString Lib "ole32" (ByVal lpsz As Int32, ByRef lpiid As GUID) As Long

    Declare Function FindWindowEx Lib "User32" Alias "FindWindowExA" ( _
        ByVal hWnd1 As Integer, ByVal hWnd2 As Integer, ByVal lpsz1 As String, ByVal lpsz2 As String) As Integer

    Private Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}"

    Function RetrieveExcelInstances() As Excel.Application()
        Dim Excel_Array() As Excel.Application
        Dim GetOneApp As Excel.Application
        Dim lngCount As Long
        Dim hWinXL As Long
        'Dim xlApp As Object ' Excel.Application
        'Dim wb As Object  ' Excel.Workbook

        On Error GoTo Err
        Excel_Array = Nothing
        lngCount = -1
        hWinXL = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
        While hWinXL > 0
            'Debug.Print "Instance_" & lngCount; hWinXL
            GetOneApp = GetXLapp(hWinXL)
            If (Not GetOneApp Is Nothing) Then
                lngCount = lngCount + 1
                ReDim Preserve Excel_Array(0 To lngCount)
                Excel_Array(lngCount) = GetOneApp
            End If
            hWinXL = FindWindowEx(0, hWinXL, "XLMAIN", vbNullString)
        End While
        RetrieveExcelInstances = Excel_Array

Func_Exit:
        Exit Function

Err:
        Select Case Err.Number
            Case Else
                Call MyUniversalClass.PrintError(Err, "RetrieveEWxcelInstances")
                Resume Func_Exit
                Resume
        End Select
    End Function

    Function GetXLapp(ByVal hWinXL As Long) As Excel.Application
        Dim hWinDesk As Long, hWin7 As Long
        Dim obj As Object
        Dim iid As GUID

        On Error GoTo Err
        iid = Nothing
        obj = Nothing
        Call IIDFromString(StrPtr(IID_IDispatch), iid)
        hWinDesk = FindWindowEx(hWinXL, 0&, "XLDESK", vbNullString)
        hWin7 = FindWindowEx(hWinDesk, 0&, "EXCEL7", vbNullString)
        If (AccessibleObjectFromWindow(hWin7, OBJID_NATIVEOM, iid, obj) = S_OK) Then
            GetXLapp = obj.Application
        Else
            GetXLapp = Nothing
        End If

Func_Exit:
        Exit Function

Err:
        Select Case Err.Number
            Case Else
                Call MyUniversalClass.PrintError(Err, "GetXLapp")
                Resume Func_Exit
                Resume
        End Select
    End Function

Open in new window

0
Comment
Question by:PeterFrb
  • 8
  • 5
13 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40029942
It looks like you will need something like Spy++ to figure out the windows for the Access instances that running, and use them here:

hWinXL = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
hWinDesk = FindWindowEx(hWinXL, 0&, "XLDESK", vbNullString)
hWin7 = FindWindowEx(hWinDesk, 0&, "EXCEL7", vbNullString)

and change the object types here:

Dim Excel_Array() As Excel.Application
Dim GetOneApp As Excel.Application
0
 

Author Comment

by:PeterFrb
ID: 40030345
Thank you, but now I'm working on getting Access applications.
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 40030469
Right, so you need the window names and Access.Application, instead of Excel.Application.  

Also, depending on the version of Access, you might be able to get instances from the Running Object Table (ROT).

Untested example:
<DllImport("user32.dll", CharSet:=CharSet.Auto)> Private Shared Sub GetClassName(ByVal hWnd As System.IntPtr, ByVal lpClassName As System.Text.StringBuilder, ByVal nMaxCount As Integer) End Sub
<DllImport("ole32.dll", ExactSpelling:=True, PreserveSig:=False)> Private Shared Function GetRunningObjectTable(ByVal reserved As Int32) As IRunningObjectTable End Function
<DllImport("ole32.dll", CharSet:=CharSet.Unicode, ExactSpelling:=True,  PreserveSig:=False)> Private Shared Function CreateItemMoniker(ByVal lpszDelim As String, ByVal lpszItem As String) As IMoniker End Function
<DllImport("ole32.dll", ExactSpelling:=True, PreserveSig:=False)> Private Shared Function CreateBindCtx(ByVal reserved As Integer) As IBindCtx End Function

Try

    Dim ROTObject As Object = Nothing
    Dim runningObjectTable As IRunningObjectTable
    Dim monikerEnumerator As IEnumMoniker = Nothing
    Dim monikers(1) As IMoniker

    runningObjectTable = GetRunningObjectTable(0)
    runningObjectTable.EnumRunning(monikerEnumerator)
    monikerEnumerator.Reset()

    Dim numFetched As IntPtr = New IntPtr()
    While (monikerEnumerator.Next(1, monikers, numFetched) = 0)
        Dim ctx As IBindCtx
        ctx = CreateBindCtx(0)

        Dim runningObjectName As String = ""
        monikers(0).GetDisplayName(ctx, Nothing, runningObjectName)

        runningObjectName = runningObjectName.ToUpper
        If (Not runningObjectName.Equals("")) Then
            Dim runningObjectIns As Object = Nothing
            runningObjectTable.GetObject(monikers(0), runningObjectIns)

            'Check if object is a Catia object
            Try
                Dim catiaIns As INFITF.Application = Nothing
                catiaIns = DirectCast(runningObjectIns, INFITF.Application)
                ListCATIA.Items.Add(catiaIns.Windows.Count)
             Catch Exc As Exception
                MessageBox.Show(Exc.ToString())
            End Try
        End If
    End While

Catch Exc As Exception
    Throw Exc
End Try

Open in new window

0
 

Author Comment

by:PeterFrb
ID: 40030808
Thanks, oh learned one.  I'm trying to get your code to run, but I can't resolve INFITF.  I'm unfamilar with CATIA, about which I'm learning as I Google and Noodle along.  Some programs I've seen start simply with "IMPORTS INFITF", but I think I need another reference to do that, on which the sites I've seen thus far have not elaborated.  

I'm pretty sure that with enough research, I can find the proper reference I need.  What would be helpful for me, and anyone else reading this, is if you could briefly expound on the subject of CATIA and INFITF.  I think we're on a good track here, and I'd appreciate your helping me expand my knowledge of the subject matter.  Sincerely, ~Peter Ferber
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40030834
The code was only an example taken from a post.  You would need Access.Application in place of that type.
0
 

Author Comment

by:PeterFrb
ID: 40030878
I've been doing some research, and CATIA has to be imported in order to use the functionality, which is why I can't find it.  This does not come standard issue with VBA, VB6, or VB.Net.  Without it, of course, I can't run the code you provided, great though it may be.  

Although this seemed like a good trajectory, I would prefer a tool that uses existing COM or VB.Net references, without having to venture outside to find additional tools that .Net should already cover.  Thanks for your efforts thus far.  ~Peter
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 500 total points
ID: 40031803
I am not sure that I understand what you mean, so here is some cleaned up code:

Public Class RunningObjectTable

<DllImport("user32.dll", CharSet:=CharSet.Auto)> 
Private Shared Sub GetClassName(ByVal hWnd As System.IntPtr, ByVal lpClassName As System.Text.StringBuilder, ByVal nMaxCount As Integer)
End Sub

<DllImport("ole32.dll", ExactSpelling:=True, PreserveSig:=False)> 
Private Shared Function GetRunningObjectTable(ByVal reserved As Int32) As IRunningObjectTable 
End Function

<DllImport("ole32.dll", CharSet:=CharSet.Unicode, ExactSpelling:=True,  PreserveSig:=False)> 
Private Shared Function CreateItemMoniker(ByVal lpszDelim As String, ByVal lpszItem As String) As IMoniker 
End Function

<DllImport("ole32.dll", ExactSpelling:=True, PreserveSig:=False)> 
Private Shared Function CreateBindCtx(ByVal reserved As Integer) As IBindCtx End Function

Public Shared Function GetRunningAccess()

    Dim ROTObject As Object = Nothing
    Dim runningObjectTable As IRunningObjectTable
    Dim monikerEnumerator As IEnumMoniker = Nothing
    Dim monikers(1) As IMoniker

    runningObjectTable = GetRunningObjectTable(0)
    runningObjectTable.EnumRunning(monikerEnumerator)
    monikerEnumerator.Reset()

    Dim numFetched As New IntPtr()
    While monikerEnumerator.Next(1, monikers, numFetched) = 0
        Dim ctx As IBindCtx = CreateBindCtx(0)

        Dim runningObjectName As String = ""
        monikers(0).GetDisplayName(ctx, Nothing, runningObjectName)
        runningObjectName = runningObjectName.ToUpper()

        If Not runningObjectName.Equals("") Then
            Dim runningObjectIns As Object = Nothing
            runningObjectTable.GetObject(monikers(0), runningObjectIns)

                Dim application As Access.Application = DirectCast(runningObjectIns, Access.Application)

             Return application
        End If
    End While

    Return Nothing

    End Function

End Class

Open in new window

0
 

Author Comment

by:PeterFrb
ID: 40032696
Although the process is not successfully retrieving the Access object, I very much appreciate your efforts.  All the code compiles without error, and the use of the Running Object Table (with the unfortunate acronym ROT) is, I believe, the correct method.  Unfortunately, after running the statement "runningObjectTable = GetRunningObjectTable(0)", the runningObjectTable comes up with an error (see screenshot, below).  

What I like about this is that it attempts to create a complete solution within the existing framework of VB.Net, and I'm sure that a little tweaking will eventually come up with a viable solution.  I welcome further comments, and I will continue researching this on my end.  Thank you, ~Peter Ferber

Error for RunningObjectTable, no further info available
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40032724
COM objects, like the Running Object Table, cannot be inspected like .NET objects.  You just have to know how the interface works, and program to that.  It is dynamically compiled and evaluated during run-time.

Does that code go into the while loop?  
Does the code set "runningObjectName" after the call to GetDisplayName?
0
 

Author Comment

by:PeterFrb
ID: 40032879
To answer these questions, the process is "runningObjectTable = GetRunningObjectTable(0)", which is one of the first procedures, before the while loop.  

Once in Debug mode, I would expect to be able to see the properties associated with this object in the Watch and Immediate windows, just like anythng else.  Wouldn't this be true in this case?

Please consider my original post as another avenue of inquiry.  The FindWindowEx for XLMAIN, XLDESK, and EXEL7 resolves to Excel application objects reliably.  I believe there has to be an equally reliable sequence that will just as reliably resolve to Access application objects.  Any thoughts on that?

Going back to my original objective, what I've been trying in vain to find is the exact full-file path associated with each open Access object.  Since the code for killing a process is readily available, I find it incredible that this information is so difficult to ascertain.  As an example, consider the situation where multiple Access applications are open, each one running exactly the same file name, although kept in different folders.  My objective is to kill just one application: the correct one.  Solving that conundrum will completely satisfy my requirement (the two candidates I found likeliest to answer this question were Process.StartInfo.FileName and Process.MainModule.FileName, but neither did).  

Does the ROT dataset provide the full file path to each open file?  

Thanks, ~Peter
0
 

Author Comment

by:PeterFrb
ID: 40035216
I've requested that this question be closed as follows:

Accepted answer: 0 points for PeterFrb's comment #a40032879
Assisted answer: 250 points for TheLearnedOne's comment #a40030469
Assisted answer: 250 points for TheLearnedOne's comment #a40031803

for the following reason:

Even though the problem is not solved, you've done some good work here, for which I'm giving you credit.  I find that it sometimes helps to create a new question, using the insight thus far gained, because there's generally more energy around a new question than one that's been around the block a few times.  Thanks for the help thus far.  Going for round 2.
0
 

Author Comment

by:PeterFrb
ID: 40035207
I don't understand this.  I did not intend for my comment to be the solution.  I intended TheLearnedOne's two comments to constitute the answer.
0
 

Author Closing Comment

by:PeterFrb
ID: 40035217
Trying again.  I've posted a new question, based on the progress made here.  Good work has been done on this question, and even though it is not fully answered, it helped me hone the question.  My experience is that there is more energy to answering new questions than ones that have been around the block a few times.  Hoping round 2 completely answers the question.  ~Peter
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now