《2022年使用VBA执行SQL .pdf》由会员分享，可在线阅读，更多相关《2022年使用VBA执行SQL .pdf（5页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、 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(日期 )=
2、& 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( 日
3、报数量)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) con
4、n.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 _ & le
5、ft 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 分数列查询汇总() 名师资料总结 - - -精品资料欢迎下载 - -
6、- - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 5 页 - - - - - - - - - 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
7、 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.Execu
8、te(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.
9、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.connecti
10、on) 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) = a
11、rr(i) + Range(d & i + 4) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 5 页 - - - - - - - - - Next i End If f = Dir Loop Range(d5).Resize(UBound(arr), 1) = WorksheetFunction.Transpose(arr) Application.ScreenUpdating = True End Sub * E、根据本文件夹下其他工作簿多个表区域的多列区域查询求和
12、 sub 本文件夹下其他工作簿的每个工作簿的第BCD 列 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=exce
13、l 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.Scr
14、eenUpdating = 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。 - 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - -
15、 - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 5 页 - - - - - - - - - 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 意思是数
16、据库查询，后接源库文件名 001.xls - 代码中长单词怎么记住的? 比如 copyfromrecordset可以拆开记忆，copy、from 、 recordset 这三个单词意思知道吧，就是“复制、从、记录集” - Sql = select sum(分数 ) from sheet1$这里加 分数 两字什么作用 ? SQL一般结构是select 字段 from 表，意思是从指定的表中查询字段，字段的理解可以是：表 中的列名 分数是 001.xls文件的 sheet1 第一行 A列的字段名，SQL一般以字段来识别每列数据 - 为什么要用复制的对象引用过来计算呢？ 因为Sql 语句只是对源数据
17、库的字段找到了符合条件的的数据，但不会自动复制到汇总表来，所以需要复制copy 注意这里的 sheet1$ ， 001 文件的数据存放地上sheet1 表，应当用方括号并加上$ 如果源数据文件001 不是 excel ，而是 Access，则引用表时，不需要加方括号，也不要$ - 还有 ,这里 Execute 表示什么作用 ? Execute是执行 SQL查询语句的意思 - 如果不要字段也可以，那么在打开语句中加上：hdr=no 这样没有分数字段也可实现 SQL语句我换了形式，而且加上了hdr=no ，即无需字段，而且我在SQL中用了 sum(f1) ，f1表示第一列数据 sheet1$a1:
18、a10 是只求 a1:a10 区域的和 * 使用 VBA执行 SQL 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 5 页 - - - - - - - - - sub test() 定义过程名称Dim i As Integer, j As Integer, sht As Worksheet i,j为整数变量； sht 为 excel 工作表对象变量，指向某一工作表Dim cn As New ADODB.Connection 定义数据链接对象，保存连接数据库信息；请先添加
19、ADO 引用Dim rs As New ADODB.Recordset 定义记录集对象，保存数据表Dim strCn As String ,strSQL as String 字符串变量strCn = Provider=sqloledb;Server=服务器名称或IP 地址 ;Database= 数据库名称 ;Uid= 用户登录名 ;Pwd=密码 ; 定义数据库链接字符串 下面的语句将读取数据表数据，并将它保存到excel工作表中：画两张表想像一下，工作表为一张两维表，记录集也是一张两维表strSQL = select 字段 1, 字段 2 from 表名称 定义 SQL查询命令字符串cn.Op
20、en strCn 与数据库建立连接，如果成功，返回连接对象cn rs.Open strSQL, cn 执行 strSQL 所含的 SQL命令，结果保存在rs 记录集对象中i = 1 Set sht = ThisWorkbook.Worksheets(sheet1) 把 sht 指向当前工作簿的sheet1 工作表Do While Not rs.EOF 当数据指针未移到记录集末尾时，循环下列操作 sht.Cells(i, 1) = rs(字段 1) 把当前记录的字段1 的值保存到sheet1 工作表的第i 行第 1列 sht.Cells(i, 2) = rs(字段 2) 把当前字段2 的值保存到
21、sheet1 工作表的第i 行第 2 列 rs.MoveNext 把指针移向下一条记录 i = i + 1 i加 1，准备把下一记录相关字段的值保存到工作表的下一行Loop 循环rs.Close 关闭记录集， 至此，程序将把某数据表的字段1 和字段 2 保存在 excel 工作表 sheet1 的第 1、2 列，行数等于数据表的记录数 下面的语句将读取excel 工作表数据，并将之简单计算后存入数据库，这里使用上面程序中的一些变量 假设分别读取工作表sheet1 第 5 行至第 500 行的第 8 列和第 9 列已存在的数据，然后将它们相乘，并将积存入数据库的某个表strSQL= 清空上面定义
22、的变量for i=5 to 500 循环开始， i 从 5 到 500 strSQL=strSQL & insert into 表名 ( 字段 ) values( & sht.cells(i,8)*sht.cells(i,9) & ) ; 构造 SQL命令串next 至此生成一串SQL命令串， 保存的内容大概为： insert into 表名 ( 字段 ) values( 数值 1);insert into 表名( 字段 ) values( 数值 2); cn.execute strSQL 执行该 SQL命令串，如果SQL命令没有错误，将在数据库中添加501 个记录 ; 也可以用 rs.open strSQL,cn 执行cn.close 关闭数据库链接, 释放资源end sub 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 5 页 - - - - - - - - -