EXCEL(VBA)~SQL经典写法范本汇集2008年03月30日星期日下午07:21EXCEL(VBA)~SQL经典写法范本汇集****************************************************************A、根据本工作簿的1个表查询求和写法范本Sub查询方法一()Set CONN=CreateObject("ADODB.Connection")CONN.Open"provider=microsoft.jet.oledb.4.0;extended properties=excel8.0;data source="& ThisWorkbook.FullNamesql="select区域,存货类,sum(代销仓入库数量),sum(代销仓出库数量),sum(日报数量)from[sheet4$a:i]where区域='"&[b3]&"'and month(日期)='"&Month(Range("F3"))&"'group by区域,存货类"Sheets("sheet2").[A5].CopyFromRecordset CONN.Execute(sql)CONN.Close:Set CONN=NothingEnd Sub-----------------Sub查询方法二()Set CONN=CreateObject("ADODB.Connection")CONN.Open"dsn=excel files;dbq="&ThisWorkbook.FullNamesql="select区域,存货类,sum(代销仓入库数量),sum(代销仓出库数量),sum(日报数量)from[sheet4$a:i]where区域='"&[b3]&"'and month(日期)='"&Month(Range("F3"))&"'group by区域,存货类"Sheets("sheet2").[A5].CopyFromRecordset CONN.Execute(sql)CONN.Close:Set CONN=NothingEnd Sub**************************************************************************************************B、根据本工作簿2个表的不同类别查询求和写法范本Sub根据入库表和回款表的区域名和月份分别求存货类发货数量和本月回款数量查询()Set conn=CreateObject("adodb.connection")conn.Open"provider=microsoft.jet.oledb.4.0;"&_"extended properties=excel8.0;data source="&ThisWorkbook.FullNameSheet3.ActivateSql="select a.存货类,a.fh,b.hk from(select存货类,sum(本月发货数量)"_&"as fh from[入库$]where存货类is not null and区域='"&[b2]_&"'and month(日期)="&[d2]&"group by存货类)as a"_&"left join(select存货类,sum(数量)as hk from[回款$]where存货类"_&"is not null and区域='"&[b2]&"'and month(开票日期)="&[d2]&""_&"group by存货类)as b on a.存货类=b.存货类"Range("a5").CopyFromRecordset conn.Execute(Sql)End Sub*******************************************************************C、根据本文件夹下其他工作簿1个表区域的区域求和Sub在工作表1汇总本文件夹下001工作薄的表1分数列查询汇总()Set conn=CreateObject("ADODB.Connection")conn.Open"dsn=excel files;dbq="&ThisWorkbook.Path&"\001.xls"sql="select sum(分数)from[sheet1$]"Sheets(1).[a2].CopyFromRecordset conn.Execute(sql)conn.Close:Set conn=NothingEnd Sub---------------------Sub在工作表1汇总本文件夹下001工作薄的表1A1:A10查询汇总()Set conn=CreateObject("ADODB.Connection")conn.Open"provider=microsoft.jet.oledb.4.0;extended properties='excel8.0;hdr=no;';data source="& ThisWorkbook.Path&"\001.xls"sql="select sum(f1)from[sheet1$a1:a10]"Sheets(1).[A5].CopyFromRecordset conn.Execute(sql)conn.Close:Set conn=NothingEnd Sub-----------------------Sub在工作表1汇总本文件夹下001工作薄的表1分数列A1:A7查询并msgbox表达汇总()Set conn=CreateObject("ADODB.Connection")Set rr=CreateObject("ADODB.recordset")conn.Open"dsn=excel files;dbq="&ThisWorkbook.Path&"\001.xls"sql="select sum(分数)from[sheet1$a1:a7]"Sheets(1).[A8].CopyFromRecordset conn.Execute(sql)rr.Open sql,conn,3,1,1MsgBox rr.fields(0)conn.Close:Set conn=NothingEnd Sub******************************************************************************************D、根据本文件夹下其他工作簿多个表区域的单列区域查询求和sub本文件夹下其他工作簿的每个工作簿的第4列30行查询求和Dim cn As Object,f$,arr&(1To30),i%Application.ScreenUpdating=FalseSet cn=CreateObject("adodb.connection")f=Dir(ThisWorkbook.Path&"\*.xls")Do While f<>""If f<> Thencn.Open"provider=microsoft.jet.oledb.4.0;extended properties='excel8.0;hdr=no;';data source="& ThisWorkbook.Path&"\"&fRange("d5").CopyFromRecordset cn.Execute("select f4from[基表1$a5:d65536]")cn.CloseFor i=1To30arr(i)=arr(i)+Range("d"&i+4)Next iEnd Iff=DirLoopRange("d5").Resize(UBound(arr),1)=WorksheetFunction.Transpose(arr)Application.ScreenUpdating=TrueEnd Sub**************************************************************************************************E、根据本文件夹下其他工作簿多个表区域的多列区域查询求和sub本文件夹下其他工作簿的每个工作簿的第B\C\D列25行查询求和Dim cn As Object,f$,arr&(1To25,1To3),i%Application.ScreenUpdating=FalseSet cn=CreateObject("adodb.connection")f=Dir(ThisWorkbook.Path&"\*.xls")Do While f<>""If f<> Thencn.Open"provider=microsoft.jet.oledb.4.0;extended properties='excel8.0;hdr=no;';data source="& ThisWorkbook.Path&"\"&fRange("b6").CopyFromRecordset cn.Execute("select f2,f3,f4from[基表3$a6:e65536]")cn.CloseFor i=1To25For j=1To3arr(i,j)=arr(i,j)+Cells(i+5,j+1)Next jNext iEnd Iff=DirLoopRange("b6").Resize(UBound(arr),3)=arrApplication.ScreenUpdating=TrueEnd Sub***********************************************************************************F、其他相关知识整理'用excel SQL方法'conn是建立的连接对象，用open打开'通过CreateObject("ADODB.Connection")这一句建立了一个数据库连接对象conn'在工程中就不再需要引用“Microsot ActiveX Data Objects2.0Library“对象'设置对象conn为一个新的ADO链接实例,也可以用set conn=New ADODB.Connection。