Excel VBA setting variable for power query from textbox

Question

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.

Can you use VBA to run that string as-is? If you can post that code than adding in the substitution is easier for us... — Tim Williams
– Tim Williams, Commented Jan 19, 2022 at 16:43
Thank you for the quick response. I am somewhat new to VBA and have not mastered importing data with VBA. I think this is what you are asking for as far as VBA code for the user inputbox: Sub username() Dim UNAME As String Dim MyInput As String MyInput = InputBox("PLEASE ENTER YOUR USER INITIALS HERE", "TREND_USER/BUYER INITIALS", "") If MyInput = "" Then Exit Sub End If UNAME = MyInput End Sub I would use UNAME as the text string to filter [takenby] rows in power querey...is this on the right path? — randy dickinson
– randy dickinson, Commented Jan 19, 2022 at 16:51
If you record a macro while performing the import you should get the M code incorporated in the recorded VBA — Tim Williams
– Tim Williams, Commented Jan 19, 2022 at 17:01

horseyride · Accepted Answer · 2022-01-24 15:01:24Z

4

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))

edited Jan 24, 2022 at 15:01

answered Jan 19, 2022 at 16:48

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

randy dickinson Over a year ago

I thought it was working, when opening excel I enter the value in the inputbox and I get run-time error 1004: method 'Range' of object'_Global' failed. is this a reference thing that I need to have selected?

horseyride Over a year ago

Once I have the range name defined it works for me. Maybe someone else can chime in

randy dickinson Over a year ago

It took me a second but I figured out that I needed to create the table "aaa" somewhere in the workbook......Works perfect now. LOL. again. Thank you so much!!

horseyride Over a year ago

You needed to create the range name aaa, not a table, I don't think, but as long as it works

randy dickinson Over a year ago

I agree but one of the error messages that came up said, "Table "aaa" not found. So I just made table out of a cell range at random lol. to your point...if it works, Great! Thanks again.

Collectives™ on Stack Overflow

Excel VBA setting variable for power query from textbox

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related