# Linking named ranges to Power Query or using VBA to update a Power Query



## MikeMyers (Dec 12, 2019)

I need to update a Power Query using VBA or be able to define the query so that the code is linked to a named range in the same workbook.
I write dynamic SQL scripts (based on user input) in a worksheet, then in a single cell (range name = “sqlQuery”) I use the Textjoin function to compile the code into that cell.  Most are select queries but some are update queries to an ERP database running on Pervasive. 

Update Example:
    Update v_Inventory_mst3  set STANDARD_COST_QTY = '0' where Part = '0-0.250A' ;
    Update v_Inventory_mst3  set STANDARD_COST_QTY = '0' where Part = '000M-SS' ;
    select now() ;

The name of the Power Query is: Query1

I can do this successfully but manually and the query definition looks like this:
    = Odbc.Query("dsn=global_fah", "Update v_Inventory_mst3  set STANDARD_COST_QTY = '0'
    where Part = '0-0.250A' ; Update v_Inventory_mst3  set STANDARD_COST_QTY = '0'
    where Part = '000M-SS' ; select now() ; ")

The results are that the values get updated in the database as specified and the value in the query results is the last time I ran it.

If I can link the defined name range into the query definition then presumably all I would need to do is refresh it to process the next set of updates.

If I can’t link the defined name into the query definition but I can run a VBA script to update it from the current value in “sqlQuery” then all I would need to do is execute the macro.  This is the type of process I currently use for select queries, from a (non-Power Query) TableQuery.  This meth does not work for multiline update scripts to a Pervasive db.

VBA sample for a select query:
Public Sub QTY_VAR()

    '    Copy Content which contains a textjoin formual formual compiling many lines of code
    Dim sqlCode As String
    sqlCode = Range("sqlQVAR").Value

    '    Select Report which is a Query Table (not created through Power Query)
   Application.Goto Reference:="rptQVAR"

     With Selection.ListObject.QueryTable
        .CommandType = xlCmdSql
        .CommandText = sqlCode
        .Refresh BackgroundQuery:=False
    End With
    DoEvents

    If Err <> 0 Then MsgBox Err.Description

    Application.CutCopyMode = False

    End Sub


----------



## sandy666 (Dec 12, 2019)

try

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim con As WorkbookConnection
Dim Cname As String

For Each con In ActiveWorkbook.Connections
    If Left(con.Name, 8) = "Query - " Then
    Cname = con.Name
        With ActiveWorkbook.Connections(Cname).OLEDBConnection
            .BackgroundQuery = False  'or true, up to you
            .Refresh
        End With
    End If
Next
End Sub
```


----------



## MikeMyers (Dec 13, 2019)

Hi Sandy, I do not know if I hooked your code in correctly or not.  When I run it, it prompts me for a macro to run.  When I select Test, it fails on the '.Refresh BackgroundQuery:=False' line.  After that the original power will no longer run even though the code in it appears unchanged.  It doesn't matter if I run your code first and then run my macro or not, it still corrupts the original power query.

`Public Sub Test()
    ' Copy Content which contains a textjoin formual compiling many lines of code
Dim sqlCode As String
    sqlCode = Range("sqlTest").Value

    ' Select Report which is a Query Table (not created through Power Query)
    Application.Goto Reference:="Query1"

    With Selection.ListObject.QueryTable
.CommandType = xlCmdSql
.CommandText = sqlCode
.Refresh BackgroundQuery:=False
End With
    DoEvents

    If Err <> 0 Then MsgBox Err.Description

    Application.CutCopyMode = False

End sub`

Error message when I attempt to refresh the original power query after running the macro:

The command 'Odbc.Query("dsn=global_fah", "Update v_Inventory_mst3  set STANDARD_COST_QTY = '0' where Part = '0-0.250A' ; Update v_Inventory_mst3  set STANDARD_COST_QTY = '0' where Part = '000M-SS' ; select now() ; ")' isn't supported.


----------



## sandy666 (Dec 13, 2019)

post#2 put code into the Sheet code




it will start automatically if you change any cell on this sheet
btw. I don't know VBA so don't ask me about it


----------



## MikeMyers (Dec 13, 2019)

Making progress.  Yes, with that code on the sheet, any changes to that sheet will execute the current code in the power query.

However, the code is meant to be dynamic.  The new code is stored in a named range: sqlTest.  It could be hundreds of update statements or just a couple.

It needs to flow into the power query and replace the part highlighted in the pic:


----------



## sandy666 (Dec 13, 2019)

MikeMyers said:


> with that code on the sheet, any changes to that sheet will execute the current code in the power query.


I can say: *each* Query connection

Did you test post#2 code with *more* than one Query Connection?


----------



## MikeMyers (Dec 13, 2019)

I have no idea what that answer means.  Perhaps I failed to give enough background on my application of this tech/method (using Office 2016).  I was not looking for a way to trigger a refresh to the original power query.  

This is highly summarized:

In sheet1 I have a typical table query (of GLOBAL_FAH.v_Inventory_mst3) which lists all Parts (in column A) and their current Standard_Cost_Qty (in Column B).  In a column C the user can enter a new Standard_Cost_Qty and if they do, in column D, I have a formulas which creates an update script for that part to the new Standard_Cost_Qty.  In sheet2 I textjoin all those update scripts followed by a "SELECT NOW()" line into a single cell named "sqlTest".  When the user is done entering new Standard_Cost_Qty entries, they will push a button which will run some VBA code to execute the changes via the power query, which is in sheet3.  If it worked the user will see the current date and time in the power query results (see below pic).  *It is that VBA code that I am missing*.  The example of VBA code that I posted above is only useful for executing a table query containing a select script; it is useless for executing a power query.  And table queries will not execute an update script without causing an error, but power queries will do so without an error.

Sheet3:




Your VBA code simply executes a refresh of the power query if some change is made in sheet3, but no one is making changes in sheet3.  Also, all a refresh does is execute the changes made on a previous date; it fails to execute the new changes because the power query is not linked to "sqlTest".  If we can figure out a way to link it then a refresh should work perfectly.

So there are two possible solutions:

Solution A:  use VBA code to copy the data in "sqlTest" to the power query (Query1) replacing the code which is highlighted below:




Solution B:  Link "sqlTest" into the power query (Query1) so that the data in it replaces the code which is highlighted above, or I can modify the data in "sqlTest" so that is contains the entire string following the equal sign and link that into the power query.  Then a refresh should be able to execute the new updates.


----------



## sandy666 (Dec 13, 2019)

Too complicated "on paper" so I can't help
have a nice day


----------



## MikeMyers (Dec 13, 2019)

No, not really.  It's quite simple if someone just knows how to do it.  But thanks for trying.

Best regards.


----------



## sandy666 (Dec 13, 2019)

IMHO if someone has real example in the file 

Good luck


----------

