使用 ADO-SQL 处理 EXCEL 文件的程序架构(实例注释)

这是学习中的一个小结，其中必定存在错漏，需要各位朋友多多指正(hand)使用ADO连接文件自身时出现的问题及应对ADO-SQL语句对照使用－加载宏使用方式一的实例实例1：汉语中的“和”与逻辑运算中的“或”（一楼）实例2：ADO查询（一楼）实例3：带窗体进度条的筛选（一楼）实例4：通用多文件条件汇总（二楼）实例5：sql中的分类汇总、对字段使用函数（二楼）实例6：sql中的横向间隔显示字段值、排序（二楼）实例7：将没有打开的文件汇总到当前表中（三楼）（2006年4月10日新增）实例7：改进版：将查询结果保存在临时对象中，而不再使用工作表单元格作为临时区域（第3页23楼）（2006年4月19日新增）实例8：使用Union进行多表查询，并对月份表进行汇总（三楼）（2006年4月13日新增）实例9：使用IMEX=1对于数据类型不一致的列强制为文本型处理（第3页22楼）（2006年4月18日新增）实例10：对于大数量文件，不打开源文件及目标文件而进行重新分类（第4页39楼）（2006年6月6日新增）实例11：ADO批量处理ACCESS数据库（第5页48楼）（2006年6月14日新增）实例12查询ACCESS后的结果直接产生新文件实例13：批量导出DBF为对应Excel文件实例14：不打开文件而引用单列、单行或某个单元格（第6页57楼）实例15：格式相同的多文件多表去除空行后顺序汇入一张表中（第7页68楼）实例16：替代VLOOUP、SUMPRODUCT的VBA编码－多重Join的使用（第7页69楼）实例17：列出库中欠缺编号-NOTIN的应用（第8页71楼）实例18：用Union从字段不相同的两个Access表中筛选记录后按相似字段排序（第11页102楼）实例19：用Union进行忽略某种数值的汇总（第11页104楼）实例20：用ADO进行联动选择填写单据－ADO与LISTBOX（第11页107楼）实例21：多字段分类汇总－类数据透视表格式的汇总（第12页113楼）实例22：ADO-SQL语句对照使用（第12页117楼）实例23：Transform的使用－若要将某一字段的分类作为列标题，可使用Transform来实现（第13页128楼）实例24：使用ADO在Excel中与SQLSERVER进行数据传输（第14页140楼）使用方式二的实例实例2-1：标准查询格式（三楼）（2006年4月12日新增）实例2-2：对不打开的文件进行更新－无列标题时的处理（三楼）微软ADO使用帮助：=2&ID=70082&replyID=&skin=0相关资料：=2&ID=159747&replyID=349064&skin=0ADO-SQL语句对照使用－加载宏使用方法：工具－加载宏－浏览，找到下载的“汇总-查询-分表-合表.xla”，确定，确定，则在菜单上会增加一项“汇总-查询-分表-合表”，单击第一个菜单项：多字段分类汇总－类数据透视表格式即包含有“ADO-SQL语句对照”窗口：卸载方法：工具－加载宏，去掉“汇总-查询-分表-合表”前的对勾，确定即可。实例22：Transform的使用－若要将某一字段的分类作为列标题，可使用Transform来实现（第13页128楼）=2&replyid=626134&id=205703&page=1&skin=0&Star=2Subtotal()Application.ScreenUpdating=FalseSetCnn=CreateObject(adodb.connection)cnn.OpenProvider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties=Excel8.0;DataSource=&ActiveWorkbook.FullName[a4:d65536].ClearContentsSql=TRANSFORMCOUNT(通话类型)SELECT对方号码,SUM([时长(秒)])FROM[原始数据$]GROUPBY对方号码PIVOT通话类型[a4].CopyFromRecordsetCnn.Execute(Sql)cnn.CloseSetcnn=NothingRange(A3:E&[a65535].End(xlUp).Row).SortKey1:=Range(D3),Order1:=xlDescendingApplication.ScreenUpdating=TrueEndSub1.设置对象SetConn=CreateObject(adodb.connection)2.打开联接（1）包含字段名的情况Conn.Openprovider=microsoft.jet.oledb.4.0;extendedproperties=excel8.0;datasource=&文件全路径（2）不包含字段名的情况Conn.Openprovider=microsoft.jet.oledb.4.0;extendedproperties='excel8.0;hdr=no';datasource=&文件全路径（3）同一列中数据类型不一致的情况Conn.Openprovider=microsoft.jet.oledb.4.0;extendedproperties='excel8.0;imex=1';datasource=&文件全路径3.设置sql语句Sql=sql语句4.执行sql语句放置结果的区域的左上角单元格.CopyFromRecordsetConn.Execute(Sql)5.关闭联接conn.Close6.对象置空SetConn=Nothing处理不同问题时深色部分的代码可以保持不变，只要根据不同情况改变红色部分的代码即可。所以对照上面的分析，本例中需要解释的就剩SQL语句了SetCnn=CreateObject(adodb.connection)cnn.OpenProvider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties=Excel8.0;DataSource=&ActiveWorkbook.FullName[a4:d65536].ClearContentsSql=TRANSFORMCOUNT(通话类型)SELECT对方号码,SUM([时长(秒)])FROM[原始数据$]GROUPBY对方号码PIVOT通话类型[a4].CopyFromRecordsetCnn.Execute(Sql)cnn.CloseSetcnn=Nothing若仅是获取工作表某区域中的数值而没有汇总等等其他的操作，那么基本的SQL语句为：SELECT*FROM[表名$区域]若没有指明区域且包含字段名，则对于字段名连续的表：SELECT*FROM[原始数据$])可将原始数据表中所有字段名下的数据取出，SELECT字段名1FROM[原始数据$])可将原始数据表中指定字段名1下的数据取出，SELECTSUM(字段名2)FROM[原始数据$])GROUPBY字段名1可求出原始数据表中字段名2根据字段名1的分类求和值，若同时列出分类字段字段名1：SELECT字段名1,SUM(字段名2)FROM[原始数据$])GROUPBY字段名1则结果的第一列为字段名1的不重复值，第二列为字段名2根据字段名1的分类求和值，TRANSFORMCOUNT(字段名4)SELECT字段名1FROM[原始数据$])GROUPBY字段名1PIVOT字段名3可将PIVOT后面指定的字段名3的非重复值作为列标题，并求出字段名4根据字段名1的分类个数值，TRANSFORMCOUNT(字段名4)SELECT字段名1,SUM(字段名2)FROM[原始数据$])GROUPBY字段名1PIVOT字段名3得结果的第一列为字段名1的不重复值，第二列为字段名2根据字段名1的分类求和值，后面为字段名3的非重复值作为列标题的字段名4根据字段名1的分类个数值，（上面所涉及到的字段名可相同也可不同）所以，TRANSFORMCOUNT(通话类型)SELECT对方号码,SUM([时长(秒)])FROM[原始数据$]GROUPBY对方号码PIVOT通话类型即可得到第一列为对方号码的不重复值，第二列为时长根据对方号码的分类求和值，后面为通话类型的非重复值作为列标题的通话类型根据对方号码的分类个数值，因为通话类型只有两种“主叫”和“被叫”，所以第三列为“主叫”次数即呼出次数，第四列为“被叫”次数即呼入次数。若要使用ADO来处理Excel文件，只要将以下6条基本的语句中的红色部分替换为实际内容即可：方式一：SetConn=CreateObject(adodb.connection)‘（1）设置对象Conn.Openprovider=microsoft.jet.oledb.4.0;extendedproperties=excel8.0;datasource=&文件全路径‘（2）打开联接Sql=sql语句&from[表的显示名称$]&sql语句‘（3）设置sql语句放置结果的区域的左上角单元格.CopyFromRecordsetConn.Execute(Sql)‘(4)执行sql语句'或Settemp=Conn.Execute(Sql)'(4-1)执行sql语句，并存于临时对象中………'(4-2)从临时对象中取出结果进行处理Conn.Close‘（5）关闭联接SetConn=Nothing‘（6）对象置空实例1：汉语中的“和”与逻辑运算中的“或”=2&ID=160318&replyID=353335&skin=0设表中有包括字段1的多个字段Subandor()DimSql$SetConn=CreateObject(adodb.connection)‘（1）Conn.Openprovider=microsoft.jet.oledb.4.0;extendedproperties=excel8.0;datasource=c:\a\a.xls”‘（2）Sql=select*from[Sheet1$]where字段1='020009'or字段1='050023'or字段1='010024'‘（3）‘上一句查找字段值为020009、050023和010024的记录，并显示所有字段的值‘由此看出，汉语中的“和”对应的逻辑关系反而常常是“或”（or）'Sql=select字段1from[Sheet1$]‘上一句可显示所有记录的字段1的值Sheets(2).Range(a2).CopyFromRecordsetConn.Execute(Sql)‘（4）‘将结果放在以表2的a2单元格为左上角的区域Conn.Close‘（5）SetConn=Nothing‘（6）EndSub实例2：ADO查询=31&ID=153013&replyID=&skin=0Subfilterado()DimstrTbl$,,intTblCnt%,Sql$,sAddress$,str$Dimrng1AsRange,rngtAsRangeDima()AsString‘有关变量定义，参见：‘=2&id=477Setcn=NewADODB.Connection‘（1）Withcn‘也可将一句分成三句写‘（2）.Provider=Microsoft.Jet.OLEDB.4.0.ConnectionString=DataSource=&ActiveWorkbook.Ful