Question Get Active file names from already running Excel and Word

[SteveMcD]

I am developing approval automation for documents with vb through .Net as an independent program (not as a vb Macro). In order for this to function I need to be able to list all currently open excel and word documents and for me to obtain the full file path of each document.

I fear I have bitten off more than I can code and am finding nothing which assists. Through the process.getprocess route I can get active handles for word and excel but after that I'm stuck.

Any help would be appreciated and if it can't be done, then let me know and I'll have to find another way around this.

 

[JohnH]

From my research the only way (*) to do that is to enumerate the Running Object Table, this is some complex code using unmanaged methods and interfaces. Code example follows.

imports:

Imports System.Runtime.InteropServices

declarations:

Private Declare Function GetRunningObjectTable Lib "ole32.dll" (reserved As Int32, ByRef pprot As ComTypes.IRunningObjectTable) As Integer
Private Declare Function CreateBindCtx Lib "ole32.dll" (ByVal reserved As Int32, ByRef ppbc As ComTypes.IBindCtx) As Integer
Private Declare Function ProgIDFromCLSID Lib "ole32.dll" (<[In]()> ByRef clsid As Guid, <MarshalAs(UnmanagedType.LPWStr)> ByRef lplpszProgID As String) As Integer
Private Const S_OK As Int32 = &H0

enumerator example

Private Sub EnumROT()
    Dim ROT As ComTypes.IRunningObjectTable = Nothing
    If GetRunningObjectTable(0, ROT) = S_OK Then
        Dim enumerator As ComTypes.IEnumMoniker = Nothing
        Dim monikers(0) As ComTypes.IMoniker
        Dim ctx As ComTypes.IBindCtx = Nothing            
        ROT.EnumRunning(enumerator)
        If enumerator IsNot Nothing Then
            If CreateBindCtx(0, ctx) = S_OK Then
                Do While enumerator.Next(1, monikers, Nothing) = S_OK
                    Dim progid As String = Nothing
                    Dim displayname As String = Nothing
                    Dim classid As Guid = Nothing
                    monikers(0).GetDisplayName(ctx, Nothing, displayname)
                    monikers(0).GetClassID(classid)                        
                    If ProgIDFromCLSID(classid, progid) = S_OK Then
                        If progid = "file" Then
                            Dim ext = IO.Path.GetExtension(displayname).ToLower
                            If ext = ".xls" OrElse ext = ".doc" Then
                                Debug.WriteLine(displayname)                                    
                            End If
                        End If
                    End If
                Loop
            End If
        End If           
    End If
End Sub

Here is also a sample for getting the automation object for a Excel workbook from the moniker, this sample just get the WorkBook object and query its FullName property, which returns same path as the display name of the moniker:

'Imports Excel = Microsoft.Office.Interop.Excel

Dim o As Object = Nothing
If ROT.GetObject(monikers(0), o) = S_OK Then
    Debug.WriteLine(CType(o, Excel.Workbook).FullName)
End If

(*) more research shows it is also possible to get the automation object using unmanaged AccessibleObjectFromWindow method, which is not any easier.

 

[SteveMcD]

Sorry, I can't get that to work (unless its just my own incompetence, the programming is a bit complex). It seems to be ignoring the office applications . I did read somewhere that Office 2007 does not register itself in the ROT (deliberately apparently).
You accessibleobjectfromwindow suggestion looks promising but I can't find any suitable VB code for this -can you oblige ?
Alternatively am I doing something wrong with your posted code - pasted into standalone project and called when button clicked. Output window shows nothing even if doc or xls open. If I remove the filter it shows the Vb.net project file name but nothing else.

Thanks (again)

Steve

 

[JohnH]

Office 2007 doesn't register in ROT on load, but waits until first time it looses focus, so at the time you click a button in your app this will necessarily have happened.

 

[JohnH]

The AccessibleObjectFromWindow loadout:

