如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据.docx

如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据.docx

《如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据.docx》由会员分享，可在线阅读，更多相关《如何在 Visual Basic 或 VBA 中使用 ADO 来处理 Excel 数据.docx（28页珍藏版）》请在冰豆网上搜索。

如何在VisualBasic或VBA中使用ADO来处理Excel数据

如何在VisualBasic或VBA中使用ADO来处理Excel数据

微软帮助和支持

查看本文应用于的产品

本页概要

更多信息

简介

使用ADO连接到Excel

如何使用MicrosoftJetOLEDBProvider

使用MicrosoftOLEDBProviderforODBCDrivers

使用这两种OLEDB提供程序时都应考虑的问题

使用ADO检索和编辑Excel数据

如何选择数据

使用代码选择Excel数据

使用ADO数据控件选择Excel数据

使用数据环境命令选择Excel数据

如何更改Excel数据：

编辑、添加和删除

从Excel检索数据源结构（元数据）

查询表信息

查询字段信息

枚举表和字段及其属性

使用数据视图窗口

Excel的限制

参考

展开全部|关闭全部

概要本文介绍如何使用ActiveXDataObjects（ADO）来处理数据源为MicrosoftExcel电子表格的数据。

本文还着重讨论特定于E...本文介绍如何使用ActiveXDataObjects（ADO）来处理数据源为MicrosoftExcel电子表格的数据。

本文还着重讨论特定于Excel的语法问题和限制。

本文不讨论OLAP或PivotTable技术，或Excel数据的其他特殊用法。

有关其他信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：

303814（）如何在VisualBasic或VBA中使用ADOX来处理Excel数据

回到顶端

更多信息简介MicrosoftExcel工作表中的行和列与数据库中的行和列非常相似。

只要用户记住MicrosoftExcel不是关系型数据库管理系统，并认识到...简介

MicrosoftExcel工作表中的行和列与数据库中的行和列非常相似。

只要用户记住MicrosoftExcel不是关系型数据库管理系统，并认识到这一事实所带来的限制，在许多情况下都可以利用Excel及其工具来存储和分析数据。

MicrosoftActiveXDataObjects让我们可以将Excel工作簿看做和数据库一样。

本文通过以下几节来讨论如何实现这一点：

使用ADO连接到Excel

使用ADO检索和编辑Excel数据

从Excel检索数据源结构（元数据）

注意：

本文中的测试是使用MicrosoftDataAccessComponents（MDAC）2.5在装有VisualBasic6.0ServicePack3和Excel2000的MicrosoftWindows2000上执行的。

对用户在使用不同版本的MDAC、MicrosoftWindows、VisualBasic或Excel时可能会观察到的不同现象，本文可能未确认或讨论。

回到顶端

使用ADO连接到Excel

ADO使用MDAC中以下两个OLEDB提供程序中的任何一个都可以连接到Excel数据文件。

MicrosoftJetOLEDBProvider—或—

MicrosoftOLEDBProviderforODBCDrivers

如何使用MicrosoftJetOLEDBProvider

Jet提供程序只需要两条信息就可以连接到Excel数据源：

路径（包括文件名），和Excel文件版本。

Jet提供程序使用连接字符串

DimcnasADODB.Connection

Setcn=NewADODB.Connection

Withcn

.Provider="Microsoft.Jet.OLEDB.4.0"

.ConnectionString="DataSource=C:

\MyFolder\MyWorkbook.xls;"&_

"ExtendedProperties=Excel8.0;"

.Open

EndWith

提供程序版本：

必须使用Jet4.0提供程序；Jet3.51提供程序不支持JetISAM驱动程序。

如果指定了Jet3.51提供程序，在运行时会出现以下错误信息：

Couldn'tfindinstallableISAM.

Excel版本：

对于Excel95工作簿（Excel版本7.0），应指定Excel5.0；对于Excel97、Excel2000或Excel2002（XP）工作簿（Excel版本8.0、9.0和10.0），应指定Excel8.0版本。

使用“数据链接属性”对话框的Jet提供程序

如果在应用程序中使用“ADO数据控件”或“数据环境”，就会出现数据链接属性对话框来收集必要的连接设置。

在提供程序选项卡上，选择Jet4.0提供程序；Jet3.51提供程序不支持JetISAM驱动程序。

如果指定了Jet3.51提供程序，在运行时会出现以下错误信息：

Couldn'tfindinstallableISAM.

在连接选项卡上，浏览到您的工作簿文件。

忽略“用户ID”和“密码”项，因为这些不适用于Excel连接。

（无法打开受密码保护的Excel文件作为数据源。

本文后面有关于此主题的详细信息。

）

在所有选项卡上，从列表中选择扩展属性，然后单击编辑值。

输入Excel8.0;，用分号（;）将其与任何其他已有的项分隔。

