How to show External Data Options form

[Jan Karel Pieterse]

Hi All,

I wish to show the "External Data Range properties" dialog
using code.

When I try this:

Application.CommandBars(1).FindControl(ID:=1951,
recursive:=True).Execute

I get "Method 'Execute' of 'CommandBarButton_' failed".

Why??

I am sure a querytable is selected when I run the code.

This code:

Sendkeys "%dda"

DOES work, but I don't want to use sendkeys, for obvious
reasons.

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[Vasant Nanavati]

Hi Jan Karel:

Not that this is helpful, but:

Application.Dialogs(xlDialogExternalDataProperties).Show

gives a similar error. Not sure why.

Regards,

Vasant.

 

[Jan Karel Pieterse]

Hi Vasant,

I know, forgot to include that.

Thanks.

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[keepitcool]

Jan Karel,

no solution yet..

but I noticed that even if the button LOOKS enabled..
(and DOES work from the UI)

it's .enabled property cannot be set to TRUE
it doesnt throw an error, but the system automatically disables it.
even if the Querytable is nicely selected etc.

I've checked/set
cb.protection=0
commandbars.disablecustomize=false

still cant get it to enabled.

i will continue investigating.. later tonite



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[keepitcool]

Jan Karel,


as an alternative:
an international sendkeys..
tested in xl97 nl, xl2k en ,xl2003 de/fr/en


Sub QueryPropDialog()
Dim ctl As Object, itm As Variant, s As String
s = "%"
For Each itm In Array(30011, 30101, 1951)
Set ctl = CommandBars(1).FindControl(, itm, , , True)
s = s & Mid(ctl.Caption, InStr(ctl.Caption, "&") + 1, 1)
Next
SendKeys s
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Jan Karel Pieterse]

Hi keepitcool,

Sub QueryPropDialog()
Dim ctl As Object, itm As Variant, s As String
s = "%"
For Each itm In Array(30011, 30101, 1951)
Set ctl = CommandBars(1).FindControl(, itm, , , True)
s = s & Mid(ctl.Caption, InStr(ctl.Caption, "&") + 1, 1)
Next
SendKeys s
End Sub

Thanks, I did something quite similar, but a bit more cumbersome <g>:

Sub GetMenuLetters()
Dim sTemp As String
sMenuKeys = "%"
sTemp = Application.CommandBars(1).FindControl(ID:=30011,
recursive:=True).Caption
sMenuKeys = sMenuKeys & Mid(sTemp, InStr(sTemp, "&") + 1, 1)
sTemp = Application.CommandBars(1).FindControl(ID:=30101,
recursive:=True).Caption
sMenuKeys = sMenuKeys & Mid(sTemp, InStr(sTemp, "&") + 1, 1)
sTemp = Application.CommandBars(1).FindControl(ID:=1951,
recursive:=True).Caption
sMenuKeys = sMenuKeys & Mid(sTemp, InStr(sTemp, "&") + 1, 1)
MsgBox sMenuKeys
End Sub

But I dislike the sendkeys bit, because if any of the menues has a
shortcut letter that appears more than once, the code falls over.

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[keepitcool]

Although sendkeys remains tricky...
This one should solve the "double letter" issue too


Sub QueryPropMenuOpen()
Dim ctl, itm
Dim s$, c$, i%, n%

s = "%"
For Each itm In Array(30011, 30101, 1951)
Set ctl = CommandBars(1).FindControl(, itm, , , True)
c = Mid(ctl.Caption, InStr(ctl.Caption, "&") + 1, 1)
n = 0
For i = 1 To ctl.Index
With ctl.Parent.Controls(i)
If InStr(.Caption, "&" & c) > 0 Then n = n + 1
End With
Next
If n > 1 Then s = s & "{" & c & " " & n & "}{enter}" Else s = s & c
Next
SendKeys s
End Sub




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >