none
Excel 2016 Power Query: What is VBA code to edit and save same query? RRS feed

  • Question

  • This is a standalone Excel 2016 running on a PC.   

    This is sample CSV with 2 columns, 4 rows called "test.csv"

    state   zip

    CA      92129

    CA      92128

    IL        60660

    IL        60659

    This is recorded macro in workbook "macro.xlsm" for the first query to import the CSV and filter by IL:

    ActiveWorkbook.Queries.Add Name:="test", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source =    Csv.Document(File.Contents(""C:\test.csv""),[Delimiter="","", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.Transfor" & _
            "mColumnTypes(#""Promoted Headers"",{{""state"", type text}, {""zip"", Int64.Type}})," & Chr(13) & "" & Chr(10) & _

    "    #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", each ([state] = ""IL""))" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"""

    Workbooks("macro.xlsm").Connections.Add2 "Query - test", _
            "Connection to the 'test' query in the workbook.", _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=test;Extended Properties=" _
            , """test""", 6, True, False

    -----------------------

    Later I duplicate the query "test" in "macro.xlsm" as new "test (2)" query and add the "Group By" state to sum the zip column.

        ActiveWorkbook.Queries.Add Name:="test (2)", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\test.csv""),[Delimiter="","", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.Transfor" & _
            "mColumnTypes(#""Promoted Headers"",{{""state"", type text}, {""zip"", Int64.Type}})," & Chr(13) & "" & Chr(10) & _

    "    #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", each ([state] = ""IL""))," & Chr(13) & "" & Chr(10) & _

    "    #""Grouped Rows"" = Table.Group(#""Filtered Rows"", {""state""}, {{""zip_sum"", each List.Sum([zip]), type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Grouped Rows"""

        Workbooks("macro.xlsm").Connections.Add2 "Query - test (2)", _
            "Connection to the 'test (2)' query in the workbook.", _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=test (2);Extended Properties=" _
            , """test (2)""", 6, True, False

    The question is, instead of duplicating the query and having to reload the CSV just to add a Group By action, what is the code to modify the first query with the Group By action, and close the editor window and "Keep" the change.


    • Edited by kkaax Monday, March 19, 2018 3:28 PM clarity
    Sunday, March 18, 2018 1:34 AM

Answers

  • Your clue led me to a solution.  I had to start the recorder, then rightclick the first query and choose "References" to create the 2nd query.   It recorded the code as

        ActiveWorkbook.Queries.Add Name:="test (2)", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = test," & Chr(13) & "" & Chr(10) & "    #""Grouped Rows"" = Table.Group(Source, {""state""}, {{""zip_sum"", each List.Sum([zip]), type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Grouped Rows"""
        Workbooks("macro.xlsm").Connections.Add2 "Query - test (2)", _
            "Connection to the 'test (2)' query in the workbook.", _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=test (2);Extended Properties=" _
            , """test (2)""", 6, True, False

    I don't know why the recorder inserts so much junk characters like " & Chr(13) & "" & Chr(10) & "

    which i had to manually delete.

    Wednesday, March 21, 2018 3:05 PM

All replies

  • Now that's an exotic use of PowerQuery :)

    If I understood your question right you need to change the string

    "    Source =    Csv.Document..."

    to 

    "    Source =    #""Query - test"","

    This way you'll be referencing the output from first query into the second one.

    Wednesday, March 21, 2018 10:13 AM
  • Thanks I replace the 2nd query as follows, but when executing the Connections.Add2 line, it gives error:

    Expression.Error: The import Query - test matches no exports. Did you miss a module reference?

        ActiveWorkbook.Queries.Add Name:="test (2)", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = #""Query - test""," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.Transfor" & _
            "mColumnTypes(#""Promoted Headers"",{{""state"", type text}, {""zip"", Int64.Type}})," & Chr(13) & "" & Chr(10) & _

    "    #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", each ([state] = ""IL""))," & Chr(13) & "" & Chr(10) & _

    "    #""Grouped Rows"" = Table.Group(#""Filtered Rows"", {""state""}, {{""zip_sum"", each List.Sum([zip]), type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Grouped Rows"""

        Workbooks("macro.xlsm").Connections.Add2 "Query - test (2)", _
            "Connection to the 'test (2)' query in the workbook.", _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=test (2);Extended Properties=" _
            , """test (2)""", 6, True, False

    Wednesday, March 21, 2018 2:45 PM
  • Hi kkaax

    The reason is that you use Chr(13)  anf Chr(10). which are not recognized by PQ as correct function or query/step names. You should use #(cr) for "carriage return" and #(lf) for "line feed" keywords instead


    Maxim Zelensky Excel Inside

    Wednesday, March 21, 2018 3:04 PM
  • Your clue led me to a solution.  I had to start the recorder, then rightclick the first query and choose "References" to create the 2nd query.   It recorded the code as

        ActiveWorkbook.Queries.Add Name:="test (2)", Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    Source = test," & Chr(13) & "" & Chr(10) & "    #""Grouped Rows"" = Table.Group(Source, {""state""}, {{""zip_sum"", each List.Sum([zip]), type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Grouped Rows"""
        Workbooks("macro.xlsm").Connections.Add2 "Query - test (2)", _
            "Connection to the 'test (2)' query in the workbook.", _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=test (2);Extended Properties=" _
            , """test (2)""", 6, True, False

    I don't know why the recorder inserts so much junk characters like " & Chr(13) & "" & Chr(10) & "

    which i had to manually delete.

    Wednesday, March 21, 2018 3:05 PM
  • Oh, got it. Yes, I missed that this is VBA :) 

    Maxim Zelensky Excel Inside

    Wednesday, March 21, 2018 5:01 PM