Excel 2016 query editor parameters code#
Once that is done, the next step of the code creates a new Sheet and names it after the currently selected Company.
Excel 2016 query editor parameters update#
So, For each and every row in the first table on Sheet1, it remembers the Company name from that row and feeds it into the Update Query step which replaces the “Company 1” part of the query to whichever company was read from the current row. The code revolves around the For statement. "OLEDB Provider=.1 Data Source=$Workbook$ Location=" & p & " Extended Properties=""""" _ '-Change The Load Properties for New Query, so it loads on the new Sheet Set ws = Sheets.Add(Before:=Worksheets(1)) '-Create A New Sheet To load Your Query Into Set tbl = Sheets("Sheet1").ListObjects(1) And now the VBA code: Sub PQDynamicToSheets()Ī = InputBox("Name Of The Basic Query?", "Query Name") We will also use the Company name for the name of the New Query and we will use it for the name of the newly created Worksheet where we will load the result of the Query. We will step through this table with our VBA code and create a copy of our BasicQuery1 using these Companies as parameters for the final step (filtering of the Company column). We kick it off by creating an Excel Table with a list off all unique Companies from our original table. Now we have our basic Query that we will modify with VBA. Now we’re finished with the Query and select Home/Close & Load dropdown and select Close and Load To…Īnd we select Only Create Connection and OK At this point if we choose the Advanced Editor command in Power Query Editor either on the Home tab or the View tab, we would see this This step is the part of the Power Query M code that we will be changing with VBA.
In this step we will filter the Company column to keep only records of Company 1. I called it BasicQuery1Īt this point we add an additional step to the query.
This is a very important step, as we need to feed the name into a VBA Input Box (you could also hard-code it into VBA but I wanted to make it dynamic). The first thing we want to do is to change the name of the Query in the Properties section of the Query Settings Sidebar. We select a cell within the Table and go to Data/FromTable/Range Now we create a Query that will read from our Table. We will start with a simple Excel Table on Sheet2. And if you want to follow along, here is a blank file to follow along. This code was written and tested in Excel 2016. The core of this blog post is a VBA code that will create a copy of a Power Query M code, modify it, create a new sheet and load the result of the modified Query to the new Sheet.
0 kommentar(er)
