Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim xlSheet As Worksheet
Dim objPivotCache As PivotCache

rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Planning;Data Source=MULTIPOINT8"

cn.Open
rs.Open "Select nameBrand as Брэнд from Dictionaries.Brands ", cn
'MsgBox rs.GetString

Set xlSheet = ActiveWorkbook.Sheets.Add
    With xlSheet
        .Name = "Сводная"
    
        Set objPivotCache = ThisWorkbook.PivotCaches.Add(xlExternal)          'Создаю сводную таблицу с внешним источником данных (xlExternal)
        Set objPivotCache.Recordset = rs                                  'Присваиваю источник данных рекордсет

        .PivotTables.Add PivotCache:=objPivotCache, TableDestination:=Range("D4"), TableName:="Сводная"           'Создаем сводную
        
    End With
    rs.Close
    Set rs = Nothing