如何在 Access VBA 中引用 Excel 对象？

How to refer to Excel objects in Access VBA?

提问by Pablo

What declarations I have to make in order to be able to use Excel objects and constants from my Access 2007 VBA script?

为了能够使用 Access 2007 VBA 脚本中的 Excel 对象和常量，我必须进行哪些声明？

Dim wb As Workbook

or

或者

Set objExcelApp = New Excel.Application

or

或者

.Borders(xlEdgeBottom)

Thanks

谢谢

采纳答案by marg

First you need to set a reference (Menu: Tools->References) to the Microsoft Excel Object Library then you can access all Excel Objects.

首先，您需要设置对 Microsoft Excel 对象库的引用（菜单：工具 -> 引用），然后您可以访问所有 Excel 对象。

After you added the Reference you have full access to all Excel Objects. You need to add Excel in front of everything for example:

添加引用后，您可以完全访问所有 Excel 对象。您需要在所有内容前面添加 Excel，例如：

Dim xlApp as Excel.Application

Let's say you added an Excel Workbook Object in your Form and named it xLObject.

假设您在表单中添加了一个 Excel 工作簿对象并将其命名为 xLObject。

Here is how you Access a Sheet of this Object and change a Range

这是访问此对象的工作表并更改范围的方法

Dim sheet As Excel.Worksheet
Set sheet = xlObject.Object.Sheets(1)
sheet.Range("A1") = "Hello World"

(I copied the above from my answer to this question)

（我从我对这个问题的回答中复制了上面的内容）

Another way to use Excel in Access is to start Excel through a Access Module (the way shahkalpesh described it in his answer)

在 Access 中使用 Excel 的另一种方法是通过 Access 模块启动 Excel（shahkalpesh 在他的回答中描述的方式）

回答by David-W-Fenton

I dissent from both the answers. Don't create a reference at all, but use late binding:

我不同意这两个答案。根本不要创建引用，而是使用后期绑定：

  Dim objExcelApp As Object
  Dim wb As Object

  Sub Initialize()
    Set objExcelApp = CreateObject("Excel.Application")
  End Sub

  Sub ProcessDataWorkbook()
     Set wb = objExcelApp.Workbooks.Open("path to my workbook")
     Dim ws As Object
     Set ws = wb.Sheets(1)

     ws.Cells(1, 1).Value = "Hello"
     ws.Cells(1, 2).Value = "World"

     'Close the workbook
     wb.Close
     Set wb = Nothing
  End Sub

You will note that the only difference in the code above is that the variables are all declared as objects and you instantiate the Excel instance with CreateObject().

您会注意到上面代码的唯一区别是变量都声明为对象，并且您使用 CreateObject() 实例化 Excel 实例。

This code will run no matter what version of Excel is installed, while using a reference can easily cause your code to break if there's a different version of Excel installed, or if it's installed in a different location.

无论安装的是哪个版本的 Excel，此代码都会运行，而如果安装了不同版本的 Excel 或安装在不同的位置，则使用引用很容易导致代码中断。

Also, the error handling could be added to the code above so that if the initial instantiation of the Excel instance fails (say, because Excel is not installed or not properly registered), your code can continue. With a reference set, your whole Access application will fail if Excel is not installed.

此外，错误处理可以添加到上面的代码中，以便如果 Excel 实例的初始实例化失败（例如，因为 Excel 未安装或未正确注册），您的代码可以继续。使用参考集，如果未安装 Excel，您的整个 Access 应用程序将失败。

回答by shahkalpesh

Inside a module

模块内部

Option Explicit
dim objExcelApp as Excel.Application
dim wb as Excel.Workbook

sub Initialize()
   set objExcelApp = new Excel.Application
end sub

sub ProcessDataWorkbook()
    dim ws as Worksheet
    set wb = objExcelApp.Workbooks.Open("path to my workbook")
    set ws = wb.Sheets(1)

    ws.Cells(1,1).Value = "Hello"
    ws.Cells(1,2).Value = "World"

    'Close the workbook
    wb.Close
    set wb = Nothing
end sub

sub Release()
   set objExcelApp = Nothing
end sub