Excel VBA setting variable for power query from textbox

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:

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}})
#"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.

1 answer

  • answered 2022-01-19 16:48 horseyride

    1 Create a range name, here aaa

    2 Use VBA to populate it

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

    3 Refer to the named range in powerquery

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

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum