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

Posted on 2012-08-14
Medium Priority
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 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
Question by:PeterFrb
  • 2
  • 2
LVL 96

Expert Comment

by:Bob Learned
ID: 38296205
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

Corey Scheich earned 1500 total points
ID: 38297046
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 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
            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

ID: 38297500
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

ID: 38297622
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

by:Corey Scheich
ID: 38297661
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month17 days, 5 hours left to enroll

862 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