作者:乔山办公网日期:
返回目录:excel表格制作
.Parameters.Append .CreateParameter("@FromDate", adVarChar, 1, Len(CStr(Format(Me.FromDate, "yyyy-mm-dd"))), CStr(Format(Me.FromDate, "yyyy-mm-dd")))
我是这度样写的, 输入输出用1和知2即可
另外未定道义的错误信版息是VBA提示权,还是SQL里面的存储过程提示?
sub test()
Dim Conn As New ADODB.Connection
Dim strConn As String
Dim strSQL As String
Dim rs As New ADODB.Recordset
strConn = "Provider=sqloledb;Server=192.168.1.111;Database=db2014;Uid=用户来名;Pwd=密码自;"
Conn.Open strConn
Max_row = Range("A1").CurrentRegion.Rows.Count
for i=1 to Max_row
strSQL = "select name from sales2014 where id='" & range("A" & i).value & "'"
Conn.Execute strSQL
rs.Open strSQL, Conn, 1, 1
range("B" & i).value= rs(0)
rs.Close
Set rs = Nothing
next i
'关闭百数据度库
Conn.Close
end sub
查询结果就可以直接复制粘贴,存储过程没试过,一般都直接用代码调用存储过程,然后再保存到excel。
您好e799bee5baa6e997aee7ad94e58685e5aeb9337:
给您找了一段VBA的代码,请参考,未测试。
Sub test()
Dim strcon As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
strcon = "Driver={SQL Server};Server=**********;UID=**;PWD=*********;DataBase=***"
cn.Open strcon
strsql = "exec 存储过程名 '" & Sheet2.Cells(2, 2).Value & " ','" & Sheet2.Cells(3, 2).Value & " '" '参数可选
rs.Open strsql, cn, adOpenDynamic, adLockBatchOptimistic
r = 6
Sheet2.Range(Cells(6, 1), Cells(50000, 20)).ClearContents
For i = 0 To rs.Fields.Count - 1
Sheet2.Cells(5, i + 1).Value = rs.Fields(i).Name
Next i
While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
Sheet2.Cells(r, i + 1).Rows.Value = rs.Fields(i).Value
Next i
r = r + 1
rs.MoveNext
Wend
End Sub