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

Posted on 2012-08-14
Last Modified: 2012-08-15
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, 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
Question by:PeterFrb
    LVL 96

    Expert Comment

    by:Bob Learned
    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.
    LVL 13

    Accepted Solution

    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

    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;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
                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
                    ' 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
                Catch ex As Exception
                End Try
        End Sub
    End Class

    Open in new window


    Author Closing Comment

    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.

    Author Comment

    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.
    LVL 13

    Expert Comment

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

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now