We help IT Professionals succeed at work.

VBA vs. VB.Net: .Net not returning Excel Objects

I have attached two files to this post, which must be open and run to really understand what I'm trying to do.  The idea of having a function that can return all instances of Excel applications running on the desktop is extremely valuable and compelling to me, and I have designed just such a process as both a VBA and VB.Net application.  [A huge shout-out goes to "ForEachLoop" (yes, his online name) at the site http://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel, who planted the seed on which I built my own app.]  

The VBA piece inside my Access application performs as I designed it: return an array of every Excel application running on the desktop, along with its handle.  However, the VB.Net code fails to accomplish the feat.  When I run the routine "ReturnExcelObjectFromHWnd", at the point that the routine calls "IIDFromString", I contrast the results of the IID variable: in VBA, the "IIDFromString" has changed the variable contents in meaningful ways.  In VB.Net, the contents are unchanged.  

I made a notation in the code of where the breakdown between VBA and VB.Net appears, and I've included an image of that portion of code.  

I've worked really hard to distill all the problem into its relevant components, and hopefully you can replicate my findings and come up with a solution.

Thanks, ~Peter Ferber

P.S.  I've added the file handle because that's the environment I've inherited.  My main interest is in the Excel Application objects.   If VB.Net has its own method of achieving the same result without the use of DOS declaritives, I am just fine dispensing the file handle!  The real gold here is the application objects.

Late-breaking news: I discovered that my strPtr function in VB.Net calls varptr, but the results are different.  In VBA, there is a call to strPtr, and I can't find a reference anywhere to that function.  The calls in the different languages yield different values, which is likely the cause of the difficulty.  I'm still looking for a means to solve the problem, and this may be the tip I need.


Image of error messageClean-FindExcel.accdb
modProcess.vb
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
What version of Excel are you working with?  I have a routine that enumerates the objects registered in the Running Objects Table (ROT), and Excel 2010 causes a problem, since it doesn't register with the ROT as expected.
Developer
Commented:
Here is a working .Net example that uses the Process list as a starting point I tested it with 2007 because that's what was handy.  I started with a win form with a button and a textbox with multiline = true

the brunt of the work was found on this thread
http://stackoverflow.com/questions/779363/how-to-use-use-late-binding-to-get-excel-instance

Imports Microsoft.Office.Interop.Excel
Imports System.Text
Imports System.Runtime.InteropServices
Imports System.Globalization

Public Class Form1

    ''' <summary>
    ''' Interface definition for Excel.Window interface
    ''' </summary>
    <Guid("00020893-0000-0000-C000-000000000046"), _
    InterfaceType(ComInterfaceType.InterfaceIsIDispatch)> _
    Public Interface ExcelWindow
    End Interface

    ''' <summary>
    ''' This class is needed as a workaround to http://support.microsoft.com/default.aspx?scid=kb;en-us;320369
    ''' Excel automation will fail with the follwoing error on systems with non-English regional settings:
    ''' "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))" 
    ''' </summary>
    Class UILanguageHelper
        Implements IDisposable

        Private _currentCulture As CultureInfo

        Public Sub New()
            ' save current culture and set culture to en-US 
            _currentCulture = System.Threading.Thread.CurrentThread.CurrentCulture
            System.Threading.Thread.CurrentThread.CurrentCulture = New CultureInfo("en-US")
        End Sub

        Public Sub Dispose() Implements System.IDisposable.Dispose
            'reset to original culture 
            System.Threading.Thread.CurrentThread.CurrentCulture = _currentCulture
        End Sub

    End Class

    <DllImport("user32.dll", SetLastError:=True, CharSet:=CharSet.Auto)> _
    Private Shared Function FindWindow(ByVal lpClassName As String, ByVal lpWindowName As String) As IntPtr
    End Function

    <DllImport("Oleacc.dll")> _
    Private Shared Function AccessibleObjectFromWindow(ByVal hwnd As Integer, ByVal dwObjectID As UInt32, ByVal riid() As Byte, ByRef ptr As ExcelWindow) As Integer
    End Function

    Public Delegate Function EnumChildCallback(ByVal hwnd As Integer, ByRef lParam As Integer) As Boolean

    <DllImport("User32.dll")> _
    Public Shared Function EnumChildWindows(ByVal hWndParent As Integer, ByVal lpEnumFunc As EnumChildCallback, ByRef lParam As Integer) As Boolean
    End Function

    <DllImport("User32.dll")> _
    Public Shared Function GetClassName(ByVal hWnd As Integer, ByVal lpClassName As StringBuilder, ByVal nMaxCount As Integer) As Integer
    End Function

    ' Callback passed to EnumChildWindows to find any window with the
    ' registered classname "paneClassDC" - this is the class name of
    ' PowerPoint's accessible document window.
    Public Function EnumChildProc(hwnd As Integer, ByRef lParam As Integer) As Boolean

        Dim windowClass As StringBuilder = New StringBuilder(128)
        GetClassName(hwnd, windowClass, 128)
        If windowClass.ToString() = "EXCEL7" Then
            Debug.Print(windowClass.ToString)
            lParam = hwnd
            Return False
        End If
        Return True
    End Function

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        TextBox1.Text = ""
        For Each p As Process In Process.GetProcessesByName("Excel")
            TextBox1.Text += p.MainWindowTitle & vbCrLf
            Dim App As ExcelWindow
            Try
                ' Walk the children of this window to see if any are
                ' IAccessible.
                Dim hwnd As Integer = p.MainWindowHandle
                Dim hWndChild As Integer = 0
                Dim cb As EnumChildCallback =
                    New EnumChildCallback(AddressOf EnumChildProc)
                EnumChildWindows(hwnd, cb, hWndChild)
                ' OBJID_NATIVEOM gets us a pointer to the native 
                ' object model.
                Dim OBJID_NATIVEOM As UInteger = CUInt("&HFFFFFFF0")
                Dim IID_IDispatch As Guid = GetType(Application).GUID
                IID_IDispatch = New Guid("{00020400-0000-0000-C000-000000000046}") '


                App = Nothing
                Dim hr As Integer = AccessibleObjectFromWindow(
                    hWndChild, OBJID_NATIVEOM,
                    IID_IDispatch.ToByteArray(), App)
                Dim xl As Application = App.Application '<---now that we have the window we can get the application property and do some real work
                TextBox1.Text += xl.Range("A1").Value & vbCrLf
                Debug.Print(hr)
            Catch ex As Exception

            End Try

        Next


    End Sub
End Class

Open in new window

PeterFrbData anslyst, tableau visualization developer, vb certified

Author

Commented:
This definitely contains the foundation for what I'm trying to do.  I obviously need to brush up on processes, but this gives me code to really peruse and discern what's happening and adapt it to my own uses.  Thank you.
PeterFrbData anslyst, tableau visualization developer, vb certified

Author

Commented:
Just to confirm, a very slight tweaking of this code rendered spot-on results.  If I knew how simple it would actually be, I would have graded this as excellent, not just good.  Consider it done.
Corey ScheichDeveloper

Commented:
If you want to change the grade you could use the Request attention button.  Either way I am glad it accomplished what you needed.