Private Delegate Function EnumWindowsProc(ByVal hwnd As IntPtr, ByVal lParam As IntPtr) As Boolean
Private Declare Function EnumChildWindows Lib "user32.dll" (ByVal hWndParent As IntPtr, ByVal lpEnumFunc As EnumWindowsProc, ByVal lParam As IntPtr) As Boolean
Private Declare Function GetClassName Lib "user32.dll" Alias "GetClassNameA" (ByVal hwnd As IntPtr, ByVal lpClassName As String, ByVal nMaxCount As Int32) As Int32
Private Const MAX_TITLE As Int32 = 256

Private Function GetClassName(ByVal hwnd As IntPtr) As String
    Dim name As New String(" "c, MAX_TITLE)
    Dim len = GetClassName(hwnd, name, MAX_TITLE)
    If len = 0 Then Return Nothing
    Return name.Remove(len)
End Function

Private Declare Function AccessibleObjectFromWindow Lib "OLEACC.dll" (ByVal hwnd As IntPtr, ByVal dwId As Int32, ByVal riid As Byte(), ByRef ppvObject As IntPtr) As Integer
Private Const OBJID_NATIVEOM As Int32 = &HFFFFFFF0
Private IID_IDispatch As New Guid("{00020400-0000-0000-C000-000000000046}")

a loop to get Word/Excel processes and main window handles:

For Each p In Process.GetProcesses
    If p.ProcessName = "WINWORD" OrElse p.ProcessName = "EXCEL" Then
        EnumChildWindows(p.MainWindowHandle, AddressOf EnumCWindows, IntPtr.Zero)
    End If
Next

EnumChildWindows callback:

' using these imports:
'Imports Word = Microsoft.Office.Interop.Word
'Imports Excel = Microsoft.Office.Interop.Excel
'Imports System.Runtime.InteropServices

Function EnumCWindows(ByVal hwnd As IntPtr, ByVal lParam As IntPtr) As Boolean
    Dim name = GetClassName(hwnd)
    If name = "EXCEL7" Then
        Dim ptr As IntPtr
        If AccessibleObjectFromWindow(hwnd, OBJID_NATIVEOM, IID_IDispatch.ToByteArray(), ptr) = S_OK Then
            Dim win = CType(Marshal.GetObjectForIUnknown(ptr), Excel.Window)
            Dim sheet = CType(win.ActiveSheet, Excel.Worksheet)
            Dim book = CType(sheet.Parent, Excel.Workbook)
            Debug.WriteLine(book.FullName)
        End If
    ElseIf name = "_WwG" Then
        Dim ptr As IntPtr
        If AccessibleObjectFromWindow(hwnd, OBJID_NATIVEOM, IID_IDispatch.ToByteArray(), ptr) = S_OK Then
            For Each doc As Word.Document In CType(Marshal.GetObjectForIUnknown(ptr), Word.Window).Application.Documents
                Debug.WriteLine(doc.FullName)
            Next
        End If
    End If
    Return True
End Function

 

[SteveMcD]

That works fantastically - I am so pleased. Now (as I am self taught) I can enjoy understanding exactly why it works.

Thanks a million.

Hopefully this will be of use to others as well.

 

[SteveMcD]

Using Vb.ner to List all currently Open Documents in Word/Excel 2003

I got some great help for obtaining the file names and paths for all currently open Excel and Word documents for 2007, however the same code does not work for Excel and Word 2003.
Anyone have any thoughts on how I can get these names.
The 2007 version worked with checking the process name and then if Excel or Word Enumerating the Child windows through the MarshalGetObject using the window handles (I think). Then it checks for "EXCEL7" or "_WwG" names before moving into the code I have the problem with. I am getting a file not found error from Word and an Invalid Class string for Excel.
The lines are (ptr is an intptr);
Dim win = CType(Marshal.GetActiveObject(ptr), Excel.window) ' for excel files
For each doc as word.document in ctype(marshal.getobjectforiunknown(ptr), word.window).application.documents ' for word files
I really am a little out of my depth here and I am hoping that there is a simple way of doing this with the older version of office which I have to adapt my code for to be backwards compatible.

 

