• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

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.
0
inkineu
Asked:
inkineu
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now