将SSRS数据作为数据集提取的Excel VBA代码

Sub ExtractSSRSData()
    Dim conn As Object
    Dim rs As Object
    Dim strSQL As String
    Dim strConnString As String
    Dim i As Integer
    Dim j As Integer
    
    ' 设置连接字符串
    strConnString = "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDatabaseName;User ID=YourUsername;Password=YourPassword;"
    
    ' 设置查询语句
    strSQL = "SELECT * FROM YourTableName"
    
    ' 创建连接对象
    Set conn = CreateObject("ADODB.Connection")
    
    ' 打开数据库连接
    conn.Open strConnString
    
    ' 创建记录集对象
    Set rs = CreateObject("ADODB.Recordset")
    
    ' 执行查询语句
    rs.Open strSQL, conn
    
    ' 将数据写入Excel工作表
    For i = 1 To rs.Fields.Count
        Cells(1, i).Value = rs.Fields(i - 1).Name
    Next i
    
    For i = 2 To rs.RecordCount + 1
        For j = 1 To rs.Fields.Count
            Cells(i, j).Value = rs.Fields(j - 1).Value
        Next j
        rs.MoveNext
    Next i
    
    ' 关闭记录集和连接对象
    rs.Close
    conn.Close
    
    ' 释放对象
    Set rs = Nothing
    Set conn = Nothing
End Sub