I have a workbook with several connections to SQL Server. When I need to change the SQL statement, I do that in Connection Properties.
I added a command to the QAT to show the connection properties dialog, but there’s something I don’t like about it. If I’m in a table with a connection, it’s pretty likely that I want to see the properties of that particular connection and not just a list of all connections. Of course I’m awesome at naming my connections so I don’t have to guess which is which, but if you weren’t so awesome you might have trouble distinguishing them.
The long-term answer is to write my own interface to change the things I want to change. But in the mean time, I want to open the connections dialog and highlight the connection related to the table I’m in, if any.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Public Sub ShowConnection() Dim qt As QueryTable Dim sConName As String Dim i As Long On Error Resume Next Set qt = ActiveCell.ListObject.QueryTable On Error GoTo 0 If Not qt Is Nothing Then sConName = qt.WorkbookConnection.Name Application.CommandBars.ExecuteMso "Connections" Application.Wait Now + TimeSerial(0, 0, 2) For i = 1 To Len(sConName) SendKeys Mid$(sConName, i, 1) Next i Else Application.CommandBars.ExecuteMso "Connections" End If End Sub |
When I open the Connections dialog, I can start typing the name of the connection to get down to it. For example, I could start typing “dup” and it will highlight the first connection that starts with those keys.
With SendKeys, I can type the entire name. First I see if the ActiveCell is in a QueryTable. If it’s not, I just open the dialog. If it is, I open the dialog, wait a couple seconds, then send all the keys in the connection’s name. SendKeys can be very dangerous, but we’re just experimenting here.
What the above code actually does is open the Connections dialog, wait for it to close, then send all those keystrokes into the ActiveCell. Dangerous. And not helpful. Apparently the Connections dialog is modal and all code is suspended until it’s closed. I did a little searching and found this command, which does not help.
|
1 |
Application.CommandBars.ReleaseFocus |
Maybe the old CommandBars behave differently than the Ribbon.
|
1 |
Application.CommandBars.FindControl(, 11205).Execute |
Nope. Same as ExecuteMso. One last try. This opens the dialog with SendKeys.
|
1 2 3 4 5 6 7 8 |
sConName = qt.WorkbookConnection.Name SendKeys "%ao" Application.Wait Now + TimeSerial(0, 0, 2) For i = 1 To Len(sConName) SendKeys Mid$(sConName, i, 1) Next i |
And it works. For some reason sending Alt+A+O opens the Connections dialog modeless, the SendKeys executes, and takes me to the “active” connection. I have a couple of applications on my machine that like to steal the focus, so I try to avoid SendKeys whenever I can (which is always). In this code, I’m using it twice, so I won’t be using it all. Interesting, though, that it seems to be the only way to get what I want.
Along the way, I discovered I could get to the “active” connection’s property sheet with this key sequence:
- right-click key
- b
- a
- tab
- tab
- enter
I guess that will work. It’s a lot of keystrokes, though.
What about alt-DDE ?
@Jan Karel, one issue with that is that it sometimes opens the MS Query interface (I think if the query has parameters).
Dick, this post might be of interest. I have a tool that lets me edit the SQL and run it: http://yoursumbuddy.com/tables-edit-query-dialog/
I’ve since updated the tool a bit, allowing you to (try to) enter parameters. If you’re interested I can sent it to you.