我正在将数据表从 sql-server 移动到 Excel 中。
我不需要浏览记录集,只是为了获取数据并将其粘贴到工作表中。
我是否为记录集使用了正确的参数Open
方法?
Dim recSet As ADODB.Recordset
Set recSet = New ADODB.Recordset
aConnection.Open
recSet.Open stringSQL, aConnection, adOpenForwardOnly, adLockReadOnly, adCmdText
wb.Sheets(sName).Cells(1, 1).CopyFromRecordset recSet
recSet.Close
If Not (recSet Is Nothing) Then
If (recSet.State And 1) = 1 Then recSet.Close
Set recSet.ActiveConnection = Nothing
Set recSet = Nothing
End If
这是我用来从 MSSQLServer 提取数据的方法,也许它对您有用:
Sub test()
Dim Connection As ADODB.Connection
Dim rs As ADODB.Recordset
Dim QT As Excel.QueryTable
Dim ConnectionString As String
Dim SQL As String
Set Connection = New ADODB.Connection
Set rs = New ADODB.Recordset
ConnectionString = ""
SQL = "SELECT * FROM SomeTable"
Connection.Open ConnectionString
rs.Open SQL, Connection, adOpenStatic, adLockReadOnly
Set QT = ActiveSheet.QueryTables.Add(rs, ActiveSheet.Cells(1, 1))
QT.Refresh: rs.Close: QT.Delete: Connection.Close
End Sub
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)