Query Wizard Cannot Be Used To Edit This Query

C

Carl

I am trying to edit a query in an Excel worksheet to change the name of the
source file but keep getting the message, "Query wizard cannot be used to
edit this query". The worksheet is not protected. Does anyone have a
suggestion? Thanks in advance.
 
D

Debra Dalgleish

If you click OK, Microsoft Query should open, and you can edit the
source file name in the SQL string.
 
C

Carl

Thanks, Debra, I really appreciate your help but nothing happens when I click
OK. I tried to get Microsoft Query to open by selecting “Data†then “Get
External Dataâ€, then “Edit Query†which gave me the message, "Query wizard
cannot be used to edit this query". I also tried selecting "Data", then "!
Refresh Data", which also gave me the same message. Do you have any other
suggestions? Thanks in advance.
 
D

Debra Dalgleish

Do you know what the current source file is?
Thanks, Debra, I really appreciate your help but nothing happens when I click
OK. I tried to get Microsoft Query to open by selecting “Data†then “Get
External Dataâ€, then “Edit Query†which gave me the message, "Query wizard
cannot be used to edit this query". I also tried selecting "Data", then "!
Refresh Data", which also gave me the same message. Do you have any other
suggestions? Thanks in advance.

:
Click to expand...
 
C

Carl

No, unfortunately. That is why I am trying to edit it...to find the old name
and change it to the new name. Someone else set up the original query but
then the source file changed after they left the company. Thanks again!
 
D

Debra Dalgleish

You could try programming to write the connection string to a worksheet.
Then, edit the string, and reset the connection.

For example, with the query on sheet1:

Sub GetQueryInfo()
Worksheets("Sheet2").Range("B2").Value _
= Worksheets("Sheet1").QueryTables(1).Connection
End Sub

Sub SetQueryInfo()
Worksheets("Sheet1").QueryTables(1).Connection _
= Worksheets("Sheet2").Range("B2").Value
End Sub
 
Top