Recording VBA to change SQL Statement

[lhershco]

I am trying to record a VBA that will automate a specific SQL code like in all my power queries SQL statements.
in all of them i have a version number and i would like to have an automation that once i pick a version number it will insert that number to all power queries SQL statements.
The SQL code line is currently WHERE PD.PublishLogId = 52 in all power queries SQL statements and i want the to run a macro that will ask me which PublishLogId number i want it to be and then replace the 52 (or any other number currently there) with that new number i picked.
when i record a macro it ignores all my clicks and no VBA code is written even though i am recording .

 

[Trevor G]

VBA code to use an inputbox example is shown here. I have used a msgbox to return the number I tested when the inputbox ran. I don't know if this will help with you Power SQL queries though.

Sub intb()
Dim i As Integer
i = InputBox("Title", "Please enter your number")
'WHERE PD.PublishLogId = i
MsgBox "Integer number is " & i
End Sub

 

[John_w]

Welcome to MrExcel forums.

Try this macro on a copy of your workbook.

Public Sub Modify_SQL()

    Dim ws As Worksheet
    Dim listObj As ListObject
    Dim id As String
    
    id = InputBox("Enter Id number")
    
    If id <> "" Then
        For Each ws In ActiveWorkbook.Worksheets
            For Each listObj In ws.ListObjects
                listObj.QueryTable.CommandText = Left(listObj.QueryTable.CommandText, InStr(listObj.QueryTable.CommandText, "= ") + 1) & id
                listObj.QueryTable.Refresh
            Next
        Next
    End If

End Sub