经典Excel_VBA_SQL语句.doc

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=excel 8.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=excel 8.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.xlssql = 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=excel 8.0;hdr=no;data source= & ThisWorkbook.Path & 001.xlssql = select sum(f1) from sheet1$a1:a10Sheets(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.xlssql = select sum(分数) from sheet1$a1:a7Sheets(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&(1 To 30), i%Application.ScreenUpdating = FalseSet cn = CreateObject(adodb.connection)f = Dir(ThisWorkbook.Path & *.xls)Do While f If f ThisWorkbook.Name Then cn.Open provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;hdr=no;data source= & ThisWorkbook.Path & & f Range(d5).CopyFromRecordset cn.Execute(select f4 from 基表1$a5:d65536) cn.Close For i = 1 To 30 arr(i) = arr(i) + Range(d & i + 4) Next i End If f = DirLoopRange(d5).Resize(UBound(arr), 1) = WorksheetFunction.Transpose(arr)Application.ScreenUpdating = TrueEnd Sub*E、根据本文件夹下其他工作簿多个表区域的多列区域查询求和sub 本文件夹下其他工作簿的每个工作簿的第BCD列 25行查询求和Dim cn As Object, f$, arr&(1 To 25, 1 To 3), i%Application.ScreenUpdating = FalseSet cn = CreateObject(adodb.connection)f = Dir(ThisWorkbook.Path & *.xls)Do While f If f ThisWorkbook.Name Then cn.Open provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;hdr=no;data source= & ThisWorkbook.Path & & f Range(b6).CopyFromRecordset cn.Execute(select f2,f3,f4 from 基表3$a6:e65536) cn.Close For i = 1 To 25 For j = 1 To 3 arr(i, j) = arr(i, j) + Cells(i + 5, j + 1) Next j Next i End If f = DirLoopRange(b6).Resize(UBound(arr), 3) = arrApplication.ScreenUpdating = TrueEnd Sub*F、其他相关知识整理 用excel SQL方法conn是建立的连接对象，用open打开 通过 CreateObject(ADODB.Connection) 这一句建立了一个数据库连接对象conn 在工程中就不再需要引用“Microsot ActiveX Data Objects 2.0 Library“ 对象设置对象 conn 为一个新的 ADO 链接实例,也可以用 set conn = New ADODB.Connection。- conn.Close表示关闭conn连接 Set conn = Nothing 是把连接对象conn置空，不然你退出了文件，但数据库还没有关闭conn.Open dsn=excel files;dbq= & ThisWorkbook.Path & 001.xls能把这段含义具体解释一下吗?这里的dbq的作用?-dsn是缩写，data source name数据库名 是 excel filedbq 也是缩写，data base query 意思是数据库查询，后接源库文件名 001.xls-代码中长单词怎么记住的?比如copyfromrecordset可以拆开记忆，copy、from、recordset 这三个单词意思知道吧，就是“复制、从、记录集”-Sql = select sum(分数) from sheet1$这里加分数两字什么作用?SQL一般结构是select 字段 from 表，意思是从指定的表中查询字段，字段的理解可以是：表 中的列名分数 是001.xls文件的sheet1第一行A列的字段名，SQL一般以字段来识别每列数据-为什么要用复制的对象引用过来计算呢？因为Sql语句只是对源数据库的字段找到了符合条件的的数据，但不会自动复制到汇总表来，所以需要复制copy注意 这里的 sheet1$ ，001文件的数据存放地上sheet1表，应当用方括号并加上$如果源数据文件001不是excel，而是Access，则引用表时，不需要加方括号，也不要$-还有,这里Execute表示什么作用? Execute是执行SQL查询语句的意思-如果不要字段也可以，那么在打开语句中加上：hdr=no这样没有分数字段也可实现SQL语句我换了形式，而且加上了hdr=no，即无需字段，而且我在SQL中用了sum(f1)，f1表示第一列数据sheet1$a1:a10 是只求a1:a10区域的和 EXCEL(VBA)SQL 经典写法范本汇集(二)2008-05-10 11:21一)选择供应商和选择月份记录的查询原创:小爪 日期:2008-5-10Private Sub CommandButton1_Click()Range(a5:k1000).ClearContentsSet conn = CreateObject(ADODB.Connection) conn.Open provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;imex=1;data source= & ThisWorkbook.FullName If Range(b3) = 全部 And Range(d3) = 全部 Then Sql = select * from 数据源$a3:i1000 GoTo 100 End If If Range(b3) = 全部 Then Sql = select * from 数据源$a3:i1000 where month(日期) = & d3 & GoTo 100 End If If Range(d3) = 全部 Then Sql = select * from 数据源$a3:i1000 where 供应商= & b3 & GoTo 100 End If If Range(d3) 全部 And Range(d3) 全部 Then i = Range(d3) Sql = select * from 数据源$a3:i1000 where (供应商= & b3 & ) and (month(日期) = & i & ) GoTo 100 End If 100:Sheets(统计).Range(a5).CopyFromRecordset conn.Execute(Sql) conn.Close: Set conn = NothingEnd Sub-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-二)查询某地的 收款记录工作表 的收款日期,凭证号,金额,摘要 和 送货记录工作表 的发货日期,单号,金额,折扣,赠送,退货,备注原创:小爪 日期:2008-5-6实例地址:E:欢乐-office小爪-excel小爪-vbaADO+SQLPrivate Sub CommandButton1_Click()Range(a6:k16).ClearContentsSet conn = CreateObject(ADODB.Connection) conn.Open provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;imex=1;data source= & ThisWorkbook.FullName Sql1 = select 收款日期,凭证号,金额,摘要 from 收款记录$B2:F20 where 客户名称 = & b2 & Sql2 = select 发货日期,单号,金额,折扣,赠送,退货,备注 from 送货记录$B2:i20 where 客户名称 = & b2 & Sheets(套打).Range(a6).CopyFromRecordset conn.Execute(Sql1)Sheets(套打).Range(e6).CopyFromRecordset conn.Execute(Sql2) conn.Close: Set conn = NothingEnd Sub用VBA将SQL查询结果送到EXCEL指定单元格2009年09月22日 星期二 16:43（转）如何利用VBA将SQL查询结果送到EXCEL指定单元格Dim i As Integer, j As Integer, sht As Worksheet i,j为整数变量；sht 为excel工作表对象变量，指向某一工作表Dim cn As New ADODB.Connection 定义数据链接对象 ，保存连接数据库信息；请先添加ADO引用Dim rs As New ADODB.Recordset 定义记录集对象，保存数据表Dim strCn As String, strSQL As