excel+vba+sql
要求一,将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=" & 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_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
Set 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_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字符值加上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 = CreateObject("ADODB.Connection")
CONN.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
sql = "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 = Nothing
End Sub
-----------------
Sub 查询方法二()
Set CONN = CreateObject("ADODB.Connection")
CONN.Ope
n "dsn=excel files;dbq=" & ThisWorkbook.FullName
sql = "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 = 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.FullName
Sheet3.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").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 = 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.xls"
sql = "select sum(f1) from [sheet1$a1:a10]"
Sheets(1).[A5].CopyFromRecordset conn.Execute(sql)
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.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 f <> ""
If f <> https://www.360docs.net/doc/d915512772.html, 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(UBound(arr), 1) = WorksheetFunction.Transpose(arr)
Application.ScreenUpdating = True
End Sub
**************************************************************************************************
E、根据本文件夹下其他工作簿多个表区域的多列区域查询求和
sub 本文件夹下其他工作簿的每个工作簿的第B\C\D列 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 While f <> ""
If f <> https://www.360docs.net/doc/d915512772.html, 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 = 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 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可以拆开记忆，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区域的和"
**********************************************