《使用VBA执行SQL.doc》由会员分享，可在线阅读，更多相关《使用VBA执行SQL.doc（5页珍藏版）》请在三一文库上搜索。
1、A 、根据本工作簿的 1 个表查询求和写法范本Sub 查询方法一 ()Set CONN = CreateObject("ADODB.Connection"),sum( 代销仓出库数量 ),sum( 日报数日期 )='" &),sum( 代销仓出库数量 ),sum( 日报数日期 )='" &CONN.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.Fu
2、llName sql = "select 区域 , 存货类 , sum( 代销仓入库数量 量)fromsheet4$a:i where 区域 ='" & b3 & "' and month( Month(Range("F3") & "' group by区域 , 存货类 "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区域 , 存货类 , sum( 代销仓入库数量量 )fromsheet4$a:i where区域 ='" & b3 & "' and month(Month(Range("F3") & "' gr
4、oup by区域 , 存货类 "Sheets("sheet2").A5.CopyFromRecordset CONN.Execute(sql) CONN.Close: Set CONN = NothingEnd Sub*B 、根据本工作簿 2 个表的不同类别查询求和写法范本Sub根据入库表和回款表的区域名和月份分别求存货类发货数量和本月回款数量查询()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( 本月发货数量 ) " _入库 $ where 存货类 is not null and区域
6、='" & b2 _日期 )=" & d2 & " group by存货类 ) as a" _存货类，sum(数量)as hk from 回款$ where 存货类”& " is not null and d2 & "" _& " group by区域='" & b2 & "' and month(开票日期 )=" &存货类 ) as b on a. 存货类 =b. 存货类 "Ra
7、nge("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
8、(分数 ) 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 分数列 A1:A7 查询并 msgbox 表达汇总 () Set conn = CreateObject("
10、;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.CopyFromRecordset conn.Execute(sql) rr.Open sql, conn, 3, 1, 1 MsgBo
11、x rr.fields(0) conn.Close: Set conn = NothingEnd Sub*D 、根据本文件夹下其他工作簿多个表区域的单列区域查询求和 sub 本文件夹下其他工作簿的每个工作簿的第 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 、根据本文件夹下其他工作簿多个表区域的多列区域查询求和sub 本文件夹下其他工作簿的每个工作簿的第 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 、其他相关知识整理'用 excel SQL 方法'conn 是建立的连接对象，用 open 打开'通过 CreateObject("ADODB.Connection")这一句建立了一个数据库连接对象 conn' 在工程中就不再需要引用“ Microsot ActiveX Data Objects 2.0
17、 Library “ 对象' 设置对象 conn 为一个新的 ADO 链接实例 , 也可以用 set conn = New ADODB.Connection 。' conn.Close表示关闭 conn 连接' Set conn = Nothing是把连接对象 conn 置空，不然你退出了文件，但数据库还没有关闭conn.Open "dsn=excel files;dbq=" & ThisWorkbook.Path &"001.xls" 能把这段含义具体解释一下吗 ?' 这里的 dbq 的作用 ?'
18、dsn是缩写， data source name 数据库名 是 excel fileI'dbq 也是缩写， data base query 意思是数据库查询，后接源库文件名 001.xls代码中长单词怎么记住的 ?比如 copyfromrecordset 可以拆开记忆， copy 、 from 、 recordset 这三个单词意思知道吧，就是“复制、从、记录集”'Sql = "select sum( 分数 ) from sheet1$" 这里加 " 分数 " 两字什么作用 ?I'SQL 一般结构是 select 字段 from
19、表，意思是从指定的表中查询字段， 字段的理解可以是： 表 中的列名I'分数 是OO1.xls文件的sheetl第一行A列的字段名，SQL般以字段来识别每列数据'为什么要用复制的对象引用过来计算呢？I'因为 Sql 语句只是对源数据库的字段找到了符合条件的的数据，但不会自动复制到汇总表来，所以需要复制 copyI'注意 这里的 sheet1$" ， 001 文件的数据存放地上 sheet1 表，应当用方括号并加上 $I'如果源数据文件001不是excel，而是Access，则引用表时，不需要加方括号，也不要 $还有 , 这里 Execute 表示
20、什么作用 ?''Execute是执行SQL查询语句的意思如果不要字段也可以，那么在打开语句中加上： hdr=no'这样没有分数字段也可实现'SQL语句我换了形式，而且加上了hdr= no，即无需字段，而且我在SQL中用了 sum(fl) ，fl表示第一列数据'sheet1$a1:a10 "是只求 a1:a10 区域的和 "*使用VBA执行SQLsub test() ' 定义过程名称Dim i As Integer, j As Integer, sht As Worksheet 'i,j为整数变量； sht为 excel
21、 工作表对象变量，指向某一工作表Dim cn As New ADODB.ConnectionDim rs As New ADODB.Recordset '定义数据链接对象，保存连接数据库信息；请先添加定义记录集对象，保存数据表ADO引用Dim strCn As String ,strSQL as String '字符串变量strCn = "Provider=sqloledb;Server=服务器名称或IP地址;Database=数据库名称；Uid=用户登录名；Pwd=密码 ;"' 定义数据库链接字符串面的语句将读取数据表数据，并将它保存到 excel
22、 工作表中：画两张表想像一下，工作表为一张两维表，记录集也是一张两维表strSQL = "select 字段1,字段2 from 表名称”' 定义SQL查询命令字符串cn.Open strCn ' 与数据库建立连接，如果成功，返回连接对象 cnrs.Open strSQL, cn '执行strSQL所含的SQL命令，结果保存在 rs记录集对象中i = 1Set sht = ThisWorkbook.Worksheets("sheet1")把 sht 指向当前工作簿的 sheet1 工作表Do While Not rs.EOF 'sh
23、t.Cells(i, 1) = rs("列sht.Cells(i, 2) = rs(" rs.MoveNexti = i + 1'i当数据指针未移到记录集末尾时，循环下列操作字段 1"） ' 把当前记录的字段 1 的值保存到 sheet1 工作表的第 i 行第 1Loop' 循环rs.Close '关闭记录集， 至此，程序将把某数据表的字段 1 和字段 2 保存在 excel 工作表 sheet1 的第 1、字段 2"） ' 把当前字段 2 的值保存到 sheet1 工作表的第 i 行第 2 列把指针移向下一条记录
24、加 1，准备把下一记录相关字段的值保存到工作表的下一行2 列，行数等于数据表的记录数下面的语句将读取 excel 工作表数据，并将之简单计算后存入数据库，这里使用上面程序中的一些变量 假设分别读取工作表 sheet1 第 5 行至第 500 行的第 8 列和第 9 列已存在的数据，然后将它们相乘，并将积存入数据库的某个表strSQL="" ' 清空上面定义的变量for i=5 to 500 ' 循环开始， i 从5到 500strSQL=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