A、根据本工作簿的1个表查询求和写法范本
七年级英语试卷分析
beverly hills Sub 查询方法一()
Set CONN = CreateObject("ADODB.Connection")
CONN.Open "provider=microsoft.jet.oledb.4.0;extended
攀枝花培训学校 properties=excel 8.0;data source=" & ThisWorkbook.FullName
sql = "lect 区域,存货类, sum(代销仓入库数量),sum(代销仓出库数量),sum(日报数量)from
[sheet4$a:i] where 区域='" & [b3] & "' and month(日期)='" &
Month(Range("F3")) & "' group by 区域,存货类"
tomorrow Sheets("sheet2").[A5].CopyFromRecordt CONN.Execute(sql)
CONN.Clo: Set CONN = Nothing
End Sub
-----------------
Sub 查询方法二()
Set CONN = CreateObject("ADODB.Connection")
CONN.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName
sql = "lect 区域,存货类, sum(代销仓入库数量),sum(代销仓出库数量),sum(日报数量)from
[sheet4$a:i] where 区域='" & [b3] & "' and month(日期)='" &
Month(Range("F3")) & "' group by 区域,存货类"
Sheets("sheet2").[A5].CopyFromRecordt CONN.Execute(sql)
CONN.Clo: Set CONN = Nothing
End Sub
**************************************************************************************************
冰川时代1 B、根据本工作簿2个表的不同类别查询求和写法范本
Sub 根据入库表和回款表的区域名和月份分别求存货类发货数量和本月回款数量查询()
Set conn = CreateObject("tion")
假期里的一件事 conn.Open "provider=microsoft.jet.oledb.4.0;" & _
"extended properties=excel 8.0;data source=" &
ThisWorkbook.FullName
Sheet3.Activate
Sql = " lect a.存货类,a.fh ,b.hk from (lect 存货类,sum(本月发货数量) " _
& " as fh from [入库$] where 存货类 is not null and 区域='" & [b2] _
& "' and month(日期)=" & [d2] & " group by 存货类) as a" _
& " left join (lect 存货类,sum(数量) as hk from [回款$] where 存货类"
_
& " is not null and 区域='" & [b2] & "' and month(开票日期)=" &
[d2] & "" _
& " group by 存货类) as b on a.存货类=b.存货类"
Range("a5").CopyFromRecordt 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 = "lect sum(分数) from [sheet1$]"
Sheets(1).[a2].CopyFromRecordt conn.Execute(sql)
conn.Clo: 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 = "lect sum(f1) from [sheet1$a1:a10]"
Sheets(1).[A5].CopyFromRecordt conn.Execute(sql)
世界大学最新排名 conn.Clo: Set conn = Nothing
End Sub
-----------------------
Sub 在工作表1汇总本文件夹下001工作薄的表1分数列A1:A7查询并msgbox表达汇总()
Set conn = CreateObject("ADODB.Connection")
Set rr = CreateObject("dt")
conn.Open "dsn=excel files;dbq=" & ThisWorkbook.Path & "\001.xls"
sql = "lect sum(分数) from [sheet1$a1:a7]"
Sheets(1).[A8].CopyFromRecordt conn.Execute(sql)
rr.Open sql, conn, 3, 1, 1
MsgBox rr.fields(0)
conn.Clo: Set conn = Nothing
End Sub
******************************************************************************************
D、根据本文件夹下其他工作簿多个表区域的单列区域查询求和
sub 本文件夹下其他工作簿的每个工作簿的第4列 30行查询求和
Dim cn As Object, f$, arr&(1 To 30), i%
Application.ScreenUpdating = Fal
Set cn = CreateObject("tion")
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
