ADO在EXCEL中应用基础
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
创建ADO对象方法2：添加工程引用
这个方法首先通过VBE“工具”菜单-引用，在“引用”列表中找到
Microsoft ActiveX Data Objects 2.x Library 勾选后确定。应尽量选择高一点版本。 然后就可以使用下面的代码创建ADO对象：
Dim cnn As ADODB.Connection Set cnn = New ADODB.Connection 也可以在声明是直接创建，上面代码写为： Dim cnn As New ADODB.Connection
后面的ThisWorkbook.FullName表示数据源文件的全路径，这里是连接自身文件， 如果是其它EXCEL文件，只需要替换一下这儿的ThisWorkbook.FullName即可。
上面连接Hdr=Yes是系统的默认设置，所以一般不需要写出来，ConnectionString 属性的设置可以简化为： .ConnectionString = "Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
注意如果存在两个以上的“Extended Properties”，等号后面必须用引号引起 来，而且上面连接表达式中的分号是不能省略的。
如果不想先设置Connection对象的Provider和ConnectionString属性在打开连 接，也可以在使用Open方法打开连接的时候完成这些设置。上面的连接代码可以 写成这样：
创建ADO对象的方法使用上面的两种方法之一即可，第二种方法的好处是可以在编 辑代码时“自动列出对象成员”，后面的代码我们将采用这种方法。
现在，请在你的Sub Ado0中写入如下代码（后三行我们后面会用到）： Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim Sql As String Dim i As Integer Dim j As Integer
End With
上面with语句体中，第一句为cnn指定OLE DB驱动提供者为 Microsoft.Jet.OleDb.4.0，下面说明一下第二句： Extended Properties='Excel 8.0;Hdr=Yes; 表示要连接的数据源是EXCEL文件， Hdr=Yes表示后面对数据源进行查询时，将要查询的“表”区域的第一行做为表头 区，即每一列的第一行作为“字段名”，第二行起为数据区。如果Hdr=No，则表 示全部为数据区，这时需要“字段名”第一列系统默认为f1,第二列为f2，依此类 推。
大家记住一点：当我们打算使用ADO来管理EXCEL数据时，这个数据区域一定要可 以被看做“表格”，它的每一列要保证相同的类型，举个例子说，不能有些是日 期，而另外一些是文本或数字类型。
概念5：什么是SQL？ SQL：Stuctured Query Language 结构化查询语言 ADO管理数据，是通过连接OLE DB驱动来完成的（OLE Database这个词不用解释了 吧，大家看名字就知道是干什么活的），真正的数据管理者是OLE DB，管理嘛， 当然要使用语言了，OLE DB使用的语言就是SQL。所以，SQL对我们来说，是使用 OLE DB的核心，也就成为使用ADO的核心内容，你要发布管理数据的“命令”必须 使用SQL语言。不会SQL就无法管理数据，也就谈不上使用ADO。
概念1：什么是ADO？ ActiveX Data Objects：ActiveX 数据对象 我从未见过有人给出“ActiveX”的汉语翻译，不过仅从后面的两个英文单 词，我们已经可以知道ADO是一种数据对象。 数据对象嘛，其作用就是用来管理数据的。当然管理数据的不一定非得是数据对 象，数据对象也不是可以管理所有的数据。（绕口令呵）
步骤2：建立连接
创建了ADO对象后，首先要做的就是为Connection对象指定连接的数据源。
ADO建立连接是通过OLE DB进行的，OLE DB的驱动种类有很多，对EXCEL而言，支 持的OLE DB连接方式有两种： ODBC（Open Database Connectivety）开放数据库连接 JET（Joint Engine Technology）连接引擎技术
概念4：什么是关系数据库？
ADO管理的是数据，其实这里的数据通常情况下是“关系数据”，这些“关系数据 ”的集合称为关系数据库。
何谓“关系”，简而言之，即“表格”。
这样，关系数据库的含义就是由“表格”组成的数据库。
这样解释可能出乎很多朋友的意料，但这个解释肯定错不了。我不再去细说这个 “表格”，说的多了，只会让人糊涂。只说一些我们后面有用的： 表格的列一般称为字段，每一列（字段）都具有相同的类型 表格的行一般称为记录。一行称为一条记录。
但我还是要给大家介绍一下我所看的相关介绍，先要来了解另一个概念。
概念3：什么是OLE?
OLE是Object Linking and Embedding，对象链接与嵌入技术
OLE是封装了一些软件（对象）的库文件，这个库文件通常称为“部件”，它有几 个特征： （1）它是可运行代码 （2）它是可被其它外部应用程序调用的代码 （3）外部程序可以重复调用库中的代码，通常称为代码重用 大家可以看出，上面的三个特征都与“类”有关，这就是为什么说“类”是部件 的基础的原因。
Recordset 对象表示的是来自基本表或命令执行结果的记录全集。
上面的概念来自ADO的帮助文档，现在觉得抽象不要紧，关键是后面学会怎么用它 们就行。
创建ADO对象方法1：使用VBA的CreateObject函数。
Dim cnn As Object, rst As Object Set cnn = CreateObject("ADODB.Connection") Set rst = CreateObject("ADODB.Recordset") 上面语句为我们创建了两个ADO对象。
对数据的管理我们可以不使用任何对象，而只使用普通的代码来完成；也可以使 用数据对象来做，至于选用何种方式，主要取决于哪种方式更适合（有时也取决 于写代码者的偏好）！
问题是，怎么知道哪种方式更适合呢，当然你必要要了解各种方式，今天我们要 了解的是ADO！ 在这个概念中，我已经初步回答了ADO的作用。更多的进一步的回答我放在后面的 实战中：-）。
这里我们知道了ADO和SQL的关系了。
简单介绍SQL的历史。 SQL是关系数据库研究的产物，他是美国的一位博士于上世纪70年代最先提出，80 年代美国国家标准局（ANSI）制订发布了SQL美国国家标准，并被国际标准化组织 （ISO）所接受。这样，随着SQL标准地位的确定，很多数据库厂家都纷纷采用， SQL也就成了最流行的数据库语言。但各家在采用SQL，都对“标准”SQL进行了扩 充和改动，形成了很多“方言”，OLE DB采用的SQL也是方言之一。
第12课 ADO在EXCEL中应用基础（QEE用）
一、ADO的概念
这一部分是不得不讲，却又很难讲清的部分，当你以后能熟练使用ADO的时候， 你可能会把这些“概念”全部忘掉了，但如果你从未了解过ADO的这些概念， 它会始终困扰你，甚至影响你继续学习的信心。
但是要想完全真正理解这些概念，对我们几乎是不可能的。我的理论水平也非常 有限，下面只就ADO涉及的最常用的概念给出一些尽可能“易于理解”的说明，首 先声明：这些概念不是官方的严谨叙述，更象是“演义”，目的只是让初学的朋 友知道“是那么回事”或者产生一个“朦朦胧胧的印象”，如果有对ADO相关理 论感性趣的朋友，请参考MSDN（Microsoft Developer Network）的文档。
ODBC是早期的OLE DB驱动，它对系统的底层依赖和限制过多，且以“效率最低” 著称，相对而言，JET更为灵活高效，所以我们只介绍JET连接。不是ODBC没用， 对早期的某些数据源，因为JET没有提供支持，或者你的机器上没有JET驱动（可 能性不大），还是必须使用ODBC的。
建立连接是通过使用Connection对象的Open方法来完成的，在打开连接前，需要 先设置Connection对象的Provider和ConnectionString属性。下面代码为前面创 建的cnn对象建立连接：
概念2：什么是ActiveX？
在很早以前，我曾经问过我的一个朋友：ActiveX是什么意思？他回答是：一种商 标的名字。
当时我确实注意到，ActiveX后面有一个 （R），我是学经济类专业的，知道 (R) 是注册商标的意思。所以在很长时间我不再去追究它的具体含义，商标的名字有 什么好研究的。
再后来，无意中看到了些关于ActiveX更多的介绍，现在，我还是觉得我朋友给我 的解释最好，大道至简！科学的东西从来都不复杂。
扯远了，赶紧回来。 那么OLE和ActiveX有什么关系呢？
当发展到网络时代的时候，OLE需要能够与Web浏览器交互，嵌入到网页中，随网 页传送到客户的浏览器上，并在客户端执行。这个时候，OLE的基础技术也有了发 展，就是我们常听说的COM（Component Object Model，部件对象模型），我们不 再去讨论COM了，不然就越说越远了。按照一般的升级命名原则，这时应该叫OLE 2.0，但微软给OLE改名了，它就是ActiveX。 所以可以说，ActiveX其实就是OLE 2.0，或者是支持网页技术的OLE。 大家知道，由于互联网本身具有安全问题，访问速度远低于本地访问速度等一些 特殊性，ActiveX部件通常还有如下特征： （1）一般都提供“代码签名”或要求注册使用，以保证其安全性。 （2）占用内存尽可能小，效率（速度）尽可能高。但这也不是绝对的，随着网速 的提升，很多ActiveX部件的制作要求也在下降。 到这儿，大家再统起来看看ActiveX Data Objects，是不是对这几个词有了一个 是“朦胧”的印象了~~~
其它概念我们将在后面遇到时再讲。
请大家多看几遍。以后就可以放下这些概念问题，而把更多的注意力放在ADO的实 际应用上。5分钟后，我们进入ADO的实战。
ቤተ መጻሕፍቲ ባይዱ
问：WORD中邮件合并打印功能是不是就是用了您说的OLE? 答：是。大家看看,能理解多少就理解多少
二、ADO代码步骤
从现在起，我们需要同步互动。 请打开你下载的《模拟数据.xls》，进入VBE，插入一个模块，先写下这样一个框架 Sub Ado0()
End Sub
我们下面以“查询”为例介绍ADO的工作步骤。使用ADO工作共有五个步骤：
步骤1：创建ADO对象。
我们只介绍最常用两个ADO对象Connection和Recordset，Record（记录）对象表 示Recordset（记录集）对象中的一条记录，我们也会提到。
Connection 对象代表打开的、与数据源的连接。
With cnn .Provider = "Microsoft.Jet.OleDb.4.0" .ConnectionString = "Extended Properties='Excel 8.0;Hdr=Yes;';Data
Source=" & ThisWorkbook.FullName .Open
Open 方法 (ADO Connection) 打开到数据源的连接。
语法 connection.Open ConnectionString, UserID, Password, Options 参数 ConnectionString 可选，字符串，包含连接信息。 UserID 可选，字符串，包含建立连接时所使用用户名。 Password 可选，字符串，包含建立连接时所使用密码。 Options 可选，决定该方法是连接是异步还是同步返回。
cnn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
这种写法看起来更简洁，我们后面将采用这种写法。请将上行代码写到你Sub Ado0中
这个方法首先通过VBE“工具”菜单-引用，在“引用”列表中找到
Microsoft ActiveX Data Objects 2.x Library 勾选后确定。应尽量选择高一点版本。 然后就可以使用下面的代码创建ADO对象：
Dim cnn As ADODB.Connection Set cnn = New ADODB.Connection 也可以在声明是直接创建，上面代码写为： Dim cnn As New ADODB.Connection
后面的ThisWorkbook.FullName表示数据源文件的全路径，这里是连接自身文件， 如果是其它EXCEL文件，只需要替换一下这儿的ThisWorkbook.FullName即可。
上面连接Hdr=Yes是系统的默认设置，所以一般不需要写出来，ConnectionString 属性的设置可以简化为： .ConnectionString = "Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
注意如果存在两个以上的“Extended Properties”，等号后面必须用引号引起 来，而且上面连接表达式中的分号是不能省略的。
如果不想先设置Connection对象的Provider和ConnectionString属性在打开连 接，也可以在使用Open方法打开连接的时候完成这些设置。上面的连接代码可以 写成这样：
创建ADO对象的方法使用上面的两种方法之一即可，第二种方法的好处是可以在编 辑代码时“自动列出对象成员”，后面的代码我们将采用这种方法。
现在，请在你的Sub Ado0中写入如下代码（后三行我们后面会用到）： Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim Sql As String Dim i As Integer Dim j As Integer
End With
上面with语句体中，第一句为cnn指定OLE DB驱动提供者为 Microsoft.Jet.OleDb.4.0，下面说明一下第二句： Extended Properties='Excel 8.0;Hdr=Yes; 表示要连接的数据源是EXCEL文件， Hdr=Yes表示后面对数据源进行查询时，将要查询的“表”区域的第一行做为表头 区，即每一列的第一行作为“字段名”，第二行起为数据区。如果Hdr=No，则表 示全部为数据区，这时需要“字段名”第一列系统默认为f1,第二列为f2，依此类 推。
大家记住一点：当我们打算使用ADO来管理EXCEL数据时，这个数据区域一定要可 以被看做“表格”，它的每一列要保证相同的类型，举个例子说，不能有些是日 期，而另外一些是文本或数字类型。
概念5：什么是SQL？ SQL：Stuctured Query Language 结构化查询语言 ADO管理数据，是通过连接OLE DB驱动来完成的（OLE Database这个词不用解释了 吧，大家看名字就知道是干什么活的），真正的数据管理者是OLE DB，管理嘛， 当然要使用语言了，OLE DB使用的语言就是SQL。所以，SQL对我们来说，是使用 OLE DB的核心，也就成为使用ADO的核心内容，你要发布管理数据的“命令”必须 使用SQL语言。不会SQL就无法管理数据，也就谈不上使用ADO。
概念1：什么是ADO？ ActiveX Data Objects：ActiveX 数据对象 我从未见过有人给出“ActiveX”的汉语翻译，不过仅从后面的两个英文单 词，我们已经可以知道ADO是一种数据对象。 数据对象嘛，其作用就是用来管理数据的。当然管理数据的不一定非得是数据对 象，数据对象也不是可以管理所有的数据。（绕口令呵）
步骤2：建立连接
创建了ADO对象后，首先要做的就是为Connection对象指定连接的数据源。
ADO建立连接是通过OLE DB进行的，OLE DB的驱动种类有很多，对EXCEL而言，支 持的OLE DB连接方式有两种： ODBC（Open Database Connectivety）开放数据库连接 JET（Joint Engine Technology）连接引擎技术
概念4：什么是关系数据库？
ADO管理的是数据，其实这里的数据通常情况下是“关系数据”，这些“关系数据 ”的集合称为关系数据库。
何谓“关系”，简而言之，即“表格”。
这样，关系数据库的含义就是由“表格”组成的数据库。
这样解释可能出乎很多朋友的意料，但这个解释肯定错不了。我不再去细说这个 “表格”，说的多了，只会让人糊涂。只说一些我们后面有用的： 表格的列一般称为字段，每一列（字段）都具有相同的类型 表格的行一般称为记录。一行称为一条记录。
但我还是要给大家介绍一下我所看的相关介绍，先要来了解另一个概念。
概念3：什么是OLE?
OLE是Object Linking and Embedding，对象链接与嵌入技术
OLE是封装了一些软件（对象）的库文件，这个库文件通常称为“部件”，它有几 个特征： （1）它是可运行代码 （2）它是可被其它外部应用程序调用的代码 （3）外部程序可以重复调用库中的代码，通常称为代码重用 大家可以看出，上面的三个特征都与“类”有关，这就是为什么说“类”是部件 的基础的原因。
Recordset 对象表示的是来自基本表或命令执行结果的记录全集。
上面的概念来自ADO的帮助文档，现在觉得抽象不要紧，关键是后面学会怎么用它 们就行。
创建ADO对象方法1：使用VBA的CreateObject函数。
Dim cnn As Object, rst As Object Set cnn = CreateObject("ADODB.Connection") Set rst = CreateObject("ADODB.Recordset") 上面语句为我们创建了两个ADO对象。
对数据的管理我们可以不使用任何对象，而只使用普通的代码来完成；也可以使 用数据对象来做，至于选用何种方式，主要取决于哪种方式更适合（有时也取决 于写代码者的偏好）！
问题是，怎么知道哪种方式更适合呢，当然你必要要了解各种方式，今天我们要 了解的是ADO！ 在这个概念中，我已经初步回答了ADO的作用。更多的进一步的回答我放在后面的 实战中：-）。
这里我们知道了ADO和SQL的关系了。
简单介绍SQL的历史。 SQL是关系数据库研究的产物，他是美国的一位博士于上世纪70年代最先提出，80 年代美国国家标准局（ANSI）制订发布了SQL美国国家标准，并被国际标准化组织 （ISO）所接受。这样，随着SQL标准地位的确定，很多数据库厂家都纷纷采用， SQL也就成了最流行的数据库语言。但各家在采用SQL，都对“标准”SQL进行了扩 充和改动，形成了很多“方言”，OLE DB采用的SQL也是方言之一。
第12课 ADO在EXCEL中应用基础（QEE用）
一、ADO的概念
这一部分是不得不讲，却又很难讲清的部分，当你以后能熟练使用ADO的时候， 你可能会把这些“概念”全部忘掉了，但如果你从未了解过ADO的这些概念， 它会始终困扰你，甚至影响你继续学习的信心。
但是要想完全真正理解这些概念，对我们几乎是不可能的。我的理论水平也非常 有限，下面只就ADO涉及的最常用的概念给出一些尽可能“易于理解”的说明，首 先声明：这些概念不是官方的严谨叙述，更象是“演义”，目的只是让初学的朋 友知道“是那么回事”或者产生一个“朦朦胧胧的印象”，如果有对ADO相关理 论感性趣的朋友，请参考MSDN（Microsoft Developer Network）的文档。
ODBC是早期的OLE DB驱动，它对系统的底层依赖和限制过多，且以“效率最低” 著称，相对而言，JET更为灵活高效，所以我们只介绍JET连接。不是ODBC没用， 对早期的某些数据源，因为JET没有提供支持，或者你的机器上没有JET驱动（可 能性不大），还是必须使用ODBC的。
建立连接是通过使用Connection对象的Open方法来完成的，在打开连接前，需要 先设置Connection对象的Provider和ConnectionString属性。下面代码为前面创 建的cnn对象建立连接：
概念2：什么是ActiveX？
在很早以前，我曾经问过我的一个朋友：ActiveX是什么意思？他回答是：一种商 标的名字。
当时我确实注意到，ActiveX后面有一个 （R），我是学经济类专业的，知道 (R) 是注册商标的意思。所以在很长时间我不再去追究它的具体含义，商标的名字有 什么好研究的。
再后来，无意中看到了些关于ActiveX更多的介绍，现在，我还是觉得我朋友给我 的解释最好，大道至简！科学的东西从来都不复杂。
扯远了，赶紧回来。 那么OLE和ActiveX有什么关系呢？
当发展到网络时代的时候，OLE需要能够与Web浏览器交互，嵌入到网页中，随网 页传送到客户的浏览器上，并在客户端执行。这个时候，OLE的基础技术也有了发 展，就是我们常听说的COM（Component Object Model，部件对象模型），我们不 再去讨论COM了，不然就越说越远了。按照一般的升级命名原则，这时应该叫OLE 2.0，但微软给OLE改名了，它就是ActiveX。 所以可以说，ActiveX其实就是OLE 2.0，或者是支持网页技术的OLE。 大家知道，由于互联网本身具有安全问题，访问速度远低于本地访问速度等一些 特殊性，ActiveX部件通常还有如下特征： （1）一般都提供“代码签名”或要求注册使用，以保证其安全性。 （2）占用内存尽可能小，效率（速度）尽可能高。但这也不是绝对的，随着网速 的提升，很多ActiveX部件的制作要求也在下降。 到这儿，大家再统起来看看ActiveX Data Objects，是不是对这几个词有了一个 是“朦胧”的印象了~~~
其它概念我们将在后面遇到时再讲。
请大家多看几遍。以后就可以放下这些概念问题，而把更多的注意力放在ADO的实 际应用上。5分钟后，我们进入ADO的实战。
ቤተ መጻሕፍቲ ባይዱ
问：WORD中邮件合并打印功能是不是就是用了您说的OLE? 答：是。大家看看,能理解多少就理解多少
二、ADO代码步骤
从现在起，我们需要同步互动。 请打开你下载的《模拟数据.xls》，进入VBE，插入一个模块，先写下这样一个框架 Sub Ado0()
End Sub
我们下面以“查询”为例介绍ADO的工作步骤。使用ADO工作共有五个步骤：
步骤1：创建ADO对象。
我们只介绍最常用两个ADO对象Connection和Recordset，Record（记录）对象表 示Recordset（记录集）对象中的一条记录，我们也会提到。
Connection 对象代表打开的、与数据源的连接。
With cnn .Provider = "Microsoft.Jet.OleDb.4.0" .ConnectionString = "Extended Properties='Excel 8.0;Hdr=Yes;';Data
Source=" & ThisWorkbook.FullName .Open
Open 方法 (ADO Connection) 打开到数据源的连接。
语法 connection.Open ConnectionString, UserID, Password, Options 参数 ConnectionString 可选，字符串，包含连接信息。 UserID 可选，字符串，包含建立连接时所使用用户名。 Password 可选，字符串，包含建立连接时所使用密码。 Options 可选，决定该方法是连接是异步还是同步返回。
cnn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
这种写法看起来更简洁，我们后面将采用这种写法。请将上行代码写到你Sub Ado0中