Power Query for Excel has developed at a striking pace over the last few years making it hard to keep up with the changes. Over several iterations, the Queries and Connections pane has been called various names, and the behaviour has changed a few times too. Depending on the version of Excel and Power Query, you may get different behaviours. One thing that has annoyed me is that in some versions, when the Queries and Connections pane opens, it is too narrow to fully display all the query information. See below.
I tried to find out how to control this using VBA. I turned on the VBA recorder and toggled the window – alas, nothing was recorded. At some stage I asked a question on a forum (probably MrExcel.com) and someone helped me with some VBA code to control this panel (did I ever tell you how much I love forums?). The trick is to refer to the name of the panel so it can be controlled.
Toggle the Queries and Connections Pane Open/Closed
The VBA to toggle the pane open/closed is quite simple (when you know how).
Sub Toggle()
Application.CommandBars("Queries and Connections").Visible _
= Not (Application.CommandBars("Queries and Connections").Visible)
End Sub
From there you can change the width with the following code.
Control Width of Queries and Connections Pane
Sub ToggleWidenAndRefresh()
Application.CommandBars("Queries and Connections").Visible = _
Not (Application.CommandBars("Queries and Connections").Visible)
Application.CommandBars("Queries and Connections").Width = 300
'Change width above to something that suits.
ActiveWorkbook.RefreshAll 'optional refresh after opening
End Sub
I have saved this VBA to my personal VBA workbook and added it to my Quick Access Toolbar so that is always just a click away. If you would like instructions about how to do this, read my article about setting up a personal VBA workbook.
Thanks Matt,
This is exactly what I wanted/needed (your “Toggle” script). When developing a workbook for other users, I normally have a “Control Panel” sheet that contains all the macro buttons. Of course the Refresh Query references another sheet. However I like the idea of “seeing” each query update. But I normally forget to turn the pane on. Your Toggle is perfect. I called it before the Refresh, and then again after. It does not need to be visable after the refresh is complete. The exact solution I wanted. Thanks so much!!!
Wow, this is an oldie. I’m glad some of my articles still have value to someone 🙂
Hi All.. I was having the same problem and found your VBA code through a Google search on the topic. I was about to make a QAT button or put it in a Workbook_Open() event. But, I decided to do some tinkering and experimenting first. I’m on Office 365 (not Insider) for this computer.. have not tried it on my Insider laptop. Here is what I discovered: The position and state of the Q&C panel when you next open EXCEL is influence by the position and state of the Q&C panel when you last closed EXCEL. If you have the panel docked to the right and with a width of your choosing and then you close EXCEL with the panel in a visible state, the next time you open EXCEL, the panel will default to the smallest width (i.e. forgetting your previous settings). This is also true if you un-dock the panel and drag it onto the worksheet. If you close EXCEL with the un-docked panel in a visible state, the next time you open EXCEL, the panel will default to the smallest size, both vertically and horizontally, although it will still be un-docked. So, the trick is to set the size and orientation of the panel to your liking, then close it (i.e. make it not visible), then close EXCEL. If you do that procedure, then the next time you open EXCEL, the panel will be in the same position and orientation as you defined before closing the panel in the previous session.. before then closing EXCEL. I know it sounds stupid.. but there must be something to the coding of the EXCEL window that says “return to default” if the panel is visible upon closing EXCEL.. and “retain current size and orientation” if the panel is not visible upon closing EXCEL. Your mileage may vary, but it works every time on my system. So, I think it is a solution that does not require VBA, but does require remembering to close the panel before closing EXCEL. Note that it does not seem to have anything to do with saving or not saving the workbook.. just the visible or not-visible state of the panel when closing EXCEL. I’ve had this same issue with the Pivot Table Field List panel as well.. forgetting my preferred width and position between sessions. I suspect maybe this will solve that problem, as well; I will experiment with that next. In the mean time.. sharing what I discovered for the Q&C panel for anyone who does not want to solve it with VBA. I hope it works for you. Good luck!
Thank you, Matt.
This is what worked for me to open the queries pane and adjust the width. I am leaving the note in case it helps someone:
Sub OpenQueriesPane()
Application.CommandBars(“Workbook Queries”).Visible = True
Application.CommandBars(“Workbook Queries”).Width = 400 ‘Change width as suits.
End Sub
I must be doing something wrong as I have never added VB to a Excel sheet before.
1. I opened my .xlsm file.
2. Hit Alt-F11
3. Right clicked “ThisWorkBook” and Inserted Model.
4. Entered this code
Sub OpenQueriesPane()
Application.CommandBars(“Workbook Queries”).Visible = True
Application.CommandBars(“Workbook Queries”).Width = 400
End Sub
5. Hit F5 and selected my Marco but nothing happened.
I just want the pane to be visible to viewers of the xlsm file when they open it.
Microsoft is forever changing the product. I noticed the workbook queries window has been renamed in my version. This version works for me
Sub OpenQueriesPane()
Application.CommandBars(“Queries and Connections”).Visible = True
Application.CommandBars(“Queries and Connections”).Width = 400
End Sub
You have used “Workbook Queries”. I am not sure if that is the name of your queries pane or not, as I don’t have the older version of Excel.
Hey Matt-
I stumbled across your solution, but the window appears not to open if you only have connections and no queries. I have 2 connections to a Power BI dataset and if I select Queries and Connections from the Data toolbar the window opens.
Any ideas?
Thanks, Doug
To toggle the Query panel and Open / Close connections, you can add in Quick Access Toolbar in more commands.
Yes you can, but you can’t set the width without vba like I provided.
Awesome – thanks
Plus Jan Karel Pieterse just put a User Voice post here today
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/33451300-improve-queries-and-connections-task-pane
THANK YOU!!!! This has been driving me *INSANE*