1、A 、根據(jù)本工作簿的 1 個(gè)表查詢求和寫法范本Sub 查詢方法一 ()Set CONN = CreateObject("ADODB.Connection"),sum( 代銷倉出庫數(shù)量 ),sum( 日報(bào)數(shù)日期 )='" &),sum( 代銷倉出庫數(shù)量 ),sum( 日報(bào)數(shù)日期 )='" &CONN.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.Fu

2、llName sql = "select 區(qū)域 , 存貨類 , sum( 代銷倉入庫數(shù)量 量)fromsheet4$a:i where 區(qū)域 ='" & b3 & "' and month( Month(Range("F3") & "' group by區(qū)域 , 存貨類 "Sheets("sheet2").A5.CopyFromRecordset CONN.Execute(sql) CONN.Close: Set CONN = NothingEnd SubS

3、ub 查詢方法二 ()Set CONN = CreateObject("ADODB.Connection")CONN.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName sql = "select區(qū)域 , 存貨類 , sum( 代銷倉入庫數(shù)量量 )fromsheet4$a:i where區(qū)域 ='" & b3 & "' and month(Month(Range("F3") & "' gr

4、oup by區(qū)域 , 存貨類 "Sheets("sheet2").A5.CopyFromRecordset CONN.Execute(sql) CONN.Close: Set CONN = NothingEnd Sub*B 、根據(jù)本工作簿 2 個(gè)表的不同類別查詢求和寫法范本Sub根據(jù)入庫表和回款表的區(qū)域名和月份分別求存貨類發(fā)貨數(shù)量和本月回款數(shù)量查詢()Set conn = CreateObject("adodb.connection") conn.Open "provider=microsoft.jet.oledb.4.0;"

5、; & _ "extended properties=excel 8.0;data source=" &ThisWorkbook.FullNameSheet3.ActivateSql = " select a. & " as fh from & "' and month( & " left join (select存貨類 ,a.fh ,b.hk from (select存貨類 ,sum( 本月發(fā)貨數(shù)量 ) " _入庫 $ where 存貨類 is not null and區(qū)域

6、='" & b2 _日期 )=" & d2 & " group by存貨類 ) as a" _存貨類，sum(數(shù)量)as hk from 回款$ where 存貨類”& " is not null and d2 & "" _& " group by區(qū)域='" & b2 & "' and month(開票日期 )=" &存貨類 ) as b on a. 存貨類 =b. 存貨類 "Ra

7、nge("a5").CopyFromRecordset conn.Execute(Sql)End Sub*C 、根據(jù)本文件夾下其他工作簿 1 個(gè)表區(qū)域的區(qū)域求和Sub 在工作表 1 匯總本文件夾下 001 工作薄的表 1 分?jǐn)?shù)列查詢匯總 ()Set conn = CreateObject("ADODB.Connection") conn.Open "dsn=excel files;dbq=" & ThisWorkbook.Path & "001.xls" sql = "select sum

8、(分?jǐn)?shù) ) from sheet1$"Sheets(1).a2.CopyFromRecordset conn.Execute(sql) conn.Close: Set conn = NothingEnd SubSub 在工作表 1 匯總本文件夾下 001 工作薄的表 1A1:A10 查詢匯總 () Set conn = CreateObject("ADODB.Connection") conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=no;

9、'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 SubSub 在工作表 1 匯總本文件夾下 001 工作薄的表 1 分?jǐn)?shù)列 A1:A7 查詢并 msgbox 表達(dá)匯總 () Set conn = CreateObject("

10、;ADODB.Connection")Set rr = CreateObject("ADODB.recordset") conn.Open "dsn=excel files;dbq=" & ThisWorkbook.Path & "001.xls" sql = "select sum(分?jǐn)?shù) ) from sheet1$a1:a7"Sheets(1).A8.CopyFromRecordset conn.Execute(sql) rr.Open sql, conn, 3, 1, 1 MsgBo

11、x rr.fields(0) conn.Close: Set conn = NothingEnd Sub*D 、根據(jù)本文件夾下其他工作簿多個(gè)表區(qū)域的單列區(qū)域查詢求和 sub 本文件夾下其他工作簿的每個(gè)工作簿的第 4 列 30 行查詢求和 Dim cn As Object, f$, arr&(1 To 30), i% Application.ScreenUpdating = FalseSet cn = CreateObject("adodb.connection") f = Dir(ThisWorkbook.Path & "*.xls")D

12、o While f <> ""If f <> ThisWorkbook.Name Thencn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=no;'data source=" & ThisWorkbook.Path & "" & fRange("d5").CopyFromRecordset cn.Execute("select f4 fro

13、m 基表 1$a5:d65536") cn.CloseFor i = 1 To 30 arr(i) = arr(i) + Range("d" & i + 4)Next iEnd Iff = DirLoopRange("d5").Resize(UBound(arr), 1) =WorksheetFunction.Transpose(arr)Application.ScreenUpdating = True End Sub*E 、根據(jù)本文件夾下其他工作簿多個(gè)表區(qū)域的多列區(qū)域查詢求和sub 本文件夾下其他工作簿的每個(gè)工作簿的第 BCD 列 2

14、5 行查詢求和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 Thencn.Open "provider=microsoft.jet.ol

