ActiveWorkbook.Queries.Add Filter Criteria

Hello,

I have a code in which i use ActiveWorkbook.Queries.Add to import a CSV file. But while importing i need to filter only those rows containing value given by user. Below is the code. I have Highlighted and Bold the portion where i give the filter criteria as [Object ID] = 140. But what i want is, instead of  [Object ID] = 140, I want Object ID = xObjID, where xObjID is input taken from user xObjID = InputBox(“Enter Object ID”). But its not working. Is there any alternative. Please help.

 

ActiveWorkbook.Queries.Add Name:=”S2KVTQ_VTQTimeTable”, Formula:= _

        “let” & Chr(13) & “” & Chr(10) & ”    Source = Csv.Document(File.Contents(“”” & xFileName & “””),[Delimiter=””,””, Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),” & Chr(13) & “” & Chr(10) & ”    #””Changed Type”” = Table.TransformColumnTypes(Source,{{“”Column1″”, Int64.Type}, {“”Column2″”, Int64.Type}, {“”Column3″”, type number}, {“”Column4″”, type datetime}, {“”Column5″”, type nu” & _

        “mber}, {“”Column6″”, Int64.Type}}),” & Chr(13) & “” & Chr(10) & ”    #””Removed Other Columns”” = Table.SelectColumns(#””Changed Type””,{“”Column2″”, “”Column3″”, “”Column4″”}),” & Chr(13) & “” & Chr(10) & ”    #””Renamed Columns”” = Table.RenameColumns(#””Removed Other Columns””,{{“”Column2″”, “”Object ID””}, {“”Column3″”, “”Value””}, {“”Column4″”, “”Date & Time””}}),” & Chr(13) & “” & Chr(10) & ”    #””Filtered Rows”” = Table.SelectRows(#””Rename” & _

        “d Columns””, each ([Object ID] = 140))” & Chr(13) & “” & Chr(10) & “in” & Chr(13) & “” & Chr(10) & ”    #””Filtered Rows”””

 

By: Jyothis

3 thoughts on “ActiveWorkbook.Queries.Add Filter Criteria

  1. stevej

    Longer answer : lose the very ugly string concat logic altogether… 🙂

    [.] Save the PQ query mostly “as is” in a worksheet cell or in a file: e.g. MyCsvImportQuery.pq.txt
    Fetch it, tweak it and set the Formula param for you new query

    let
    //Replace the %parameters% in VBA
    CsvFileName = “%xFileName%”,
    ObjectID = %xObjID%,

    Source = Csv.Document(File.Contents(CsvFileName),
    [Delimiter=”,”, Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #”Changed Type” = Table.TransformColumnTypes(Source, {
    {“Column1”, Int64.Type}, {“Column2”, Int64.Type},
    {“Column3”, type number}, {“Column4”, type datetime},
    {“Column5”, type number}, {“Column6″, Int64.Type}
    }),
    #”Removed Other Columns” = Table.SelectColumns(#”Changed Type”, {“Column2”, “Column3”, “Column4″}),
    #”Renamed Columns” = Table.RenameColumns(#”Removed Other Columns”,{{“Column2”, “Object ID”}, {“Column3”, “Value”}, {“Column4”, “Date & Time”}}),
    #”Filtered Rows” = Table.SelectRows(#”Renamed Columns”, each ([Object ID] = ObjectID))
    in
    #”Filtered Rows”

    For the file approach:

    [.] In VBA Tools-References, add a reference to “Microsoft Scripting Runtime”
    [.] Define a global variable:

    Public FSO As New FileSystemObject

    [.] Read the file and “set” the params:

    Dim qry as String
    With FSO.OpenTextFile(“MyCsvImportQuery.pq.txt”)
    qry = .ReadAll
    .Close
    With
    qry = Replace(qry, “%xFileName%”, xFileName)
    qry = Replace(qry, “%xObjID%”, xObjID)

    [.] Adjust query create line:

    ActiveWorkbook.Queries.Add Name:=”S2KVTQ_VTQTimeTable”, Formula:=qry

    Note: I don’t have Excel 2016 so I don’t have the Workbook.Queries collection to experiment on,
    but you may be able to simply retrieve the “Formula” text from an existing PQ query and just alter that (the best way, imo).

Leave a Reply

Your email address will not be published. Required fields are marked *