none
Group By clause CommandText While writing a macro in excel RRS feed

  • Question

  • HI

    I am using macro to read a data from another excel and doing some filtration. I want to apply some sum formula based on data and I am  using group by clause. but to my surprise it is not working in VBA

     ActiveWorkbook.Queries.Add Name:="ProjInfo", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""FilePath""), null, true)," & Chr(13) & "" & Chr(10) & "    ProjInfo_Sheet = Source{[Item=""ProjInfo"",Kind=""Sheet""]
        Sheets.Add After:=ActiveSheet
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=ProjInfo" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = "SELECT [Client SubPortfolio],[Client Portfolio Region],[Project Name],[Project Types],[Client Lead] FROM [ProjInfo]"

    Want to know  how to use group by or join


    Monday, August 8, 2016 9:32 AM

Answers

  • Hi Deepak,

    >> I am  using group by clause. but to my surprise it is not working in VBA

    How did you use group by clause? Based on your code, I did not find you use group by clause. In my option, I could try to achieve this query manually from Excel, and record the steps. For a simple demo code, you could refer below:

        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
            "ODBC;DSN=Excel Files;DBQ=C:\Users\v-tazho\Desktop\Copy of Test.xlsx;DefaultDir=C:\Users\v-tazho\Desktop;DriverId=1046;MaxBufferSize=" _
            ), Array("2048;PageTimeout=5;")), Destination:=Range("$A$1")).QueryTable
            .CommandType = 0
            .CommandText = Array( _
            "SELECT `Query$`.X, Sum(`Query$`.Y)" & Chr(13) & "" & Chr(10) & "FROM `C:\USERS\XX\DESKTOP\Copy of Test.xlsx`.`Query$` `Query$`" & Chr(13) & "" & Chr(10) & "GROUP BY `Query$`.X" _
            )
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "Table_Query_from_Excel_Files"
            .Refresh BackgroundQuery:=False
        End With

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, August 9, 2016 5:50 AM