如果忽略此步骤，测试连接时将出现一条错误信息。

这是因为，如果不另行指定，Jet提供程序期望MicrosoftAccess数据库。

返回到连接选项卡，并单击测试连接。

将出现一个消息框，通知您测试已成功完成。

其他Jet提供程序连接设置

列标题：

默认情况下，系统认为Excel数据源的第一行包含可用作字段名的列标题。

如果不是这种情况，则必须将该设置关闭，否则，第一行数据将会“消失”，而被用作字段名称。

这可通过向连接字符串的扩展属性添加可选的HDR=设置来完成。

默认情况下（无需指定）是HDR=Yes。

如果没有列标题，则需要指定HDR=No；提供程序将字段命名为F1、F2等等。

因为扩展属性字符串现在包含了多个值，所以各值必须用双引号括起来，再加一对双引号，让VisualBasic将第一层引号中的内容当作字面值，如下例所示（为便于看清楚，添加了额外的空格）。

.ConnectionString="DataSource=C:

\MyFolder\MyWorkbook.xls;"&_

"ExtendedProperties=""Excel8.0;HDR=No;"""

使用MicrosoftOLEDBProviderforODBCDrivers

ODBC驱动程序的提供程序（本文为简便起见将其称为“ODBC提供程序”）也只需要两条信息就可以连接到Excel数据源：

驱动程序名，以及工作簿的路径和文件名。

重要说明：

Excel的ODBC连接默认为只读。

ADO记录集LockType属性设置不会覆盖此连接级别的设置。

如果想编辑数据，则必须将连接字符串或DSN配置中的ReadOnly设置为False。

否则，将出现以下错误信息：

Operationmustuseanupdateablequery.

使用没有DSN的连接字符串的ODBC提供程序

DimcnasADODB.Connection

Setcn=NewADODB.Connection

Withcn

.Provider="MSDASQL"

.ConnectionString="Driver={MicrosoftExcelDriver（*.xls）};"&_

"DBQ=C:

\MyFolder\MyWorkbook.xls;ReadOnly=False;"

.Open

EndWith

使用带有DSN的连接字符串的ODBC提供程序

DimcnasADODB.Connection

Setcn=NewADODB.Connection

Withcn

.Provider="MSDASQL"

.ConnectionString="DSN=MyExcelDSN;"

.Open

EndWith

使用“数据链接属性”对话框的ODBC提供程序

如果在应用程序中使用“ADO数据控件”或“数据环境”，就会出现数据链接属性对话框来收集必要的连接设置。

在提供程序选项卡上，选择MicrosoftOLEDBProviderforODBCDrivers。

在连接选项卡上，选择想使用的现有DSN，或选择使用连接字符串。

这将打开标准DSN配置对话框，收集必需的连接设置。

如果需要，请不要忘记取消选择默认只读设置，如上文所述。

返回连接选项卡，然后单击测试连接。

将出现一个消息框，通知您测试已成功完成。

其他ODBC提供程序连接设置

列标题：

默认情况下，系统认为Excel数据源的第一行包含可用作字段名的列标题。

如果不是这种情况，则必须将该设置关闭，否则，第一行数据将会“消失”，而被用作字段名称。

这可通过向连接字符串添加可选的FirstRowHasNames=设置来完成。

默认情况下（无需指定）为FirstRowHasNames=1，其中1=True。

如果没有列标题，则需要指定FirstRowHasNames=0，其中0=False；驱动程序将字段命名为F1、F2等等。

DSN配置对话框中没有此选项。

不过，由于ODBC驱动程序中存在错误，目前指定FirstRowHasNames设置不起作用。

换句话说，ExcelODBC驱动程序（MDAC2.1和更高版本）始终把指定数据源中的第一行作为字段名。

有关列标题错误的其他信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：

288343（）BUG：

ExcelODBC驱动程序忽略FirstRowHasNames或Header设置

要扫描的行数：

Excel不会像关系数据库那样为ADO提供有关其数据的详细架构信息。

因此，驱动程序必须至少扫描几行现有数据，才能有根据地猜测各列的数据类型。

“要扫描的行数”的默认值为八（8）行。

可以指定从一

（1）行到十六（16）行的整数值，或指定零（0），扫描所有现有行。

这可通过向连接字符串添加可选的MaxScanRows=设置，或在DSN配置对话框中更改要扫描的行数设置来完成。

但是，由于ODBC驱动程序中存在一个错误，所以目前指定“要扫描的行数”（MaxScanRows）设置不起作用。

换句话说，ExcelODBC驱动程序（MDAC2.1和更高版本）始终扫描指定数据源中的前8行，以确定各列的数据类型。

有关“要扫描的行数”错误的其他信息，包括一个简单的替代方法，请单击下面的文章编号，查看Microsoft知识库中相应的文章：

189897（）XL97：

