Queries and Connections Pane via VBA?

[Matt Allington]

I would like to be able to control the display and hiding of the Query and Connections pane in Excel 2016 (Power Query). I have tried to find the object using the macro recorder but the open/close event doesn't get recorded. Can anyone tell me if this is controllable, and if so, how?

Thanks

Matt

 

[Jerry Sullivan]

Hi Matt,

You could try this.

Application.CommandBars("Queries and Connections").Visible=True

On my system this adds the pane, but it is very narrow. I have very little experience with PowerQuery, so it could be I just don't have some other settings right to support that pane being properly displayed.

 

[Matt Allington]

sorry for not replying earlier on this. Yes, this is exactly what I am after. And yes the pane is very narrow. I don't know if there is a setting for that or not.

Matt

 

[ricardodiazjimenez]

Hi, I'm using Excel 1807 (compilation 10325.20082) and when I use this code the pane opens, but is blank (used to work before). Does anybody know if there is something to be changed? anybody else with this problem?

 

[Matt Allington]

There was a change to the UI at some stage. The later versions have 2 separate panes with links at the top. One is connections and one is queries from memory.

 

[ricardodiazjimenez]

Thank you Matt, did you manage to find whats the new commandbar name?

 

[Matt Allington]

I don't think it changed. This is what I use

Sub WidenQueriesPane()
Application.CommandBars("Queries and Connections").Visible = True
Application.CommandBars("Queries and Connections").Width = 300
'Change width above to something that suits.
End Sub

 

[ricardodiazjimenez]

This is what I get

 

[celias]

This worked for me:

Sub OpenQueriesPane()
Application.CommandBars("Workbook Queries").Visible = True
Application.CommandBars("Workbook Queries").Width = 400 'Change width as suits.
End Sub

 

[Bagnon]

Excel 365:

Sub OpenQueriesPane()
Application.CommandBars("Queries and Connections").Visible = True
Application.CommandBars("Queries and Connections").Width = 400 'Change width as suits.
End Sub