在Excel的VBA中使用SQL语句.pdf

《在Excel的VBA中使用SQL语句.pdf》由会员分享，可在线阅读，更多相关《在Excel的VBA中使用SQL语句.pdf（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、要求一,将 EXCEL 文件SG Master List SO Outanding 090520_ZY.xls中 Master 页内容中,ItemCode 字段左边六位字符值,和 U_Cat1字符值加上 U_Cat2加上”-”号,再加上 U_Cat3右边两位数相比较,将不相同所有行记录,复制到 sheet2 页中去.Sub()Dim cn As New ADODB.Connection Dim sql As String cn.Open provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=&Thi

2、sWorkbook.FullName cn.Open Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=Excel 8.0;Data Source=&ThisWorkbook.Path&SG Master List SO Outanding 090520_ZY.xls sql=select*from Master$where left(ItemCode,6)U_Cat1&U_Cat2&-&right(U_Cat3,2)Sheets(Sheet2).A4.CopyFromRecordset cn.Execute(sql)cn.Close S

3、et cn=Nothing End Sub 一,在没有写代码这前要先通过菜单栏中”工具”,”引用”加载”ADO”类.cn.Open provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=&ThisWorkbook.FullName cn.Open Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=Excel 8.0;Data Source=&ThisWorkbook.Path&SG Master List SO Outanding 090520

4、_ZY.xls 这两句都能成功建立过程与文件的链接.二,在 SQL语句中,FROM后面的格式一定要Master$,中间 Master是页名,SQL 中用到的字段名是这个页中第一行数据值.三,Sheets(Sheet2).A4.CopyFromRecordset cn.Execute(sql)语句中,Sheets(Sheet2)代表要复制的目标页(在写 VBA,之前要先建立好.).A4是要粘贴的起启单元格.要求二,将 EXCEL 文件SG Master List SO Outanding 090520_ZY.xls中 Master 页内容中,ItemCode 字段左边六位字符值,和 U_Cat1

5、字符值加上 U_Cat2加上”-”号,再加上 U_Cat3右边两位数相比较,将不相同所有行记录,标上”黄颜色”.一,先选择 ItemCode 字段第一行单元格,按住”shift”+”cntre”+”向下箭头”这样,就能将本列单元格全部选定.(适合大量数据的表中).二,在”格式”,-“条件格式”,选择”公式”写入“=LEFT($B1,6)($N1&$O1&-&RIGHT($P1,2)”(这里的列是用$B1表示,因为选中所有列,所以 EXCEL 会将公式自动刷新所有列.)EXCEL(VBA)SQL 经典写法范本汇集*A、根据本工作簿的1 个表查询求和写法范本Sub 查询方法一()Set CONN=

6、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.CopyFromRecordse

7、t CONN.Execute(sql)CONN.Close:Set CONN=Nothing End 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)

8、.A5.CopyFromRecordset CONN.Execute(sql)CONN.Close:Set CONN=Nothing End 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.FullNameSh

9、eet3.Activate Sql=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).C

10、opyFromRecordset 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

11、=Nothing End 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:a10 Sheets(1).A5.CopyFromRecordset conn.Execute(s

12、ql)conn.Close:Set conn=Nothing End 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

13、.Execute(sql)rr.Open sql,conn,3,1,1 MsgBox rr.fields(0)conn.Close:Set conn=Nothing End Sub*D、根据本文件夹下其他工作簿多个表区域的 单列区域查询求和sub 本文件夹下其他工作簿的 每个工作簿的第 4 列 30 行查询求和Dim cn As Object,f$,arr&(1 To 30),i%Application.ScreenUpdating=False Set cn=CreateObject(adodb.connection)f=Dir(ThisWorkbook.Path&*.xls)Do While

14、 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=Dir Loop Range(d5).Resize

15、(UBound(arr),1)=WorksheetFunction.Transpose(arr)Application.ScreenUpdating=True End Sub*E、根据本文件夹下其他工作簿多个表区域的 多列区域查询求和sub 本文件夹下其他工作簿的 每个工作簿的第 BCD 列 25 行查询求和Dim cn As Object,f$,arr&(1 To 25,1 To 3),i%Application.ScreenUpdating=False Set cn=CreateObject(adodb.connection)f=Dir(ThisWorkbook.Path&*.xls)Do

16、 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 Ne

17、xt i End If f=Dir Loop Range(b6).Resize(UBound(arr),3)=arr Application.ScreenUpdating=True End Sub*F、其他相关知识整理 用 excel SQL 方法conn 是建立的连接对象，用open打开 通过 CreateObject(ADODB.Connection)这一句建立了一个数据库连接对象 conn 在工程中就不再需要引用“Microsot ActiveX Data Objects 2.0 Library“对象 设置对象 conn 为一个新的 ADO 链接实例,也可以用 set conn=New

18、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 file dbq 也是缩写，data base query 意思是数据库查询，后接源库文件名 001.xls-代码中长单词怎么记住的?比如 copyfromrecordset可以拆

19、开记忆，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 区域的和 *