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

[MikeMyers]

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]

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]

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]

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]

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]

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]

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]

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

 

[MikeMyers]

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

Best regards.

 

[sandy666]

IMHO if someone has real example in the file

Good luck