使用ExcelODBC驱动程序数据截短到255个字符

其他设置：

如果是通过使用数据链接属性对话框来构造连接字符串的，那么您可能会注意到一些其他的扩展属性设置添加到了连接字符串中，而这些设置不一定有用，如：

...DefaultDir=C:

\WorkbookPath;DriverId=790;FIL=excel8.0;MaxBufferSize=2048;PageTimeout=5;

VisualBasic编辑器中的“CollatingSequence”（排序顺序）错误信息

使用某些版本的MDAC时，当您的程序在设计时第一次连接到Excel数据源时，VisualBasic设计环境中将出现以下错误信息：

Selectedcollatingsequencenotsupportedbytheoperatingsystem.

此信息仅出现在IDE中，而不会出现在编译好的程序中。

有关其他信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：

246167（）PRB：

首次在ExcelXLS中打开ADODB记录集时出现排序顺序错误

使用这两种OLEDB提供程序时都应考虑的问题

混用数据类型时应注意的事项

如上文所述，ADO必须猜测Excel工作表或范围中各列的数据类型。

（这不受Excel单元格格式设置的影响。

）如果同一列中既有数字值，也有文本值，会出现严重的问题。

Jet和ODBC提供程序将返回占多数的类型的数据，但对于占少数的数据类型，则会返回NULL（空）值。

如果该列中两种类型数据的数量相等，提供程序将优先选择数字型数据，放弃文本型数据。

例如：

在被扫描的八（8）行中，如果该列包含五（5）个数字值和三（3）个文本值，则提供程序将返回五（5）个数字和三（3）个空值。

在被扫描的八（8）行中，如果该列包含三（3）个数字值和五（5）个文本值，则提供程序将返回三（3）个空值和五（5）个文本值。

在被扫描的八（8）行中，如果该列包含四（4）个数字值和四（4）个文本值，则提供程序将返回四（4）个数字和四（4）个空值。

因此，如果列中包含不同类型的值，唯一的解决方法是将该列中的数字值存储为文本，然后在需要时使用VisualBasicVAL函数或同等功能的函数将其还原为数字。

作为解决只读数据问题的一种替代方法，可在连接字符串的“扩展属性”部分中使用“IMEX=1”这一设置来启用导入模式。

这可强制执行ImportMixedTypes=Text注册表设置。

但在此模式下，执行更新操作时可能会出现意外的结果。

有关此设置的其他信息，请单击下面的文章编号，查看Microsoft知识库中相应的文章：

194124（）PRB：

使用DAOOpenRecordset时Excel返回值为NULL

无法打开受密码保护的工作簿

如果您的Excel工作簿受密码保护，即使在连接设置中提供了正确的密码，也无法打开它以访问其数据，除非已在MicrosoftExcel应用程序中打开了该工作簿文件。

如果您尝试这样做，将出现以下错误信息：

Couldnotdecryptfile.

有关其他信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：

211378（）XL2000：

访问受密码保护的文件时，出现“CouldNotDecryptFile”（无法解密文件）错误

回到顶端

使用ADO检索和编辑Excel数据

本节讨论两种处理Excel数据的方式：

如何选择数据—和—

如何更改数据

如何选择数据

有几种选择数据的方法。

您可以进行以下操作：

使用代码选择Excel数据。

使用ADO数据控件选择Excel数据。

使用数据环境命令选择Excel数据。

使用代码选择Excel数据

Excel数据可能包含在工作簿中的以下一种对象内：

整张工作表。

工作表上的命名单元格区域。

工作表上的未命名单元格区域。

指定工作表

若要指定一张工作表作为记录源，请使用该工作表的名称，后面带有一个美元字符，并用方括号将其括起。

例如：

strQuery="SELECT*FROM[Sheet1$]"

