[转]VBA中使用ADO来处理Excel数据之现状

VBA中使用ADO来处理Excel数据之现状

Excel工作表中的行和列与数据库中的行 和列非常相似。ADO 让我们可以将 Excel 工作簿看做和数据库一样,用ADO的好处是可以不通过OPEN的方式访问工作簿,如此可以绕过不使用宏即关闭工作簿等陷井,也可以建立sql查询语句,快速搜索相关符合要求的记录,但EXCEL毕竟不是关系数据库,当我们努力想把ADO的访问技术发挥到极致的时候,才发现对EXCEL,ADO也有很大的缺陷,而且是目前技术条件下无法解决的,发本文的目的就是想通过大家讨论ADO技术访问工作簿让我们更加清楚ADO对EXCEL的使用现状. ADO 有 Microsoft Jet OLE DB Provider 和 ODBC Drivers两种方式连接到 Excel 数据文件。 ODBC 是一种底层的访问技术，因此，ODBC API 可以是客户应用程序能从底层设置和控制数据库，完成一些高级数据库技术无法完成的功能；但不足之处由于ODBC只能用于关系型数据库，使得利用ODBC很难访问对象数据库及其他非关系数据库。但一些古董级的东东仍得靠它,其他引挚可能走得太快了已经不能支持了. ADO:全名: ACTIVEX DATA OBJECTS,所谓active英语名瞧一瞧就知道是商贸中要注册的东东,何为要注册,得从oledb1.0说起,那时还没网络,网络都没安全度就可了,网格时代访问数据库安全起见,就得先注册一把,通俗的说就是oledb 2.0,无非换了个名 Jet,可以说是ODBC的传人，武功更胜一筹，先且就这样定义一把吧。 1. Jet 连接字符串 Dim cn as ADODB.Connection Set cn = New ADODB.Connection With cn     .Provider = "Microsoft.Jet.OLEDB.4.0"     .ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _     "Extended Properties=Excel 8.0;"     .Open End With ①.程序版本要求：必须使用 Jet 4.0 提供程序 ②.Excel 版本： 对于 Excel 95 工作簿（Excel 版本 7.0），应指定 Excel 5.0；对于 Excel 97、Excel 2000 或 Excel 2002 (XP) 工作簿（Excel 版本 8.0、9.0 和 10.0），应指定 Excel 8.0 版本,07及10,13使用excel 12.0 ③.列标题： 默认为 Excel 数据源的第一行包含可用作字段名的列标题,如果认为不需要列标题，可以设置 HDR=No；JET将字段命名为 F1、F2 …等。表达式也可写成 DB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False; Data Source=" & FileName & ";Extended Properties='Excel 8.0;IMEX=1;HDR=no'"     2. ODBC 驱动程序的两种表达方式 ①.不带DSN连接字符串 Dim cn as ADODB.Connection Set cn = New ADODB.Connection With cn     .Provider = "MSDASQL"     .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _     "DBQ=C:\MyFolder\MyWorkbook.xls; ReadOnly=False;"     .Open End With ②.使用带有 DSN 的连接字符串 Dim cn as ADODB.Connection Set cn = New ADODB.Connection With cn     .Provider = "MSDASQL"     .ConnectionString = "DSN=MyExcelDSN;"     .Open End With 1.列标题：默认为 Excel 数据源的第一行包含可用作字段名的列标题。如果不是这种情况，则必须将该设置关闭，否则，第一行数据将会“消失”，而被用作字段名称。这可通过向连接字符串添加可选的 FirstRowHasNames= 设置来完成。默认情况下 FirstRowHasNames=1， 1即为 True。如果没有列标题，则需要指定 FirstRowHasNames=0，其中 0 即为 False；驱动程序将字段命名为 F1、F2 …等。 由于 ODBC 驱动程序中存在错误，目前指定 FirstRowHasNames 设置不起作用。换句话说，ODBC始终把指定数据源中的第一行作为字段名。 要扫描的行数：Excel不可能象关系数据库那样为 ADO 提供有关其数据的详细架构信息。驱动程序是通过扫描几行现有数据猜测各列的数据类型。默认为8行,可以设置为1-16的整数值，或指定0，扫描所有现有行。 Excel ODBC 驱动程序（MDAC 2.1 和更高版本）始终扫描指定数据源中的前 8 行，以确定各列的数据类型。 使用这两种提供程序时的一些问题 1.混用数据类型 ①.ADO 是以猜测 Excel 工作表或范围中各列的数据类型确定字段数据类型（这不受 Excel 单元格格式设置的影响）。如果同一列中既有数字值，也有文本值，Jet 和 ODBC 将返回占多数的类型的数据，对于占少数的数据类型，则会返回 NULL（空）值。如果该列中两种类型数据的数量相等，提供程序将优先选择数字型数据，放弃文本型数据。 如果列中包含不同类型的值，可以将数字值存储为文本，在需要时再使用 VAL()等函数还原为数字。 也可以在连接字符串的“扩展属性”中使用“IMEX=1”来启用导入模式。 在 Excel 工作簿受密码保护时，即使在连接设置中提供了正确的密码，也无法访问excel，并出现错误提示： Could not decrypt file. 2.数据源的指定 ①.三种方式 • 整张工作表。 • 工作表上的命名单元格区域。 • 工作表上的未命名单元格区域。 若要指定一张工作表作为记录源，使用该工作表的名称带美元字符，并套方括号,也是用于表示未知数据库对象名的标准约定,例如： strQuery = "SELECT * FROM [Sheet1$]" 也可以使用键盘上波形符 (~) 下的斜单引号字符 (`) strQuery = "SELECT * FROM `Sheet1$`" 指定工作表时应注意的是：提供程序认为数据表从指定工作表上最左上方的非空单元格开始。 ②.若要指定命名的单元格区域作为记录源，只需定义名称。例如： strQuery = "SELECT * FROM MyRange" 指定工作表作为记录源时，提供程序将新记录添加到工作表中现有记录的下面。指定区域（命名或未命名区域）时，Jet 也将新记录添加到区域中现有记录的下面。但是，如果对原区域重新执行查询，则得到的记录集不包含新添加到该区域外的记录。 ③.指定未命名区域 指定未命名的单元格区域作为记录源时，在工作表名的后面加上用标准 Excel 行/列表示法表示的区域，并用方括号将其括起。例如： strQuery = "SELECT * FROM [Sheet1$A1:B10]" 3.使用ADO 方法编辑 Excel 数据。 对应于 Excel 工作表中包含 Excel 公式（以“=”开始）的单元格的记录集字段是只读的，不能对其进行编辑。 Excel 的 ODBC 连接默认是只读的，但可在连接设置中另行指定。 有时，使用 ADO Recordset 对象的 AddNew 和 Update 方法向 Excel 表插入新数据行时，ADO 可能会将数据值插入错误的列。 删除 Excel 数据时，不能1次删除1整条记录,只能通过分别清空各个字段的内容来删除一条记录。 删除包含 Excel 公式的单元格中的值时会出现错误信息： Operation is not allowed in this context. 电子表格中行删除完数据后，记录集空记录仍保留。 使用 ADO 在 Excel 中插入文本数据时，文本值前面会出现单引号,这在后续处理数据时可能会出现问题。 4.从 Excel 检索数据源结构（元数据） 使用 ADO 可以检索 Excel 数据源的结构,使用时JET和odbc两种 OLE 都返回很少的有用信息。使用 ADO的 OpenSchema 方法可检索此元数据，也可以使用更强大的 ADOX来检索元数据。但由于 Excel 数据源，“表”既可以是工作表也可以是命名区域，而“字段”则是几种有限的一般数据类型之一，所以这一附加的功能没有什么用处。 查询表信息 关系数据库提供较多种对象（表、视图、存储过程等），Excel 数据源仅提供相当于表的对象，它由工作表和定义的命名区域组成。命名区域被视为“表”，而工作表被视为“系统表”。除“table_type”属性外，检索不到太多有用的表信息。下列代码常用来检索工作簿中可用表的列表： Set rs = cn.OpenSchema(adSchemaTables) Jet 返回的记录集包含9个字段，但其中只有4个字段有数据： • table_name • table_type（“表”或“系统表”） • date_created • date_modified ODBC 返回的记录集也包含9个字段，但其中只有3个字段有数据： • table_catalog (该工作簿所在的文件夹) • table_name • table_type 5.查询字段信息 Excel 数据源中字段数据类型有: • 数字（ADO 数据类型 5，adDouble） • 货币（ADO 数据类型 6，adCurrency） • 逻辑或布尔值（ADO 数据类型 11，adBoolean） • 日期（使用 Jet 时，为 ADO 数据类型 7，adDate；使用 ODBC 时为数据类型 135，adDBTimestamp） • 文本（一种 ADO ad...Char 类型，例如，202，adVarChar；200，adVarWChar，或相似类型） ①.对于数字列，返回的 numeric_precision 始终为 15（是 Excel 中的最大精度） ②.对于文本列，返回的 character_maximum_length 始终为 255（是 Excel 列中文本的最大显示宽度，但不是最大长度）。除了 data_type 属性之外，得不到多少有用的字段信息。 ③.Jet 返回的记录集包含 28 个字段。对于数字字段，其中8个有数据；对于文本字段，其中9个有数据。有用的字段很可能是： • table_name • column_name • ordinal_position • data_type ④.ODBC 提供程序返回的记录集包含 29 个字段。对于数字字段，其中10个有数据；对于文本字段，其中 11 个有数据。有用的字段与上述相同。