Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to show External Data Options form
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to show External Data Options form
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to show External Data Options form
Hi Vasant,
I know, forgot to include that. Thanks. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Hi Jan Karel: Not that this is helpful, but: Application.Dialogs(xlDialogExternalDataPropertie s).Show gives a similar error. Not sure why. Regards, Vasant. "Jan Karel Pieterse" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to show External Data Options form
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 Pieterse" wrote: Hi Vasant, I know, forgot to include that. Thanks. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Hi Jan Karel: Not that this is helpful, but: Application.Dialogs(xlDialogExternalDataProperti es).Show gives a similar error. Not sure why. Regards, Vasant. "Jan Karel Pieterse" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to show External Data Options form
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" wrote: Hi Vasant, I know, forgot to include that. Thanks. Regards, Jan Karel Pieterse Excel TA/MVP -----Original Message----- Hi Jan Karel: Not that this is helpful, but: Application.Dialogs(xlDialogExternalDataProperti es).Show gives a similar error. Not sure why. Regards, Vasant. "Jan Karel Pieterse" wrote in message ... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to show External Data Options form
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to show External Data Options form
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 (Jan Karel Pieterse) wrote: Hi keepitcool, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show Data validation List options in a msgbox | Excel Discussion (Misc queries) | |||
Printing Form to Include All Options in Excel | New Users to Excel | |||
chart options show data table | Charts and Charting in Excel | |||
Cannot save external data into file excel system show not respondi | Excel Discussion (Misc queries) | |||
How can I export data from an Excel form into an external Excel d. | Excel Discussion (Misc queries) |