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



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

Excel工作表中的行和列与数据库中的行和列非常相似。ADO 让我们可以将Excel 工作簿看做和数据库一样,ADO的好处是可以不通过OPEN的方式访问工作簿,如此可以绕过不使用宏即关闭工作簿等陷井,也可以建立sql查询语句,快速搜索相关符合要求的记录,EXCEL毕竟不是关系数据库,当我们努力想把ADO的访问技术发挥到极致的时候,才发现对EXCEL,ADO也有很大的缺陷,而且是目前技术条件下无法解决的,发本文的目的就是想通过大家讨论ADO技术访问工作簿让我们更加清楚ADOEXCEL的使用现状.

ADO
Microsoft Jet OLE DB ProviderODBC 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 97Excel 2000Excel 2002 (XP) 工作簿(Excel 版本 8.09.010.0),应指定 Excel 8.0 版本,0710,13使用excel 12.0
③.
列标题:

默认为 Excel 数据源的第一行包含可用作字段名的列标题,如果认为不需要列标题,可以设置HDR=NoJET将字段命名为 F1F2 …等。表达式也可写成
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=11即为 True。如果没有列标题,则需要指定 FirstRowHasNames=0,其中 0 即为 False;驱动程序将字段命名为 F1F2 …等。
由于 ODBC 驱动程序中存在错误,目前指定 FirstRowHasNames 设置不起作用。换句话说,ODBC始终把指定数据源中的第一行作为字段名。
要扫描的行数:Excel不可能象关系数据库那样为 ADO 提供有关其数据的详细架构信息。驱动程序是通过扫描几行现有数据猜测各列的数据类型。默认为8,可以设置为1-16的整数值,或指定0,扫描所有现有行。
Excel ODBC
驱动程序(MDAC 2.1 和更高版本)始终扫描指定数据源中的前 8 行,以确定各列的数据类型。
使用这两种提供程序时的一些问题
1.
混用数据类型
①.ADO
是以猜测 Excel 工作表或范围中各列的数据类型确定字段数据类型(这不受 Excel 单元格格式设置的影响)。如果同一列中既有数字值,也有文本值,JetODBC 将返回占多数的类型的数据,对于占少数的数据类型,则会返回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 公式(以“=”开始)的单元格的记录集字段是只读的,不能对其进行编辑。ExcelODBC 连接默认是只读的,但可在连接设置中另行指定。
有时,使用 ADO Recordset 对象的 AddNewUpdate 方法向Excel 表插入新数据行时,ADO 可能会将数据值插入错误的列。
删除 Excel 数据时,不能1次删除1整条记录,只能通过分别清空各个字段的内容来删除一条记录。 删除包含Excel 公式的单元格中的值时会出现错误信息: Operation is not allowed in this context. 电子表格中行删除完数据后,记录集空记录仍保留。
使用 ADOExcel 中插入文本数据时,文本值前面会出现单引号,这在后续处理数据时可能会出现问题。
4.
Excel 检索数据源结构(元数据)
使用 ADO 可以检索 Excel 数据源的结构,使用时JETodbc两种OLE 都返回很少的有用信息。使用 ADOOpenSchema 方法可检索此元数据,也可以使用更强大的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 数据类型 5adDouble
货币(ADO 数据类型 6adCurrency
逻辑或布尔值(ADO 数据类型 11adBoolean
日期(使用 Jet 时,为 ADO 数据类型 7adDate;使用ODBC 时为数据类型 135adDBTimestamp
文本(一种 ADO ad...Char 类型,例如,202adVarChar200adVarWChar,或相似类型)
①.
对于数字列,返回的 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 个有数据。有用的字段与上述相同。


附转载网址:http://blog.sina.com.cn/s/blog_5e2017250102vyfa.html

展开阅读全文

没有更多推荐了,返回首页