15、edb.4.0;extendedproperties='excel 8.0;hdr=no;'data source=" & ThisWorkbook.Path &"" & fRange("b6").CopyFromRecordset cn.Execute("select f2,f3,f4from 基表 3$a6:e65536")cn.CloseFor i = 1 To 25For j = 1 To 3arr(i, j) = arr(i, j) + Cells(i + 5, j + 1)

16、Next jNext iEnd Iff = DirLoopRange("b6").Resize(UBound(arr), 3) = arrApplication.ScreenUpdating = TrueEnd Sub*F 、其他相關(guān)知識整理'用 excel SQL 方法'conn 是建立的連接對象，用 open 打開'通過 CreateObject("ADODB.Connection")這一句建立了一個(gè)數(shù)據(jù)庫連接對象 conn' 在工程中就不再需要引用“ Microsot ActiveX Data Objects 2.0

17、 Library “ 對象' 設(shè)置對象 conn 為一個(gè)新的 ADO 鏈接實(shí)例 , 也可以用 set conn = New ADODB.Connection 。' conn.Close表示關(guān)閉 conn 連接' Set conn = Nothing是把連接對象 conn 置空，不然你退出了文件，但數(shù)據(jù)庫還沒有關(guān)閉conn.Open "dsn=excel files;dbq=" & ThisWorkbook.Path &"001.xls" 能把這段含義具體解釋一下嗎 ?' 這里的 dbq 的作用 ?'

18、dsn是縮寫， data source name 數(shù)據(jù)庫名 是 excel fileI'dbq 也是縮寫， data base query 意思是數(shù)據(jù)庫查詢，后接源庫文件名 001.xls代碼中長單詞怎么記住的 ?比如 copyfromrecordset 可以拆開記憶， copy 、 from 、 recordset 這三個(gè)單詞意思知道吧，就是“復(fù)制、從、記錄集”'Sql = "select sum( 分?jǐn)?shù) ) from sheet1$" 這里加 " 分?jǐn)?shù) " 兩字什么作用 ?I'SQL 一般結(jié)構(gòu)是 select 字段 from

19、表，意思是從指定的表中查詢字段， 字段的理解可以是： 表 中的列名I'分?jǐn)?shù) 是OO1.xls文件的sheetl第一行A列的字段名，SQL般以字段來識別每列數(shù)據(jù)'為什么要用復(fù)制的對象引用過來計(jì)算呢？I'因?yàn)?Sql 語句只是對源數(shù)據(jù)庫的字段找到了符合條件的的數(shù)據(jù)，但不會自動復(fù)制到匯總表來，所以需要復(fù)制 copyI'注意 這里的 sheet1$" ， 001 文件的數(shù)據(jù)存放地上 sheet1 表，應(yīng)當(dāng)用方括號并加上 $I'如果源數(shù)據(jù)文件001不是excel，而是Access，則引用表時(shí)，不需要加方括號，也不要 $還有 , 這里 Execute 表示

20、什么作用 ?''Execute是執(zhí)行SQL查詢語句的意思如果不要字段也可以，那么在打開語句中加上： hdr=no'這樣沒有分?jǐn)?shù)字段也可實(shí)現(xiàn)'SQL語句我換了形式，而且加上了hdr= no，即無需字段，而且我在SQL中用了 sum(fl) ，fl表示第一列數(shù)據(jù)'sheet1$a1:a10 "是只求 a1:a10 區(qū)域的和 "*使用VBA執(zhí)行SQLsub test() ' 定義過程名稱Dim i As Integer, j As Integer, sht As Worksheet 'i,j為整數(shù)變量； sht為 excel

21、 工作表對象變量，指向某一工作表Dim cn As New ADODB.ConnectionDim rs As New ADODB.Recordset '定義數(shù)據(jù)鏈接對象，保存連接數(shù)據(jù)庫信息；請先添加定義記錄集對象，保存數(shù)據(jù)表ADO引用Dim strCn As String ,strSQL as String '字符串變量strCn = "Provider=sqloledb;Server=服務(wù)器名稱或IP地址;Database=數(shù)據(jù)庫名稱；Uid=用戶登錄名；Pwd=密碼 ;"' 定義數(shù)據(jù)庫鏈接字符串面的語句將讀取數(shù)據(jù)表數(shù)據(jù)，并將它保存到 excel

22、 工作表中：畫兩張表想像一下，工作表為一張兩維表，記錄集也是一張兩維表strSQL = "select 字段1,字段2 from 表名稱”' 定義SQL查詢命令字符串cn.Open strCn ' 與數(shù)據(jù)庫建立連接，如果成功，返回連接對象 cnrs.Open strSQL, cn '執(zhí)行strSQL所含的SQL命令，結(jié)果保存在 rs記錄集對象中i = 1Set sht = ThisWorkbook.Worksheets("sheet1")把 sht 指向當(dāng)前工作簿的 sheet1 工作表Do While Not rs.EOF 'sht.Cells(i, 1) = rs("列sht.Cells(i, 2) = rs(" rs.MoveNexti = i + 1'i當(dāng)數(shù)據(jù)指針未移到記錄集末尾時(shí)，循環(huán)下列操作字段 1"） ' 把當(dāng)前記錄的字段