Queries and Connections opens in single column that cannot be widened

[jdsmith_xyz]

In an older thread, this code was recommended to open "Queries and Connections":

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

This works sometimes for me, but not right after opening the file. On the first run after opening, a Queries and Connections window becomes visible that is locked to single column width and has no visible content. I need to let my macro run to completion (can be lengthy) or ESC out of it. The column gets wider when the macro stops and existing queries become visible. When I restart, the column usually snaps to my specified width.

I can repro this by creating a new macro sheet and adding the above single macro routine and calling it directly. I can also modify the code to toggle the window on and off, but the locked column aspect persists. Does anyone have ideas on what might be causing this?

 

[XL Pro]

I'm running into this same issue. The best I've come up with on a why (but no solution) is that when the command bar opens, it conforms to it's controls.
For some reason the Query bar does not get populated properly and so when it's opened it shrinks since there's nothing in it.
Opening the query manually, gives Excel time to parse the connections and fill in the commandbar. I believe this is the key piece we're missing on how to do.

 

[HenkDePoema]

I'm running into this same issue. The best I've come up with on a why (but no solution) is that when the command bar opens, it conforms to it's controls.
For some reason the Query bar does not get populated properly and so when it's opened it shrinks since there's nothing in it.
Opening the query manually, gives Excel time to parse the connections and fill in the commandbar. I believe this is the key piece we're missing on how to do.

Your comment actually got me on the right track for a workaround. It is not very nice, but it solved the problem of a not populated, or even invisible Queries and Connections-pane on the first run of my macro.
I eventually(...) found out that by forcing Excel to load 'some' info about the existing queries, before running the macro, some sort of extra .NET module got loaded (according to the status bar):

Dim btPaneHack As Byte
btPaneHack = ActiveWorkbook.Queries.Count

After that,

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

actually showed my queries on the first run.
But this wasn't fast enough. When running

ActiveWorkbook.RefreshAll

after the above code, the pane still didn't show up most of the time.

So, my final workaround: run the btPaneHack on opening of the workbook. In ThisWorkbook I have

Private Sub Workbook_Open()
Dim btPaneHack As Byte
btPaneHack = ActiveWorkbook.Queries.Count
End Sub

to force Excel to load the queries on startup. Now when I run my actual macro, the pane is visible, showing the progress of my refreshing queries.

Some final notes:
- there a probably better ways to force Excel into loading the queries
- this workraround is still no solution to the .width value not changing the actual width of the pane (as discussed on many fora), but that might have something to do with having Queries instead of active Connections in your workbook...
- it is bad practice to have a macro running on opening, I know. But for my case I didn't see another solution so far. Excel needs a certain amount of time to completely load the .NET module, which it doesn't have in my macro. Of course this can be done differently in other situations (e.g. with more user interaction prior to loading this pane, where you can put the btPaneHack).

 

[HenkDePoema]

I forgot to mention that, with the above workaround, the pane is opened at full width (the same as when manually opened via the ribbon).

So I guess @XL Pro is right about the width being ajusted to its content at the time the pane is loaded.