How to show External Data Options form

  • Thread starter Thread starter Jan Karel Pieterse
  • Start date Start date
J

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
 
Hi Jan Karel:

Not that this is helpful, but:

Application.Dialogs(xlDialogExternalDataProperties).Show

gives a similar error. Not sure why.

Regards,

Vasant.
 
Hi Vasant,

I know, forgot to include that.

Thanks.

Regards,

Jan Karel Pieterse
Excel TA/MVP
 
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 >
 
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 >
 
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
Click to expand...

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
 
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 >
 
Back
Top