用VBA+SQL的交叉表方案，不够完善，求指教

xiaoangel

请看附件，数据表在“Report”页里， 汇总表在“OK”页里
想请教用进一步高级的SQL直接产生汇总表各行和各列的合计。谢谢


Sub TT()
      Dim cnn As Object, rs As Object
       Dim SQL As String
        Sheets("OK").Cells.Clear
        Set cnn = CreateObject("ADODB.Connection")
        cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes';Data Source=" & ThisWorkbook.FullName
        
        
        SQL = "TRANSFORM Sum([Report$].[Value($)]) AS [SumOfValue($)]SELECT [Report$].[Project] FROM [Report$] GROUP BY [Report$].[Project]PIVOT [Report$].[Month]"
        
   
        Set rs = cnn.Execute(SQL)
        With Sheets("OK")
        For i = 1 To rs.fields.Count
        .Cells(1, i) = rs.fields(i - 1).Name
         Next
        .Range("a2").CopyFromRecordset cnn.Execute(SQL)
        End With
        cnn.Close
       Set cnn = Nothing
       Set rs = Nothing
End Sub