使用VB.NET获取Excel的运行实例 [英] Get running instances of Excel with VB.NET

问题描述

我有以下工作代码来自此答案：

I have the following working code taken from this answer:

Option Compare Binary
Option Explicit On
Option Infer On
Option Strict Off

Imports Microsoft.Office.Interop
Imports System.Collections.Generic
Imports System.Runtime.InteropServices

Friend Module Module1
    Private Declare Function GetDesktopWindow Lib "user32" () As IntPtr
    Private Declare Function EnumChildWindows Lib "user32.dll" (ByVal WindowHandle As IntPtr, ByVal Callback 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 Integer) As Integer
    Private Delegate Function EnumWindowsProc(ByVal hwnd As IntPtr, ByVal lParam As Int32) As Boolean
    Private Declare Function AccessibleObjectFromWindow Lib "oleacc" (ByVal Hwnd As IntPtr, ByVal dwId As Int32, ByRef riid As Guid, <MarshalAs(UnmanagedType.IUnknown)> ByRef ppvObject As Object) As Int32
    Private lstWorkBooks As New List(Of String)
    Public Sub Main()
        GetExcelOpenWorkBooks()
    End Sub
    Private Sub GetExcelOpenWorkBooks()
        EnumChildWindows(GetDesktopWindow(), AddressOf GetExcelWindows, CType(0, IntPtr))
        If lstWorkBooks.Count > 0 Then MsgBox(String.Join(Environment.NewLine, lstWorkBooks))
    End Sub
    Public Function GetExcelWindows(ByVal hwnd As IntPtr, ByVal lParam As Int32) As Boolean
        Dim Ret As Integer = 0
        Dim className As String = Space(255)
        Ret = GetClassName(hwnd, className, 255)
        className = className.Substring(0, Ret)
        If className = "EXCEL7" Then
            Dim ExcelApplication As Excel.Application
            Dim ExcelObject As Object = Nothing
            Dim IDispatch As Guid
            AccessibleObjectFromWindow(hwnd, &HFFFFFFF0, IDispatch, ExcelObject)
            If ExcelObject IsNot Nothing Then
                ExcelApplication = ExcelObject.Application
                If ExcelApplication IsNot Nothing Then
                    For Each wrk As Excel.Workbook In ExcelApplication.Workbooks
                        If Not lstWorkBooks.Contains(wrk.Name) Then
                            lstWorkBooks.Add(wrk.Name)
                        End If
                    Next
                End If
            End If
        End If
        Return True
    End Function
End Module

它将用于获取所有打开/正在运行的Excel实例的引用/应用程序。

It will be used to get references of all the opened/running Excel instances/applications.

如果不在线查找，我永远也不会猜到该怎么做，因为我不太了解它，所以这可能不是最好的方法且为 bug /容易出错。 我正在尝试打开选项严格 （ 1 ， 2 ），因此我将 ExcelApplication = ExcelObject.Application 行更改为 ExcelApplication = CType（ExcelObject，Excel.Application）.Application ，但这样做会引发异常：

Without looking it up online I would never guess how to do it as I don't understand much of it, so it's probably not the best way to do it and is bug/error prone. I'm trying to turn option strict on (1, 2) so I changed the line ExcelApplication = ExcelObject.Application to ExcelApplication = CType(ExcelObject, Excel.Application).Application but doing so throws the exception:

System.InvalidCastException 无法投射类型为'System .__ ComObject'的COM对象到接口类型为'Microsoft.Office.Interop.Excel.Application'。该操作失败，因为对IID为 {000208D5-0000-0000-C000-000000000046}的接口的COM组件上的查询接口调用由于以下错误而失败：不支持此类接口。 （来自HRESULT的异常：0x80004002（E_NOINTERFACE））。

System.InvalidCastException Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Application'. This operation failed because the Query Interface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported. (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

我可以在不同的站点找到对此的多个相似引用，但没有运气很好，可以使用试用和错误方法。

I can find multiple look alike references to this in different sites but haven't got the luck to fix it with the trial and error method.

我的问题是如何严格启用选项，如果有人帮助我获得更好的代码或修复/解释它的其他问题，则奖励。

My question is how to turn on option strict and bonus if someone helps me get a better code or fix/explain any other issues with it.

推荐答案

我之前标记为接受的其他答案很好，但是有一个问题（* ），这是因为它仅获取活动对象，即第一个Excel进程。

The other answer which I had previously marked as accepted is great, but there is a catch (*), which is that it only gets the active object, the first Excel process.

在大多数情况下就足够了，但是在特定情况下，大于已打开Excel实例。据我所知，这只能通过在启动Excel时按住 Alt 键，提示在新实例中启动Excel或在某些程序中使用代码来实现。

That is enough in most cases, but not in a specific one where there is more than one instance of Excel opened. From what I know, that is only possible by either holding the Alt key when starting Excel which prompts to start Excel in a new instance, or with code in some program.

另一方面，问题中的代码确实起作用并解决了获取所有正在运行的Excel实例的问题。我遇到的唯一问题是将其从后期绑定（ Option Strict Off ）转换为早期绑定（ Option Strict On ）导致直到现在我都找不到答案的错误。

On the other hand the code in the question does work and solve the issue of getting all running instances of Excel. The only problem that I was having was converting it from late binding (Option Strict Off) to early binding (Option Strict On) which was causing an error that I couldn't find the answer to, until now.

借助答案在另一个解决C＃问题的问题中，我发现我必须替换函数<< c> c的参数 ppvObject code> AccessibleObjectFromWindow 来自：

With the help of an answer in another question that approaches the issue in C# I found out that I had to replace the parameter ppvObject of the function AccessibleObjectFromWindow from:

<MarshalAs(UnmanagedType.IUnknown)> ByRef ppvObject As Object

收件人：

ByRef ppvObject As Excel.Window

并更改从 Object 到 Excel.Window 的声明中的变量 ExcelObject （也是在代码中将其重命名为 ExcelWindow 的一种很好的做法）。

And change the type of the variable ExcelObject in the declaration from Object to Excel.Window (also good practice to rename it to ExcelWindow in the code).

这篇关于使用VB.NET获取Excel的运行实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！