How do I modify query in Excel 2013 when created in Excel 2016 Power Query

2
votes

I created a workbook in Excel 2016 using Power Query. I'm using code to change the query. But I want to be able to run this in Excel 2013 too. How do I modify my code to access the query?

This is my code works in Excel 2016 but not in 2013 or older. 

Sub UpdateQuery()
    Dim CmdText As String

    'Compile error here "User Defined Type not defined"
    Dim qry As WorkbookQuery

    'Compile error here "Method or data member not found"
    Set qry = ThisWorkbook.Queries("CurrentQuery")
    CmdText = qry.Formula

    CmdText = ... make a change

    qry.Formula = CmdText

End Sub

This code runs fine in Excel 2013 but doesn't get me what I want.

Sub UpdateQuery2013()
    Dim cmdText As String

    Dim lo As ListObject
    Set lo = ws.ListObjects("CurrentTable")
    CmdText = lo.QueryTable.CommandText

    CmdText = ... make a change

    lo.QueryTable.CommandText = cmdText

End Sub

The above returns: Command text: SELECT * FROM [QueryCurrent] Connection string: Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=QueryCurrent;Extended Properties=""
Command type: SQL

What I really want is to modify QueryCurrent. How do I do that?

1
excelpowerqueryvba

1 Answers

0
votes

This is a workaround. I don't know if there's any way to modify the original (Power Query) query from VBA using Excel 2013.

I finally realized that I can simply avoid using the Power Query editor. Enter the query in Excel same as in previous versions: on the ribbon > Data > Connections > Properties > Definition tab > enter query and connection string there. Then use the Excel 2013 code to modify.

Sub UpdateQuery2013()
    Dim cmdText As String

    Dim lo As ListObject
    Set lo = ws.ListObjects("CurrentTable")
    CmdText = lo.QueryTable.CommandText

    CmdText = ... make a change

    lo.QueryTable.CommandText = cmdText

End Sub

Works fine.