[JohnH]

I tested both approaches with Office 2003, and they both work for me. What I think may be your problem is that you are referencing the interop for a specific Office version, and with the latter approach the Office window object has to be cast to correct interop interface, which in your case would break if you referenced 2007 interop and tried to cast a 2003 interop object. As I see it there shouldn't be a problem for you using the first approach, avoiding Office interop altogether. The alternative would be to reference both and figure out which was what, or reference neither and use late binding, eg for the Excel code something like:

Dim name = Marshal.GetObjectForIUnknown(ptr).ActiveSheet.Parent.FullName

 

[SteveMcD]

Apologies - my error. I assumed the first code was not working as it produced no output - I changed the debug to console and it works wonderfully. A very elegant solution.
Strangely for what its worth, the other thing I found out was that Office 2003 only seems to work with me if I dim them as objects. If I use excel.application for example it throws a file not found error. Still the object workaround works fine.

Thanks again for the help.

 

[JohnH]

Strangely for what its worth, the other thing I found out was that Office 2003 only seems to work with me if I dim them as objects. If I use excel.application for example it throws a file not found error.

Yes, as I explained, you can't cast the Office 2003 object to a 2007 interop type or vice versa.

I assumed the first code was not working as it produced no output - I changed the debug to console and it works wonderfully.

Ah, looking in wrong window then, or running a release build.

 

[SteveMcD]

Thought I'd cracked it but the ROT solution does not pick up sharePoint files in office 2003. Strangely it does work in office 2007.
I have built the application on a standalone PC which only has office 2003 on it to allow me to test and debug and it works fine for files on the PC but not when it is an internet based file. It picks up a couple of CLSID:Hex Codes but no open sharepoint based excel document. I really am starting to dislike the difficulties in trying to make something work in two versions of office !
I have tried your second suggested code but I get an invalid class string on the first dim Ctype for Excel.
Not sure where to go now (apart from College to try to learn any of this !)

 

[JohnH]

I can't test that, you'll have to do some research yourself or hope for feedback from other forum users.

 

[SteveMcD]

Will do. Thanks

 

[pramaratha]

"S_OK" Error with the code Provided

From my research the only way (*) to do that is to enumerate the Running Object Table, this is some complex code using unmanaged methods and interfaces. Code example follows.

enumerator example

Private Sub EnumROT()
Dim ROT As ComTypes.IRunningObjectTable = Nothing
If GetRunningObjectTable(0, ROT) = S_OK Then
Dim enumerator As ComTypes.IEnumMoniker = Nothing
Dim monikers(0) As ComTypes.IMoniker
Dim ctx As ComTypes.IBindCtx = Nothing
ROT.EnumRunning(enumerator)
If enumerator IsNot Nothing Then
If CreateBindCtx(0, ctx) = S_OK Then
Do While enumerator.Next(1, monikers, Nothing) = S_OK
Dim progid As String = Nothing
Dim displayname As String = Nothing
Dim classid As Guid = Nothing
monikers(0).GetDisplayName(ctx, Nothing, displayname)
monikers(0).GetClassID(classid)
If ProgIDFromCLSID(classid, progid) = S_OK Then
If progid = "file" Then
Dim ext = IO.Path.GetExtension(displayname).ToLower
If ext = ".xls" OrElse ext = ".doc" Then
Debug.WriteLine(displayname)
End If
End If
End If
Loop
End If
End If
End If
End Sub

Sorry, but I have a problem with the above code.
In VS 2010 it gives me Error for line containing "S_OK" (Highlighted in RED above)
Error is: "S_OK is not declared, It may be inaccessible due to its protection level."

 

[JohnH]

The S_OK constant is declared in post 2.