如何通过 Visual Basic 或 VBA 使用 ADOX 处理 Excel 数据

文章翻译 文章翻译
文章编号: 303814 - 查看本文应用于的产品
展开全部 | 关闭全部

本文内容

概要

用于数据定义语言和安全的 ActiveX 数据对象扩展 (ADOX) 为 ActiveX 数据对象 (ADO) 开发人员提供了数据访问对象 (DAO) 所具有的功能,使开发人员能够以编程方式处理数据库、表、字段和其他数据库对象。自从 Microsoft OLE DB Provider for Jet 4.0 版(此软件支持 Jet 引擎在 Microsoft Access 以外的桌面数据库中使用的 ISAM 驱动程序)发行以后,ADO 开发人员已经能够在 Microsoft Excel 工作簿和工作表中使用 ADO 和 ADOX 两者。

但是,由于 Excel 工作表不是普通的数据库,ADO 在 Excel 中使用时有一些限制。具体说来,许多 ADOX 方法在 Excel 中使用时无效或者会产生意外结果。 本文记录了许多 ADOX 和 Excel 限制。有关在 Excel 中使用 ADO 的其他信息,请单击下面的文章编号,查看 Microsoft 知识库文章:
257819 HOWTO:Use ADO with Excel Data from Visual Basic or VBA(HOWTO:在 Visual Basic 或 VBA 中使用 ADO 与 Excel 数据)
本文分为以下几部分:

备注:本文在 Microsoft Windows 2000 Server Service Pack 2 (SP2) 上用 Microsoft Data Access Components (MDAC) 2.6 SP1、Microsoft Visual Basic 6 SP5 和 Microsoft Excel 2002 (XP) 进行了测试。本文可能未考虑或讨论用户在不同版本的 Windows、MDAC、Visual Basic 或 Excel 中可能观察到的不同行为。

更多信息

ADOX Catalog

在 ADOX 对象模型中,Catalog 对象表示数据库或 Excel 中的工作簿。
  • 可以使用 ADO Connection 对象或连接字符串创建或打开 Catalog 对象。下面的代码示例说明了如何将 Book1.xls 作为 ADOX Catalog打开:
    Dim cat As ADOX.Catalog
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & App.Path & "\book1.xls;Extended Properties=Excel 8.0"
    					
  • Catalog 对象有一个Create 方法,但没有Delete 方法。

创建 Catalog

不能使用 ADOXCatalog 对象的 Create 方法创建新的 Excel 工作簿。如果尝试这样做,将收到以下错误信息:
“Operation is not supported for this type of object”(对象不支持此操作)。
不过,可以通过下述方法创建新的 Excel 工作簿:为 Catalog 指定新的工作簿文件名,并使用下面讨论的技术在新的 Catalog 中附加至少一个 Table。此方法只是用您作为 ADOX 表创建的工作表来创建新工作簿;换言之,将不添加通过 ExcelOptions(选项)对话框为新 Excel 工作簿指定的默认数量的空白表。

ADOX 表

ADOX Table 对象对应于可作为数据容器的 Excel 对象(即工作表或范围)之一。

创建表

可以使用 ADOX 在 Excel 中创建新表。如果不同时创建列,则无法创建 Table,因此“ADOX 列”部分显示了代码示例。
  • 创建新的 ADOX 表时,不仅会创建一个新工作表,而且会在这个新工作表上用指定的名称创建一个新的指定范围。新的指定范围仅包括列标题。例如,如果创建名为 MyTable 的两列表(工作表),则还会创建一个名为 MyTable 的范围(被定义为 MyTable!$A$1:$B$1)。
  • 指定的表名中可以有空格;但是,ADOX 会将您创建的工作表和范围名称中的空格替换为下划线。
  • 创建的新工作表将作为工作簿中的最后一张表,位于在 Excel “选项”中为新工作簿指定的默认数量的空白表后。
  • 当工作簿在 Excel 中打开时,如果试图创建新表,将收到以下错误信息:
    “Unspecified error”(未定错误)。
  • 如果试图创建未定义列的表,看起来似乎是成功的,但实际上并未创建任何东西。
  • 如果试图更改现有表的名称,将收到以下错误信息:
    “Operation is not supported for this type of object”(对象不支持此操作)。

删除表

在 Excel 中,不论 ADOX 是否创建了表(工作表)或指定的范围,都不能使用 ADOX 将其删除。
  • 如果试图删除工作表 (MyTable$),看起来似乎是成功的,没有错误,但该工作表仍保留在工作簿中。可以重复调用 Delete 方法而不会引发错误,但这是无效的。
  • 如果试图删除范围 (MyTable),看起来似乎是成功的,没有错误,但该范围仍保留在工作簿中。但是,如果试图再次删除该范围,将收到错误 3265:
    “Item cannot be found in the collection corresponding to the requested name or ordinal”(在对应所请求的名称或序号的集合中找不到项目)。
  • 如果试图删除指定的范围,指定范围定义将完整地保留下来,但范围中的所有数据都将被删除。

