如何编写VBA宏以在MS Server 2008数据库上运行SQL查询,并将结果输出到电子表格中?
发布于 2013-10-28 16:20:16
Sub RunSQL(sConnectionString, sSqlQuery, sWorkSheet, nStartColumn, nStartRow)
Dim oConnection As New ADODB.Connection
Dim oRecordSet As New ADODB.Recordset
Dim sQuery As String
Dim intMaxCol As Integer
Dim intMaxRow As Integer
'Connection to SQL Server
Call oConnection.Open(sConnectionString)
' Get Recordset
Call oRecordSet.Open(sSqlQuery, oConnection, adOpenStatic, adLockReadOnly)
' Get SQL Query Column Count
intMaxCol = oRecordSet.Fields.Count
If oRecordSet.RecordCount > 0 Then
' Get SQL Query Row Count
intMaxRow = oRecordSet.RecordCount
' Paste data into sheet
Call Worksheets(sWorkSheet).Range(Worksheets(sWorkSheet).Cells(nStartRow, nStartColumn), Worksheets(sWorkSheet).Cells(intMaxRow + nStartRow, intMaxCol + nStartColumn)).CopyFromRecordset (oRecordSet)
End If
' Close objects
oRecordSet.Close
oConnection.Close
End SubCall RunSQL("Provider=SQLNCLI10;Server=[[SERVER]]\[[INSTANCE]];Database=[[DATABASE]];Trusted_Connection=Yes;", _
"SELECT TOP 1 CAST(UniqueId AS VARCHAR(40)), Name FROM Employee WITH (NOLOCK)", _
"Sheet1", _
2, _
3)如果有帮助,我是从一条涉及以下帖子的曲折路线上得到的:
https://stackoverflow.com/questions/19639946
复制相似问题