VBA中使用ADO来处理Ecel数据

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