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

wwwbjwww

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使用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 个有数据。有用的字段与上述相同。

[ 本帖最后由 wwwbjwww 于 2010-11-25 23:39 编辑 ]

wbjjzhu

好资料，谢谢楼主分享！！

chury11

谢谢,请继续分享！

chury11

Dim sql As String, cnn As Object, rst As Object, i As Integer
    Set cnn = CreateObject("adodb.connection")
    Set rst = CreateObject("ADODB.Recordset")
    cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
    sql = "select b.存货编码, b.存货名称,(b.期初结存数量-a.期初结存数量) as 期初结存,(b.总计_入库数量-a.总计_入库数量) as 入库, (b.总计_出库数量-a.总计_出库数量) as 出库,(b.期末结存数量-a.期末结存数量) as 期末结存 from [财务$] as a right join [仓库$] as b on a.存货编码 = b.存货编码 "
    With ActiveSheet
        .Cells.ClearContents
        rst.Open sql, cnn, 1, 3
        For i = 0 To rst.Fields.Count -1
            .Cells(5, i+1) = rst.Fields.Item(i ).Name
             .Range("a6").CopyFromRecordset rst
        Next
    End With
    cnn.Close
    Set cnn = Nothing
+++++++++++++++++++++++++++++++++++++++++++
.Cells(5, i+1) = rst.Fields.Item(i ).Name中的i为何从0开始循环？
rst.Fields.Item(i ).Name的用法能讲讲吗？

[ 本帖最后由 chury11 于 2010-11-26 11:45 编辑 ]

AVEL

来学习一下。。

感谢楼主分享。

lhh175

路过，学习了

mossesho

来学习一下。。

感谢楼主分享。

pptamanegi

感谢楼主的发文,让小的能对ADO有初步的认识!!!
您的发文真的是我的福气阿!!!
感谢了,学习了~~~
让我又学到不少~THANKS!

yaojil

谢谢楼主分享 多了解一下 ADO 与 SQL

fandk

多谢楼主的总结