没有合适的资源?快使用搜索试试~ 我知道了~
需引用 microsoft activeX Data Objects 2.8 library 1.sql = "select 构件名称,构件代号,横长度,横数量,竖长度,竖数量,比重,相应工艺 from [参数$B2:K1916]where (大样代码='" & DYDH & "') and (内外框='WKXC')"
资源推荐
资源详情
资源评论
EXCEL 使用 SQL语句
-------------自注,2003后microsoft.jet.oledb.4.0,改为=Microsoft.ACE.OLEDB.12.0,可以动态查询已经打开的文件。
EXCEL(VBA)~SQL 经典写法范本汇集(一)
2007-12-10 21:20
编前话:为了更系统的学习sql语句,小爪首次系统的汇集sql
需引用 microsoft activeX Data Objects 2.8 library
1.sql = "select 构件名称,构件代号,横长度,横数量,竖长度,竖数量,比重,相应工艺 from [参数$B2:K1916]where (大样代码='" & DYDH & "') and (内外框='WKXC')"
2.sql = "select 构件名称,构件代号,横长度,横数量,竖长度,竖数量,比重,相应工艺 from [参数$B2:K" & CSMaxrow & "] where (大样代码='" & DYDH & "') and (内外框='WKXC')"
****************************************************************
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.Open "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 根据入库表和回款表的区域名和月份分别求存货类发货数量和本月回款数量查询()
-------------自注,2003后microsoft.jet.oledb.4.0,改为=Microsoft.ACE.OLEDB.12.0,可以动态查询已经打开的文件。
EXCEL(VBA)~SQL 经典写法范本汇集(一)
2007-12-10 21:20
编前话:为了更系统的学习sql语句,小爪首次系统的汇集sql
需引用 microsoft activeX Data Objects 2.8 library
1.sql = "select 构件名称,构件代号,横长度,横数量,竖长度,竖数量,比重,相应工艺 from [参数$B2:K1916]where (大样代码='" & DYDH & "') and (内外框='WKXC')"
2.sql = "select 构件名称,构件代号,横长度,横数量,竖长度,竖数量,比重,相应工艺 from [参数$B2:K" & CSMaxrow & "] where (大样代码='" & DYDH & "') and (内外框='WKXC')"
****************************************************************
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.Open "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
-----------------------
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
-----------------------
剩余5页未读,继续阅读
资源评论
sunjoin2009
- 粉丝: 0
- 资源: 8
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功