EXCEL里使用SQL的方法（歡樂小爪原創(chuàng)）
急用EXCEL的SQL用法，抄來歡樂小爪幾篇文章，支持原創(chuàng)，留下小爪印：http://hi.baidu.com/huanhuanxiaozhua/blog
EXCEL(VBA)~SQL 經(jīng)典寫法范本匯集(一)
需引用 microsoft activeX Data Objects 2.8 library
1.sql = "select 構件名稱,構件代號,橫長度,橫數(shù)量,豎長度,豎數(shù)量,比重,相應工藝 from [參數(shù)$B2:K1916]where (大樣代碼='" & DYDH & "') and (內(nèi)外框='WKXC')"
2.sql = "select 構件名稱,構件代號,橫長度,橫數(shù)量,豎長度,豎數(shù)量,比重,相應工藝 from [參數(shù)$B2:K" & CSMaxrow & "] where (大樣代碼='" & DYDH & "') and (內(nèi)外框='WKXC')"
********************************
A、根據(jù)本工作簿的1個表查詢求和寫法范本
Sub 查詢方法一()
SetCONN = CreateObject("ADODB.Connection")
CONN.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
sql = "select 區(qū)域,存貨類, sum(代銷倉入庫數(shù)量),sum(代銷倉出庫數(shù)量),sum(日報數(shù)量)from [sheet4$a:i] where 區(qū)域='" & [b3] & "' and month(日期)='" & Month(Range("F3")) & "' group by 區(qū)域,存貨類"
Sheets("sheet2").[A5].CopyFromRecordset CONN.Execute(sql)
CONN.Close: SetCONN = Nothing
End Sub
-----------------
Sub 查詢方法二()
SetCONN = CreateObject("ADODB.Connection")
CONN.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName
sql = "select 區(qū)域,存貨類, sum(代銷倉入庫數(shù)量),sum(代銷倉出庫數(shù)量),sum(日報數(shù)量)from [sheet4$a:i] where 區(qū)域='" & [b3] & "' and month(日期)='" & Month(Range("F3")) & "' group by 區(qū)域,存貨類"
Sheets("sheet2").[A5].CopyFromRecordset CONN.Execute(sql)
CONN.Close: SetCONN = Nothing
End Sub
********************************
B、根據(jù)本工作簿2個表的不同類別查詢求和寫法范本
Sub 根據(jù)入庫表和回款表的區(qū)域名和月份分別求存貨類發(fā)貨數(shù)量和本月回款數(shù)量查詢()
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(本月發(fā)貨數(shù)量) " _
& " as fh from [入庫$] where 存貨類 is not null and 區(qū)域='" & [b2] _
& "' and month(日期)=" & [d2] & " group by 存貨類) as a" _
& " left join (select 存貨類,sum(數(shù)量) as hk from [回款$] where 存貨類" _
& " is not null and 區(qū)域='" & [b2] & "' and month(開票日期)=" & [d2] & "" _
& " group by 存貨類) as b on a.存貨類=b.存貨類"
Range("a5").CopyFromRecordset conn.Execute(Sql)
End Sub
********************************
C、根據(jù)本文件夾下其他工作簿1個表區(qū)域的區(qū)域求和
Sub 在工作表1匯總本文件夾下001工作薄的表1分數(shù)列查詢匯總()
Set conn = CreateObject("ADODB.Connection")
conn.Open "dsn=excel files;dbq=" & ThisWorkbook.Path & "\001.xls"
sql = "select sum(分數(shù)) 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分數(shù)列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(分數(shù)) 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、根據(jù)本文件夾下其他工作簿多個表區(qū)域的單列區(qū)域查詢求和
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 <> 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(UBound(arr), 1) = WorksheetFunction.Transpose(arr)
Application.ScreenUpdating = True
End Sub
********************************
E、根據(jù)本文件夾下其他工作簿多個表區(qū)域的多列區(qū)域查詢求和
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 <> 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 = Dir
Loop
Range("b6").Resize(UBound(arr), 3) = arr
Application.ScreenUpdating = True
End Sub
********************************
F、其他相關知識整理
' 用excel SQL方法
'conn是建立的連接對象，用open打開
' 通過 CreateObject("ADODB.Connection") 這一句建立了一個數(shù)據(jù)庫連接對象conn
' 在工程中就不再需要引用“Microsot ActiveX Data Objects 2.0 Library“對象
'設置對象 conn 為一個新的 ADO 鏈接實例,也可以用 set conn = New ADODB.Connection。
--------------
' conn.Close表示關閉conn連接
' Set conn = Nothing 是把連接對象conn置空，不然你退出了文件，但數(shù)據(jù)庫還沒有關閉
conn.Open "dsn=excel files;dbq=" & ThisWorkbook.Path & "\001.xls"能把這段含義具體解釋一下嗎?
'這里的dbq的作用?
'------------------
'dsn是縮寫，data source name數(shù)據(jù)庫名是 excel file
'
'dbq 也是縮寫，data base query 意思是數(shù)據(jù)庫查詢，后接源庫文件名 001.xls
'---------------------
'代碼中長單詞怎么記住的?
'比如copyfromrecordset可以拆開記憶，copy、from、recordset 這三個單詞意思知道吧，就是“復制、從、記錄集”
'-----------------
'Sql = "select sum(分數(shù)) from [sheet1$]"這里加"分數(shù)"兩字什么作用?
'
'SQL一般結構是select 字段 from 表，意思是從指定的表中查詢字段，字段的理解可以是：表中的列名
'
'分數(shù)是001.xls文件的sheet1第一行A列的字段名，SQL一般以字段來識別每列數(shù)據(jù)
'-------------------
'為什么要用復制的對象引用過來計算呢？
'
'因為Sql語句只是對源數(shù)據(jù)庫的字段找到了符合條件的的數(shù)據(jù)，但不會自動復制到匯總表來，所以需要復制copy
'
'注意這里的 [sheet1$]" ，001文件的數(shù)據(jù)存放地上sheet1表，應當用方括號并加上$
'
'如果源數(shù)據(jù)文件001不是excel，而是Access，則引用表時，不需要加方括號，也不要$
'-----------------
還有,這里Execute表示什么作用?
'' Execute是執(zhí)行SQL查詢語句的意思
-----------------------------
如果不要字段也可以，那么在打開語句中加上：hdr=no
'這樣沒有分數(shù)字段也可實現(xiàn)
'SQL語句我換了形式，而且加上了hdr=no，即無需字段，而且我在SQL中用了sum(f1)，f1表示第一列數(shù)據(jù)
'[sheet1$a1:a10] "是只求a1:a10區(qū)域的和"
