Function Crate_Tables()
S1 = " Use B2 SELECT max(g13.akb_v_obl13) as 'akb', g13.offtake13,g13.procentakb13, g13.kl13,"
N1 = " t.obl,t.kategory,"
S2 = " t.god FROM B2.dbo.T1 t Left Join( SELECT sum(t.kg) / count(distinct t.kodKl) as 'offtake13', vsekg.klobl13 as 'akb_v_obl13',100 * count(distinct t.kodKl) / vsekg.klobl13 as 'procentakb13', count(distinct t.kodKl) as 'kl13',"
S3 = " t.god FROM B2.dbo.T1 t join (SELECT count(distinct t.kodKl) as 'klobl13',t.obl FROM B2.dbo.T1 t WHERE t.god = 13 And t.mes = 7 Group By t.obl ) AS vsekg on vsekg.obl = t.obl"
U1 = " WHERE t.god = 13 And t.mes = 7"
S4 = " Group By vsekg.klobl13,"
S5 = " t.god ) AS g13 on     g13.god = t.god"
N2 = " AND g13.obl = t.obl AND g13.kategory = t.kategory"
S6 = " GROUP BY  g13.offtake13,g13.procentakb13, g13.kl13,"
S7 = " t.god"
S = S1 + N1 + S2 + N1 + S3 + U1 + S4 + N1 + S5 + N2 + U1 + S6 + N1 + S7
MsgBox (S)
    DSNConn = "OLEDB;DRIVER=SQL Server;SERVER=Market6\M6;UID=ИМЯ;PWD=ПАРОЛЬ;APP=Microsoft Office 2003;WSID=MARKET-BOSS;DATABASE=B2"
     SQLQuery = S
    With ActiveSheet.QueryTables.Add(Connection:=DSNConn, Destination:=Range("$A$2"))
        .CommandType = xlCmdSql
        .CommandText = SQLQuery
        .Name = "База"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = True
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = False
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery = False
    End With
End Function
Но эта штука просто выгружает данные на лист эксель без сводной таблицы. 

У меня еще есть вариант
Sub qq
    Workbooks("Книга1").Connections.Add "Market6_M6 B2 T1", "", Array( _
        "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID=ИМЯ;Data Source=Market6\M6;Use Procedure for Prepare=1;Auto Translate=True" _
        , _
        ";Packet Size=4096;Workstation ID=MARKET6;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Cat" _
        , "alog=B2"), Array("""B2"".""dbo"".""T1"""), 3
End Sub