从Excel运行存储过程
问题描述:
我想从Excel运行存储过程。我知道如何做到这一点而不使用动态日期,但我需要日期范围是动态的。从Excel运行存储过程
Sub TestStoredProcedure()
Dim CServer As String
Dim CDatabase As String
Dim CLogon As String
Dim CPass As String
Dim StartDate As Date
Dim EndDate As Date
Dim TStartDate As String
Dim TEndDate As String
CServer = "111111" ' Your server name here
CDatabase = "111111" ' Your database name here
CLogon = "11111111" ' your logon here
CPass = "111111" ' your password here
Dim Cmd1 As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim intTemp As Integer
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = cn
Cmd1.CommandText = "callstatisticsbyQ"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(0).Value = Worksheets("Sheet2").Range("A1")
Cmd1.Parameters(1).Value = Worksheets("Sheet2").Range("A2")
Cmd1.Parameters(2).Value = Worksheets("Sheet2").Range("A3")
Set rs = Cmd1.Execute()
rs.Open Cmd1
Worksheets("Procedure Export").Range("A1").CopyFromRecordset rs
Call DumpSP("prcGetData", "", "", Worksheets("Procedure Export").Range("A1"))
End Sub
我得到一个错误说一些关于用户定义类型没有定义。
答
要使用ADO,请单击VBA IDE中的工具 - >引用&勾选“Microsoft ActiveX Data Objects” - 最好是其最高版本。
此外,您使用cn
作为连接,但它没有在该子中定义(假设它不是全局的)&您可能需要Set Cmd1.ActiveConnection = cn
。
此外看一看this,它定义了输入(adParaminput
)预先PARAMATERS而不是使用.Refresh
这是非常低效的(需要前往服务器)例如
更新:
rem for create procedure callstatisticsbyQ (@i int, @c varchar(10)) as select 1234;
Dim cn As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set Cmd1 = New ADODB.Command
Set Cmd1 = New ADODB.Command
cn.Open "Provider=SQLNCLI10;Server=1.2.3.4;Database=x;Uid=x; Pwd=x;"
Set Cmd1.ActiveConnection = cn
Cmd1.CommandText = "callstatisticsbyQ"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Append Cmd1.CreateParameter("p1", adInteger, adParamInput, , Worksheets("Sheet2").Range("A1"))
Cmd1.Parameters.Append Cmd1.CreateParameter("p2", adVarChar, adParamInput, 20, Worksheets("Sheet2").Range("A2"))
Set rs = Cmd1.Execute()
MsgBox rs(0)
rs.Close
cn.Close
感谢让我进一步但仍然不工作:/ – 2011-05-04 20:43:02
调用DumpSP(“prcGetData”,“”,“”,工作表(“过程导出”)。范围(“A1”)) – 2011-05-04 20:43:18
更新了一个示例 – 2011-05-04 20:58:23