ADOX 列

ADOX 列对应于数据库中的列或字段并整齐地对应于 Excel 工作表中的列。

用于 Excel 的 ADO 数据类型

ADO 能够识别可用于创建列的 Excel 数据源中的六种数据类型:

  • adDouble,类型 5
  • adDate,类型 7
  • adCurrency,类型 6
  • adBoolean,类型 11
  • adVarWChar,类型 202
  • adLongVarWChar ("memo"),类型 203

常规列

  • 只要先前创建的表不包含任何数据行,就可以将其他列附加到该表。
  • 指定的列名中可以有空格。
  • ADOX 在创建列标题时,总是会在前面加上一个单引号 ('),例如 'F1、'Col1、'Col2。不过,由于检索名称时不包括这个引号,这似乎不会在以后导致问题。

创建表时创建列

在使用 ADOX 创建新表时创建列的效果最佳。

下面的代码创建一个含有两列(一个数字列和一个文本列)的新表(工作表):
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	"Data Source=" & App.Path & "\book1.xls;Extended Properties=Excel 8.0"
    Set tbl = New ADOX.Table
    tbl.Name = "TestTable"
    Set col = New ADOX.Column
    With col
        .Name = "Col1"
        .Type = adDouble
    End With
    tbl.Columns.Append col
    Set col = Nothing
    Set col = New ADOX.Column
    With col
        .Name = "Col2"
        .Type = adVarWChar
    End With
    tbl.Columns.Append col
    cat.Tables.Append tbl
				

在现有工作表中创建列

向预先存在的空白工作表中附加列将产生不寻常的结果。当第一个 ADOX 列附加到表的 Columns 集合时,ADOX 首先在工作表的列 A 中创建一个名为 F1 的列标题,然后在工作表的列 B 中创建新的 ADOX 列标题。因此,附加两个新列的 ADOX 代码产生三个列标题。如果在附加第一个 ADOX 列之前,试图从 Columns 集合中删除这个多余的第一列,看起来似乎是成功的,但其实仍然已创建了这个多余的列。

删除列

在 Excel 中,不能从 ADOX 表中删除列。
  • 如果列中没有数据,这看起来似乎是成功的,没有错误,但仍然会保留列和列标题。
  • 如果列中有数据,删除操作将失败并出现以下错误信息:
    “Invalid operation”(无效操作)。

更改现有列的数据类型

列附加到表之后,如果试图将列的 .Type 属性设置为另一数据类型,将收到以下错误信息:
“Operation is not allowed in this context”(此上下文中不允许操作)。

向 Excel 表和列中添加数据

当您在 Visual Basic 设计环境中用 Excel 测试 ADO 代码时,首次运行记录集操作时可能会收到以下错误信息:
“Selected collating sequence not supported by the operating system”(操作系统不支持选择的排序)。
可以忽略此错误信息;编译应用程序后不再出现此错误。有关其他信息,请单击下面的文章编号,查看 Microsoft 知识库文章:
246167 PRB:Collating Sequence Error Opening ADODB Recordset the First Time Against an Excel XLS(首次在 Excel XLS 中打开 ADODB 记录集时的排序错误)

向 ADOX 所创建的 Excel 列中添加数据

当您使用 ADO 在 Excel 中用 ADOX 创建的表和列中插入或修改数据时,ADO 将考虑您为那些列指定的类型,但我们不清楚这些信息存储在何处。
  • 所有的文本字符串值在存储时,前面都会带一个单引号。不过,由于检索值时不包括这个引号,这似乎不会在以后导致问题。
  • 如果试图在被定义为数字的列中插入文本字符串,或者将数字插入文本列,将收到错误 80040e21:
    “Multiple-step operation generated errors”(多步操作产生错误)。“Check each status value”(检查每个状态值)。

向普通 Excel 列中添加数据

当使用 ADO 在不是用 ADOX 创建或修改的 Excel 工作表或区域中插入或修改数据时,可以插入的数据类型是不受限制的。
  • 存储文本字符串时,开头不加单引号。
  • 数字按左对齐方式显示。在 Excel 2002 中,数字带有一个“智能标签”标记,用以警告“数字是以文本存储的”。在以后处理数据时,这可能会导致问题,特别是当 Excel 中存储的数据是用于数值分析时。

使用 SQL CREATE、ALTER 和 DROP 语句

对 Excel 数据库对象运行 Microsoft SQL 数据定义语言 (DDL) 语句(如 CREATE、ALTER 和 DROP)(通过 ADOConnection )可以产生与上述的相应 ADOX 方法相同的结果。

结论

  • 当同时创建新表及其所含的列时,最适合用 ADOX 操作 Excel。
  • 当数据存储在用 ADOX 创建的工作表中时,最适合用 ADO 操作 Excel 数据。

属性

文章编号: 303814 - 最后修改: 2004年3月25日 - 修订: 1.4
这篇文章中的信息适用于:
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
关键字:?
kbhowto kbiisam kbdatabase KB303814
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润负任何责任。

提供反馈

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com