Excel query table in another workbook vba

#Excel query table in another workbook vba update#
#Excel query table in another workbook vba code#

Choose РІР‚СљUpdatePowerQueriesРІР‚Сњ and click OK.

When you let go, youРІР‚в„ўll be prompted to assign a macro.

Left click and drag a button onto your worksheet.

Click Insert and select the button in the top left.

Go to the Developer Tab (if you donРІР‚в„ўt see it, right click the ribbon, choose РІР‚СљCustomize RibbonРІР‚Сњ and check the box next to the Developer tab to expose it).

The goal here was to make it easier for my users. LetРІР‚в„ўs face it, thatРІР‚в„ўs probably harder than going to Data РІР‚вЂњ> Refresh All. If all goes well, you should now have another row of data in your table, as I do:

Go back to Sheet2 to verify itРІР‚в„ўs updated.IРІР‚в„ўm going to do that first, then IРІР‚в„ўm going to: The easiest way to do this is to go back to the table on Sheet 1 and add a new row to the table. Speaking of working, letРІР‚в„ўs prove itРІР‚В¦ But first, close the Visual Basic Editor. IРІР‚в„ўd prefer to check the type of query, but this will work. By default, all Power Query scripts create a new connection with the name РІР‚СљPower Query РІР‚вЂњРІР‚Сљ the name of your query. Now, IРІР‚в„ўll admit that I find this a little looser than I generally like.

#Excel query table in another workbook vba code#

NOTE: The code above was updated to reflect the method posted here: LTest = InStr(1, cn.OLEDBConnection.Connection, 'Provider=.1', vbTextCompare)

#Excel query table in another workbook vba update#

' Macro to update my Power Query script(s)Р”Вim lTest As Long, cn As WorkbookConnection

In the window that pops up, paste in the following code:.Right click that name and choose РІР‚СљInsert ModuleРІР‚Сњ.Find your project in the list (It should be called РІР‚Сљ'VBA Project (Selecting Data.xlsx)РІР‚Сњ.If you donРІР‚в„ўt see a folder tree at the left, then press CTRL+R to make it show up. This will open the Visual Basic editor for you. But to use it, you need to know the secret handshake: Rather than record and tweak a macro, IРІР‚в„ўm just going to give you the code that will update all Query Tables in the entire workbook in one shot. Next, we need to build our VBA for refreshing the table. LetРІР‚в„ўs sort Animal ascending (just so we know something happened)Р”Р„t this point, we should get a new РІР‚СљSheet2РІР‚Сњ worksheet, with our table on it:.LetРІР‚в„ўs just grab the sample data file from my post on pulling Excel named ranges into Power Query. The interesting part about the above statement is that Power Query doesnРІР‚в„ўt have any VBA object model, so what kind of black magic trick do we need to leverage to pull that off? As it turns out, itРІР‚в„ўs very simpleРІР‚В¦ almost too simple in fact. The last thing I want to do is to send them right clicking and refreshing everywhere, so I program a button to refresh Power Query with VBA. Activate the destination worksheet and select the position you want the upper left cell for your tableР”Вefault Your Pivot Tables To SUM not COUNTР”Вelete obsolete items from your Pivot Tables blog post or watch the YOUTUBE video.When IРІР‚в„ўve finished building a solution in Excel, I like to give it a little polish, and really make it easy for my users to update it.Select any cells in the table and press CTRL+A twice to select the entire table.If you want to move the table a different sheet within the same workbook or a completely different workbook then To move an excel table to a new location within the same worksheet just move the mouse pointer to any of the tables borders, when the mouse pointer turns into a cross with four arrows just click and drag the table to its new location. I find I need to do this sometimes if I am building a spreadsheet solution which more than most times needs to be tweaked, until I am finally happy with it. It is really easy to move a whole Excel data table to a new location on the same worksheet or even a new location on a different worksheet in the same workbook or an entirely different workbook.