VBA + ADO 调用存储过程_vba 调用存储过程_stone0823的博客

调用 SQL Server 存储过程

连接字符串

ADO 连接到数据库，需要连接字符串。在 Windows 上连接到数据库，可以利用数据库连接向导，帮助构建连接字符串，不用死记。方法如下：在任意位置新建一个扩展名为 udl 的文件，然后双击 udl 文件，启动数据库连接向导。假设需要连接到 SQL Server 数据库，首先选择数据库驱动：
点击 下一步 按钮，输入数据库服务器名称和登录的方式：
点击 测试连接 按钮，测试是否连接成功。没有问题，点击确定按钮完成。然后用可以打开文本文件的编辑器打开 udl 文件，文件保存的就是我们需要的连接字符串。
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=stonetest;
Data Source=STONEWM-PC\SQLEXPRESS
在 sql server 中创建存储过程
 
本例设计的场景是在存储过程中计算物料在某个月份的进出存数据。sql 语句可以用子查询或者 Common Table Expression。完成后的存储过程代码如下： 
CREATE PROCEDURE [dbo].[spGetStockBalances]
	@y int,
	@m int
	BEGIN
		WITH merged AS (
			SELECT
				d.DocNo,
				d.MaterialNo,
				d.Qty,
				h.MovementType,
				h.DocDate,
				t.InOutSign,
				YEAR(h.DocDate) AS TxYear,
				MONTH(h.DocDate) AS TxMonth,
				CASE InOutSign WHEN '+' THEN ISNULL(d.Qty, 0) else -1 * ISNULL(d.Qty, 0) END AS ActualQty
				stock_movement_details d
				INNER JOIN stock_movement_headers h ON d.DocNo = h.DocNo
				LEFT OUTER JOIN movement_types t ON h.MovementType = t.MovementTypeID
		calculated AS (
			SELECT
				MaterialNo,
				InOutSign,
				TxYear,
				TxMonth,
				ActualQty,
				CASE WHEN TxYear < @y
				OR (
					TxYear = @y
					AND TxMonth < @m
				) THEN ActualQty ELSE 0 END AS BeginQty,
				CASE WHEN TxYear = @y
				AND TxMonth = @m
				AND InOutSign = '+' THEN ActualQty ELSE 0 END AS StockIn,
				CASE WHEN TxYear = @y
				AND TxMonth = @m
				AND InOutSign = '-' THEN ActualQty ELSE 0 END AS StockOut
				merged
		SELECT
			MaterialNo,
			SUM(BeginQty) AS BeginQty,
			SUM(StockIn) AS StockIn,
			SUM(StockOut) AS StockOut,
			SUM(BeginQty+StockIn+StockOut) AS EndQty
			calculated
		GROUP BY
			MaterialNo;
	END;
存储过程中有两个变量 @y 表示年度，@m 表示月份。 
VBA 调用 SQL Server 存储过程
 
在 VBE 环境中，添加对 Microsoft ActiveX Data Objects 对象的引用，然后编写如下代码： 
Option Explicit
Public Sub GetStockBalance()
    Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    conn.ConnectionString = "Provider=SQLOLEDB.1;" & _
        "Integrated Security=SSPI;Persist Security Info=False;" & _
        "Initial Catalog=stonetest;Data Source=STONEWM-PC\SQLEXPRESS"
    conn.Open
    With cmd
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.spGetStockBalances"
        .Parameters.Append .CreateParameter("@y", adInteger, adParamInput, , 2008)
        .Parameters.Append .CreateParameter("@m", adInteger, adParamInput, , 4)
    End With
    Dim rs As New ADODB.Recordset
    Set rs = cmd.Execute
    ' headers
    Dim col As Integer
    Dim startCell As Range
    col = 0
    Set startCell = Sheet1.Range("A1")
    For col = 0 To rs.Fields.Count - 1
        startCell.Offset(0, col).Value = rs.Fields(col).Name
    ' lines
    startCell.Offset(1, 0).CopyFromRecordset rs
    rs.Close
    conn.Close
    Set rs = Nothing
    Set cmd = Nothing
    Set conn = Nothing
End Sub
调用 MySQL 存储过程
 
配置 ODBC 数据源
 
ADO 连接 MySQL 数据库，使用 ODBC 数据源。在 Windows 的 Run 窗口中输入 odbcad32 命令，打开配置连接界面。确保已经下载和安装 ODBC for MySQL 驱动程序：
  
然后在界面中配置数据源： 
编写存储过程
 
MySQL 的存储过程与 T-SQL 语法有一些区别，包括不支持 CTE，所以需要用子查询来解决嵌套调用。 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `spStockBalances`(
	y int,
    m int
BEGIN
	SELECT
		MaterialNo,
		SUM(BeginQty) AS BeginQty,
		SUM(StockIn) AS StockIn,
		SUM(StockOut) AS StockOut,
		SUM(BeginQty+StockIn+StockOut) AS EndQty
			SELECT
				MaterialNo,
				TxYear,
				TxMonth,
				InOutSign,
				ActualQty,
				CASE WHEN (TxYear < y)
				OR (
					TxYear = y
					AND TxMonth < m
				) THEN ActualQty ELSE 0 END AS BeginQty,
				CASE WHEN TxYear = y
				AND TxMonth = m
				AND InOutSign = '+' THEN ActualQty ELSE 0 END AS StockIn,
				CASE WHEN TxYear = y
				AND TxMonth = m
				AND InOutSign = '-' THEN ActualQty ELSE 0 END AS StockOut
					SELECT
						d.MaterialNo,
						d.Qty,
						h.MovementType,
						h.DocDate,
						t.InOutSign,
						YEAR(h.DocDate) AS TxYear,
						MONTH(h.DocDate) AS TxMonth,
						CASE WHEN t.InOutSign = '+' THEN ifnull(d.Qty, 0) ELSE -1 * ifnull(d.Qty,0) END AS ActualQty
						stock_movement_details AS d
						INNER JOIN stock_movement_headers AS h ON d.DocNo = h.DocNo
						LEFT JOIN movement_types AS t ON h.MovementType = t.MovementTypeID
				) AS joined
		) AS calculated
	GROUP BY
		MaterialNo
	ORDER BY
		MaterialNo;
END$$
DELIMITER ;
调用 MySQL 存储过程
 
根据测试，用 Command 命令创建参数并调用存储过程的结果不正确，所以直接用 Recordset Open 方法能返回正确的结果。 
Option Explicit
Public Sub GetStockBalance()
    Dim conn As New ADODB.Connection    
    Dim connStr As String
    connStr = "DRIVER={MySQL ODBC 8.0 Unicode Driver}; " & _
        "SERVER=localhost; PORT=3306; DATABASE=stonetest; USER=root; PASSWORD=pwd;OPTIONS=3"
    conn.ConnectionString = connStr
    conn.Open
    Dim rs As New ADODB.Recordset
    rs.Open "call spStockBalances(2008,4)", conn, adOpenStatic, adLockReadOnly
    ' headers
    Dim col As Integer
    Dim startCell As Range
    col = 0
    Set startCell = Sheet1.Range("A1")
    For col = 0 To rs.Fields.Count - 1
        startCell.Offset(0, col).Value = rs.Fields(col).Name
    ' lines
    startCell.Offset(1, 0).CopyFromRecordset rs
    ' clean up
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub
                    最近对 pandas, Power Query 和 SQL 的数据处理和加工进行了一些类似的处理，希望通过比较看到各种处理方式的优点。之前笔者用的比较多的是 VBA + ADO，VBA + ADO 的方式其实离不开 Sql 语句，这种方式也有非常方便：前台有 Excel，后台有数据库就能实现...
				首先，必须严肃脸说明的是，对于普通 Excel 使用者而言，VBA、SQL 以及以
后提及的 ADO 并不是非学不可的，非学不可的是基础操作、函数、透视表、图表……
然而大数据时代，对于另外相当一部分表族而言，Excel 用久了，慢慢的，会意
识到一个大问题；曾经在你心中无比强大的 Excel 函数，原来只适合小数据的腾挪
躲闪；当数据量稍大后，函数这货就像未嗑士力架的姚明——不来劲的很哩。
SQL In Excel 则可以解决函数处理大数据效率低下的问题，嗯~使用 SQL 语言，
你甚至可以将 Excel 作为前台数据看板，数据库（例如 ACCESS）作为后台数据储
存仓库，进而储存、分析远超 Excel 体积的数据量,本书描述了ADO和VBA喝SQL in Excel平台的运用
				以下是一个VBA查询总账发生额及余额表的例子,掌握了它,我们就可以在EXCEL上直接得到各账套的发生额及余额表了,但是我对它并不太满意,我想要的效果是直接得到全年各月余额(损益类的为发生额)的表,这样我做集团合并报表时会方便很多,正在研究中,若能成功再发布上来:
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
"建立公用的数据源...
				ADO是什么
首先，我们要介绍下ADO。
ADO (ActiveX Data Objects，ActiveX数据对象）是微软提出的应用程序接口，用以实现访问关系或非关系数据库中的数据……更多概念信息可以自行搜索。
之所以要学习ADO，一个原因是ADO自身的一些属性和方法对于数据处理是极其有益的；更重要的原因是，在EXCEL VBA中，一般只有通过ADO，才可以使用强大的SQL查询语言访问外部数据源，进而查、改、增、删相关数据源中的数据。
延伸在具体编程操作上，就形成了四步走发展战略：
VBA引用ADO类库
不过最主要的还是执行效率和SQL 代码封装。
特别是 SQL 代码封装功能，如果没有存储过程，在外部程序访问数据库时（例如 PHP），要组织很多 SQL 语句。特别是业务逻辑复杂的时候，一大堆的 SQL 和条件夹杂在 PHP 代码中，让人不寒而栗。
    strRegex = "\[(.*?)\]"
    strRegex = Replace(strRegex, "[", "\[")
    strRegex = Replace(strRegex, "]", "\]")
    strRegex = Replace(strRegex, ".", "\.")
    strRegex = Replace(strRegex, "(", "\(")
    strRegex = Replace(strRegex, ")", "\)")
    strRegex = Replace(strRegex, "*", "\*")
    strRegex = Replace(strRegex, "+", "\+")
    strRegex = Replace(strRegex, "?", "\?")
    strRegex = Replace(strRegex, "^", "\^")
    strRegex = Replace(strRegex, "$", "\$")
    strRegex = Replace(strRegex, "{", "\{")
    strRegex = Replace(strRegex, "}", "\}")
    strRegex = Replace(strRegex, "|", "\|")
    strRegex = Replace(strRegex, "/", "\/")
    strRegex = Replace(strRegex, "\", "\\")
    strRegex = Replace(strRegex, " ", "\s")
Dim regEx As New RegExp
    regEx.Pattern = strRegex
    regEx.Global = True
    regEx.IgnoreCase = False
    regEx.Multiline = TrueDim strInput As String
    strInput = ActiveDocument.Range.Text
Dim strReplace As String
    strReplace = ""Dim strOutput As String
    strOutput = regEx.Replace(strInput, strReplace)ActiveDocument.Range.Text = strOutput
End Sub