Dim cnn As New ADODB.Connection 
    Dim rst As New ADODB.Recordset 

    cnn.Open "..."

    rstRecordset.Open "...", cnn

    Set PivotCache = ActiveWorkbook.PivotCaches.Add( _
      SourceType:=xlExternal)
    PivotCache.MaintainConnection = False

    Set PivotCache.Recordset = rstRecordset
    PivotCache.Refresh 

    rst.Close
    cnn.Close 

    Set rst = Nothing
    Set cnn = Nothing

Public Sub CreatePivotTable()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim objPivotCache As PivotCache
    Dim objPivotTable As PivotTable
    
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    cnn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
      "Persist Security Info=False;" & _
      "Initial Catalog=Northwind;" & _
      "Data Source=(local)"
    
    rst.Open "SELECT TOP 10 * FROM Orders", cnn, _
      adOpenStatic, adLockReadOnly
    
    Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
      SourceType:=xlExternal)

    Set objPivotCache.Recordset = rst

    With objPivotCache
        .CreatePivotTable TableDestination:=Worksheets("Лист1").Range("A3"), _
          TableName:="tblOrders"
    End With

    With Worksheets("Лист1").PivotTables("tblOrders")
        .SmallGrid = False
        With .PivotFields("OrderID")
            .Orientation = xlRowField
            .Position =  1 
        End With
        With .PivotFields("OrderDate")
            .Orientation = xlColumnField
            .Position =  1 
        End With
        With .PivotFields("Freight")
            .Orientation = xlDataField
            .Position =  1 
        End With
    End With
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Sub