ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 31599|回复: 32

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

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2010-11-25 23:38 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:ADO技术
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 编辑 ]

TA的精华主题

TA的得分主题

发表于 2010-11-26 08:12 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-11-26 11:39 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
谢谢,请继续分享!

TA的精华主题

TA的得分主题

发表于 2010-11-26 11:44 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
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 编辑 ]

TA的精华主题

TA的得分主题

发表于 2010-11-26 15:49 | 显示全部楼层
来学习一下。。

感谢楼主分享。

TA的精华主题

TA的得分主题

发表于 2010-11-26 16:00 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-1-18 23:17 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-3-15 15:19 | 显示全部楼层
感谢楼主的发文,让小的能对ADO有初步的认识!!!
您的发文真的是我的福气阿!!!
感谢了,学习了~~~
让我又学到不少~THANKS!

TA的精华主题

TA的得分主题

发表于 2011-3-15 17:42 | 显示全部楼层
谢谢楼主分享 多了解一下 ADO 与 SQL

TA的精华主题

TA的得分主题

发表于 2011-9-1 13:43 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 07:45 , Processed in 0.049476 second(s), 9 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表