如何在不设置引用的情况下从另一个Office应用程序打开Excel? [英] How can I open Excel from another Office Application without setting references?

问题描述

在Word和Outlook中，我试图使用GetObject方法打开打开的Excel，但需要首先设置引用.我收到编译错误:未定义用户定义的类型".为了手动修复此问题，我需要转到工具">参考">"Microsoft Excel XX.X对象库".有没有一种方法不必手动设置参考?

From Word and Outlook, I'm trying to open open Excel using GetObject method but I need to set the references first. I receive "Compile error: User-defined type not defined". In order to manually fix this, I need to go to Tools>References>Microsoft Excel XX.X Object Library. Is there a way to not have to do the manual step of setting the reference?

我从具有不同版本的Excel的不同会话(本地会话，citrix，VM Ware)访问相同的文件.

I access the same files from different sessions (local session, citrix, VM ware) that have different versions of Excel.

> https://docs.microsoft.com/zh-cn/office/vba/excel/concepts/working-with-other-applications/controlling-one-microsoft-office-application-from -另一个

到目前为止的代码

Sub Macro1()
'
    Dim Excel As Object

    Dim wkbkXLBook As Excel.Workbook
    Dim wkSheet As Excel.worksheet

    Set Excel = GetObject(, "Excel.Application")

    If Excel Is Nothing Then

        MsgBox ("Excel was not found")

    End If

    Selection.WholeStory
    Selection.WholeStory
    Selection.Copy
End Sub

推荐答案

如果不将引用添加到类型库，则不能使用该类型库中定义的早期绑定类型.

If you're not adding the reference to the type library, you can't use the early-bound types defined in that type library.

Dim wkbkXLBook As Excel.Workbook
Dim wkSheet As Excel.worksheet

Excel是Excel类型库的程序名称，而Workbook是在该库中定义的类的名称.与Worksheet相同.由于未引用Excel，因此VBA无法解析这些类型，并且会出现编译错误.

Excel is the programmatic name of the Excel type library, and Workbook is the name of a class defined in that library. Same for Worksheet. Since Excel isn't referenced, VBA can't resolve these types, and you get a compile error.

您需要使用后期绑定代码，即在黑暗中，没有 IntelliSense ，自动补全或参数quick-info，并且不要输入任何错误-以免遇到运行时错误438和1004.

You need to work with late-bound code, i.e. in the dark, without IntelliSense, autocompletion, or parameter quick-info, and without making any typos - lest you run into run-time error 438 and 1004.

后期绑定"是指在运行时已解析".每当您声明As Object时，就会发生以下情况:

"Late-bound" means "resolved at run-time". Whenever you declare something As Object, that's precisely what happens:

Dim wkbkXLBook As Object
Dim wkSheet As Object

除非引用Excel类型库，否则不能使用任何Excel类型.其中也包括任何xl*常量.

You can't use any of the Excel types unless you reference the Excel type library. That includes any xl* constant, too.

Dim Excel As Object

我强烈建议将此名称重命名为xlApp.

I'd warmly recommend renaming this to e.g. xlApp.

当心隐式对象引用:

Dim someRange As Object
Set someRange = xlApp.ActiveWorkbook.Worksheets("Sheet1").Range("A1")

上面的方法可以工作，但也会泄漏ActiveWorkbook对象，其Worksheets集合以及检索到的Worksheet对象；即使执行了xlApp.Quit和Set xlApp = Nothing之后，这些泄漏的对象仍可以(并且经常做)阻止EXCEL.EXE进程正确关闭:避免像这样使用双点引用对象.改为执行此操作:

The above will work, but will also leak the ActiveWorkbook object, its Worksheets collection, and the Worksheet object retrieved; these leaked objects can (and often do) prevent the EXCEL.EXE process from correctly shutting down, even after executing xlApp.Quit and Set xlApp = Nothing: avoid double-dot referencing objects like this. Do this instead:

Dim books As Object
Set books = xlApp.Workbooks

Dim wb As Object
Set wb = books("workbook name")

Dim wbSheets As Object
Set wbSheets = wb.Worksheets

Dim ws As Object
Set ws = wbSheets("sheet name")

Dim rng As Object
Set rng = ws.Range("A1")

每个涉及的对象都明确地作用于本地过程，一切都会好起来的.

With every object involved explicitly scoped to the local procedure, everything should be fine.

这篇关于如何在不设置引用的情况下从另一个Office应用程序打开Excel?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！