也可以使用键盘上波形符（~）下的斜单引号字符（`）来分隔工作表名称。

例如：

strQuery="SELECT*FROM`Sheet1$`"

Microsoft建议使用方括号，这是用于表示未知数据库对象名的标准约定。

如果将美元符号和方括号全部省略，或只省略美元符号，将出现以下错误信息：

...theJetdatabaseenginecouldnotfindthespecifiedobject

如果使用美元符号而忽略了方括号，将会看到以下错误信息：

SyntaxerrorinFROMclause.

如果您尝试使用普通单引号，将出现以下错误信息：

Syntaxerrorinquery.Incompletequeryclause.

指定命名区域

若要指定命名的单元格区域作为记录源，只需使用定义的名称。

例如：

strQuery="SELECT*FROMMyRange"

指定未命名区域

若要指定未命名的单元格区域作为记录源，请在工作表名的后面加上用标准Excel行/列表示法表示的区域，并用方括号将其括起。

例如：

strQuery="SELECT*FROM[Sheet1$A1:

B10]"

指定工作表时应注意的事项：

提供程序认为数据表从指定工作表上最左上方的非空单元格开始。

换句话说，数据表可以从第3行，C列开始，这没有问题。

但在这种情况下，举个例子来说，就不能在数据左上方的A1单元格中键入工作表标题。

指定区域时应注意的事项：

指定工作表作为记录源时，提供程序将新记录添加到工作表中现有记录的下面（如果有可用空间）。

指定区域（命名区域或未命名区域）时，Jet也将新记录添加到区域中现有记录的下面（如果有可用空间）。

但是，如果对原区域重新执行查询，则得到的记录集不包含新添加到该区域外的记录。

使用2.5版之前的MDAC时，如果指定了命名区域，则无法将新记录添加到该区域定义的界限之外，否则将出现以下错误信息：

Cannotexpandnamedrange.

使用ADO数据控件选择Excel数据

在ADODC属性对话框的常规选项卡上指定Excel数据源的连接设置后，单击记录源选项卡。

如果选择的CommandType为adCmdText，则可以使用上文介绍的语法在命令文本对话框中输入一个SELECT查询。

如果选择的CommandType为adCmdTable，而且使用的是Jet提供程序，所选工作簿中的可用命名区域和工作表的名称都会显示在下拉列表中，命名区域排在前面。

此对话框会正确地在工作表名称后面加上美元符号，但不会添加必要的方括号。

结果，如果只选择工作表名称并单击确定，则稍后可能会收到以下错误信息：

SyntaxerrorinFROMclause.

您必需手动在工作表名称的前后加上方括号。

（此组合框允许进行编辑。

）如果使用的是ODBC提供程序，则在此下拉列表中只能看到命名区域。

但是，可以手动输入带有正确分隔符的工作表名称。

使用数据环境命令选择Excel数据

设置完Excel数据源的数据环境连接后，创建一个新的Command对象。

如果选择的数据源为SQL语句，则可以使用前面介绍的语法在文本框中输入一个查询。

如果选择的数据源为数据库对象，请在第一个下拉列表中选择表，如果您使用的是Jet提供程序，所选工作簿中的可用命名区域和工作表名称都会显示在该下拉列表中，命名区域排在前面。

（从该位置选择工作表名称时，不需要像在使用ADO数据控件时那样在工作表名称的前后加上方括号。

）如果使用的是ODBC提供程序，则在此下拉列表中只能看到命名区域。

但是，可以手动输入工作表名称。

如何更改Excel数据：

编辑、添加和删除

编辑

可以使用普通ADO方法来编辑Excel数据。

对应于Excel工作表中包含Excel公式（以“=”开始）的单元格的记录集字段是只读的，不能对其进行编辑。

记住Excel的ODBC连接默认是只读的，除非在连接设置中另行指定。

请参见上文中的“使用MicrosoftOLEDBProviderforODBCDrivers”一节。

添加

如果有可用空间，可以将记录添加到Excel记录源中。

但是，如果将新记录添加到了原来指定的区域之外，那么在对原来指定的区域重新进行查询时，将看不到这些记录。

请参见上文中“指定区域时应注意的事项”一节。

在某些情形中，使用ADORecordset对象的AddNew和Update方法向Excel表插入新数据行时，ADO可能会将数据值插入错误的列。

有关其他信息，请单击下面的文章编号，以查看Microsoft知识库中相应的文章：

314763（）FIX：

ADO将数据插入Excel中错误的列

删除

删除Excel数据时，受到的限制要比从关系数据源中删除数据时更多。

在关系数据库中，“行”除了表示一条“记录”外没有其他意义；但在Excel工作表中却不同。

可以删除字段（单元格）中的值。

但不能：

一次删除一整条记录，否则将出现以下错误信息：

DeletingdatainalinkedtableisnotsupportedbythisISAM.

只能通过分别清空各个字段的内容来删除一条记录。

删除包含Excel公式的单元格中的值，否则将出现以下错误信息：

Operationisnotallowedinthiscontext.

虽然电子表格中已被删除的数据原来所在的行现在是空行，但无法将其删除，而且记录集将继续显示对应于这些空行的空记录。

使用ADO编辑Excel数据时应注意的事项：

使用ADO在Excel中插入文本数据时，文本值前面带有一个单引号。

这在后面处理新数据时可能会导致问题。

回到顶端

从Excel检索数据源结构（元数据）

使用ADO可以检索有关Excel数据源（表和字段）的结构的数据。

虽然使用两种OLEDB提供程序时至少都返回相同数量（很少）字段的有用信息，但结果在两者之间仍有细微差别。

使用ADOConnection对象的OpenSchema方法可检索此元数据，该方法返回一个ADORecordset对象。

也可以使用更强大的MicrosoftActiveXDataObjectsExtensionsforDataDefinitionLanguagean