Display all open Excel Workbooks in listbox

I would like to display all open workbooks in a listbox.  The problem being that there may be a number of Excel Processes running.  Also, some workbooks may not yet be saved.

Code so far:
            For Each P As Process In Process.GetProcessesByName("Excel")
                For Each wb As Excel.Workbook In ??.Workbooks
                ..........
                Next
            Next

Visual Studio 2008.
LVL 1
inkineuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TigerManCommented:
hi inkineu
the attached does what you ask but i have split into different blocks so you can watch in the code window and adjust to suit your needs
listboxexample.xls
0
Bob LearnedCommented:
There is a considerably more complex way, but kind of cool.  It enumerates through the Running Object Table (ROT), where COM objects are registered.


Imports System.Runtime.InteropServices
Imports System.Runtime.InteropServices.ComTypes
Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim list As List(Of ComObject) = RunningObjectTableHelper.EnumerateROT()

            For Each obj As ComObject In list
                If obj.IsFileMoniker AndAlso obj.ObjectTypeName = "Workbook" Then
                    Dim workbook As Excel.Workbook = TryCast(obj.ObjectInstance, Excel.Workbook)
                    Dim displayName As String = obj.DisplayName
                End If
            Next

        Catch ex As Exception
            Dim m As String = ex.Message
        End Try

    End Sub

End Class

Public Class ComObject

    Public Property ObjectTypeName As String
    Public Property ObjectInstance As Object
    Public Property DisplayName As String
    Public Property ClassId As Guid
    Public Property IsFileMoniker As Boolean

    Public Sub New(moniker As IMoniker)
        ' Get display name
        Dim ctx As IBindCtx = Nothing
        UnsafeNativeMethods.CreateBindCtx(0, ctx)
        moniker.GetDisplayName(ctx, Nothing, DisplayName)

        ' Get class ID
        moniker.GetClassID(ClassId)

        Dim fileMonikerGuid = New Guid("00000303-0000-0000-c000-000000000046")
        IsFileMoniker = ClassId.Equals(fileMonikerGuid)

        If IsFileMoniker Then

            ' Get the instance of the COM object
            ObjectInstance = Marshal.BindToMoniker(DisplayName)

            ' Determine the type name
            ObjectTypeName = TypeName(ObjectInstance)
        End If

    End Sub

End Class

Public Class RunningObjectTableHelper

    Private Shared m_runningObjectTable As IRunningObjectTable

    Shared Sub New()
        UnsafeNativeMethods.GetRunningObjectTable(0, m_runningObjectTable)
    End Sub

    Public Shared Function EnumerateROT() As List(Of ComObject)
        Dim numFetched As Integer
        Dim monikerEnumerator As IEnumMoniker = Nothing
        Dim monikers As IMoniker() = New IMoniker(0) {}

        m_runningObjectTable.EnumRunning(monikerEnumerator)
        monikerEnumerator.Reset()

        Dim monikerList As New List(Of ComObject)

        While monikerEnumerator.[Next](1, monikers, numFetched) = 0
            monikerList.Add(New ComObject(monikers(0)))
        End While

        Return monikerList
    End Function

    Public Shared Function GetObject(moniker As IMoniker) As Object
        Dim runningObjectVal As Object = Nothing
        m_runningObjectTable.GetObject(moniker, runningObjectVal)
        Return runningObjectVal
    End Function

End Class

Friend Class UnsafeNativeMethods
    <DllImport("ole32.dll")> _
    Friend Shared Sub CreateBindCtx(reserved As Integer, ByRef ppbc As IBindCtx)
    End Sub

    <DllImport("ole32.dll")> _
    Friend Shared Sub GetRunningObjectTable(reserved As Integer, ByRef prot As IRunningObjectTable)
    End Sub
End Class

Open in new window

0
inkineuAuthor Commented:
TigerMan:
Your code would only deal with the current excel session, is that correct?
      eg.   For Each wb In Application.Workbooks
I need the ability to loop through all excel processes.

TheLearnedOne:
I'm curious about the less complex method you referred to.  Seems like there should be a simple method of handling this. You're right though, that code is pretty cool!    
0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

TigerManCommented:
inkineu
i believe Application.* refers to all instances of excel that are open or in use as per "display all open workbooks"
0
inkineuAuthor Commented:
Not so.  Application refers to the current Workbook application only.  Tried it out as well, and that's how it appears to work.
0
TigerManCommented:
most odd because i have tried with 10 open workbooks ... anyway, hope you find what you need
0
Rory ArchibaldCommented:
FYI, Excel only registers itself in the ROT once, so you cannot use that to get all instances of it. I don't do .Net but in VBA I would go through the windows looking for XLMAIN classes, then you can get a workbook object from each child EXCEL7 class window.


Regards,
Rory
0
Bob LearnedCommented:
Yeah, but FileMonikers are registered for each document in the ROT...
0
Rory ArchibaldCommented:
Makes me wish I did do .Net then. :)
0
Bob LearnedCommented:
There is a lot of code out there that talks about using GetActiveObject and BindToMoniker.  GetActiveObject is limited to a single instance.  BindToMoniker needs beforehand knowledge of the files that are open.

I had some code that examined the ROT, so it was a small step, as an experiment to see how much more information I could gather from the table.  You can see that the ComObject class is an encapsulation of what I found.  I thought that finding the TypeName function was pretty cool, since COM types are represented by System.__ComObject.  It is so much easier that what I used to do:

HOW TO: Check the Type of a COM Object (System.__ComObject) with Visual C# .NET
http://fernandof.wordpress.com/2008/02/05/how-to-check-the-type-of-a-com-object-system__comobject-with-visual-c-net/

C# Note:
The TypeName function is in the Microsoft.VisualBasic.Interaction namespace.  You would need to add a reference to Microsoft.VisualBasic.dll.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.