Display the Edit Query Dialog

I have a query on a sheet (pulling Data from another Excel File).

I want to display the Edit Query Dialog whenever  the user Activates the sheet.

1) I dont want to use the Sendkeys method

2) I have tried the below code in the Worksheet_Activate event and It errors out for some reason....

Private Sub Worksheet_Activate()

Range("A4").Select ' first cell in the query
Application.CommandBars("External Data").Controls("&EditQuery...").Execute 

End Sub

TIA

Sam

Excel 2003

There is a space in Edit Query.

Bob

Did you get it working with that? 

I am using the control ID for that button and still get an 'Unspecified Automation error' in XL2000?

Private Sub Worksheet_Activate()
Dim cb As CommandBar
Dim ctrl As CommandBarControl

Set cb = Application.CommandBars("External Data")
Me.Range("A1").Select
With cb
    .Visible = True
    .Enabled = True
    Set ctrl = .FindControl(, 1950)
    With ctrl
        .Enabled = True
        .Execute
    End With
End With
End Sub

No Nick, I didn't, I just checked the captions. I never use that way of querying, so I didn't know if it was anything to do with me not knowing what I was doing. It's much more your field than mine.

Bob

Phew, thought I was going mad...anyone else help out?

 Really annoying. I couldn't get it to work without Sendkeys either.

Bob,

My Code does have the space...It just got lost when I copied pasted

Nick,

 I get the same error...

JKP,

Application.Sendkeys "%DDE" works in Win XP - Excel 2003.

But does not work with Win Vista Excel 2003 ....

Also For XL 2000 I would have to use another Key combination as Import External Data is Get External Data (i think)

Sam

 I know, it works for my 2003/Win XP setup. No Vista here though.

 Hi Guys,

Sorry to say but this issue only confirms my own experiences. Running 2007 with Vista does work well while 2002 and 2003 don't always work properly. I haven't tested 2000 with Vista (and have no interest in it) but I believe it will behave in a similar way as 2002/2003 do.

I find it to be a shame that MSFT no longer make sure that older versions of different tools then the latest works well... 

Hi Sam,

You could store the query (.dqy file) and use the following:

Sub StartQry()
    Call Shell("Drive:\PathTo\msqry32.exe Drive:\PathToQry\YourQuery.dqy", 1)
End Sub

Haven't tested it under Windows Vista though.

Rembo

Hi Rembo,

Thanks for your post. Let me give you a brief background about the probelm

The file that I am sending to my client already has a sheet which is getting data from an external file.

Now my client thinks its too much of an effort to right click on a cell containing the data and then click on Edit Query.

The client wants the query dialog to pop up whenever the sheet is selected.

The problem with your solution is

a) I have to ship an extra file- the query file.

b) Once the user makes changes to the query for example - Chooses a criteria  the data is not returned back to the sheet. The users has to open the query file and then return data to the file

c) The Dialog that pops up is not the "Query Wizard - Choose Colums" but the Msquery Editor which is a bit more complex to use.

Far too much effort for a person who does not want to right click and say edit query...

Sam

Hi Sam,

Yes, that sounds like a lot of effort. Lifting a finger and all...

You could code something that retrieves data from the other workbook without using the query editor. Basically an Open-Copy-Paste-Close action would do the trick. A userform could be used to create some flexibility for parameter use.

If you want to do it real fancy an ADO/forms solution could solve your problem but that involves some coding.

Remco

 Hi Rembo,

I thought on the same lines ....to design a user form that lets the user edit the query...

But I got lazy and thought why not just display the built in dialogs of Edit query.....and I discovered this problem of displaying the Edit Query dialog

Sam