Excel VBA setting variable for power query from textbox-CodePudding

I want to open excel and have a textbox pop up. whatever string the user inputs in the text box will be the variable that is used in Power Query data loaded into the worksheet.

I currently have this M code:

let
Source = Sql.Databases("BMCCL006.DS.BUILDWITHBMC.COM\DW", [HierarchicalNavigation=true]),
trendsql = Source{[Name="trendsql"]}[Data],
dbo = trendsql{[Schema="dbo"]}[Data],
poeh1 = dbo{[Name="poeh"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(poeh1,{"pono", "posuf", "stagecd", "shiptonm", "enterdt", "whse", "vendno", "takenby"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [stagecd] < 3),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [takenby] = "mytextboxstring"),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"enterdt", type date}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type", each true),
#"Sorted Rows" = Table.Sort(#"Filtered Rows2",{{"enterdt", Order.Descending}})
in
#"Sorted Rows"

I want to use the textbox ("mytextboxstrig") to be the value of to filter the rows by in [takenby].

can this be done through VBA so that this query runs and loads to worksheets("Sheet1")?

Thank you.

CodePudding user response：

Create a range name, here aaa

Use VBA to populate it

Private Sub Workbook_Open()
Range("aaa").Value = _
InputBox(Prompt:="Type the value you want")
End Sub

Refer to the named range in powerquery

NameValue= Excel.CurrentWorkbook(){[Name="aaa"]}[Content]{0}[Column1],
#"Filtered Rows" = Table.SelectRows(#"YourPriorStepName", each ([takenby] = NameValue))