Open the Queries and Connections Pane Wider - Excelerator BI

Open the Queries and Connections Pane Wider

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.

Video_2018-02-25_094639

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.

5 thoughts on “Open the Queries and Connections Pane Wider”

  1. 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

  2. LINELSON Y CASTRO

    To toggle the Query panel and Open / Close connections, you can add in Quick Access Toolbar in more commands.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top