asked on

Excel - "A Query with this name already exists on this sheet" - Error Message

Dear Experts I am having a tough time with this error message.

Google searches is yielding only people having the same problem and no visible solutions.Tp illustrate this problem I am using the following example, using a fresh blank workbook.

I ma using the "Optional Attributes" Table from this url:http://www.w3schools.com/tags/tag_table.asp

So.

1. Data Ribbon, Choose "From Web". Using URL above. Then Chose the table. In the Web Query Options i choose Full HTML formatting. Import into Sheet1 cell A1.
Then select Connections button.
Screenshot1.jpg

2. In the Workbook Connections. Click on Properties and then Rename "Connection" to W3C.
Then at the bottom of the Workbook Connections dialog box, click. Then it shows where the connection is used. Screenshot2

3. In I go in the name manager there is name called "tag..." and I f I rename it to "W3C" the connection's link to the sheet in broken. If you go to the workbook connection dialog box, at the botton it says:
"Connection(s) not used in this workbook."
Question: How do you rename the name range produced by the connection without breaking the conection?

4. Now after this "mistake" lets go redefine the connection.
We go to workbook connection and delete the existing connection and repeat step 1.

5. Just before i import it into cell A1. I made the following changes:
Rename it to W3C.
Chose Overwrite instead of insert , 9last option)

See screenshot 3 for the error message.

Any help or advice on how to avoid this query error without having to rename everything as the named range (automatically refreshed by Excel) is used in a few sheets.

thanks a lot.

A.

Screenshot2.jpg
Screenshot3.jpg

ASKER CERTIFIED SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anil

ASKER

Hi Akoster, thanks very much your clear instructions work graciously.

I do have a related question though. Your instructions do show how to "prevent" the mistake but how would I be able to redefine a connection called W3C in the same workbook if the mistake has already been amde. Please use the attached excel sheet.

I did all the steps you mentioned Then I deleted the named range first and then went to delete the connection. The data is still in sheet 1 but I still seem unable to redefine a connection bearing the name W3C in that attached workbook.

A>
Excel---Define-a-connection-call.xlsx

SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anil

ASKER

Thanks a lot akoster!
This confirms what I was thinking.
This is annoying as I cannot just copy all my formulas and data across a new sheet.
However I think this is a workaround. (Please test this independently). If you "save as" then it resets the list of deleted / invalid query names.

Another tough question is as follows: Lets say I give you a fairly blank spreadsheet with a blank connections list. How do you know which connection names have already been used and deleted (if any) so that I do not re use the names.

Thanks for your help, I thought I was going crazy. Thanks also for concocting the macro. I understand this this may take time.
I am closing the questions but for the benefit of others and my sanity please post the macro, when you are ready.

A>

Anil

ASKER

Excellent answer. Thanks again.

Arno Koster

can you try running this macro ?
run it and compare the debug output to the connection to see if this solves the problem.
you can enable the debug output by pressing [Ctrl-G] when you are in the VBA editor.

Sub update_connections()

Set web_connection = ActiveWorkbook.Connections(1)
Set web_range = web_connection.Ranges(1)
Debug.Print "query [" & web_connection.Name & "] was linked to connection '" & web_range.Name.Name & "' leading to cells (" & web_range.Address & ")"

Debug.Print "renaming connection"
Set web_name = ActiveWorkbook.Names(web_range.Name.Name)
web_name.Name = "WC3"
Debug.Print "query [" & web_connection.Name & "] was linked to connection '" & web_range.Name.Name & "' leading to cells (" & web_range.Address & ")"

Debug.Print "renaming query"
web_connection.Name = "WC3"
Debug.Print "query [" & web_connection.Name & "] was linked to connection '" & web_range.Name.Name & "' leading to cells (" & web_range.Address & ")"

End Sub

Open in new window

Anil

ASKER

Amazing!
This is the output.

query [Verbinding] was linked to connection 'Sheet1!WC3_' leading to cells ($A$1:$D$27)
renaming connection
query [Verbinding] was linked to connection 'Sheet1!WC3_' leading to cells ($A$1:$D$27)
renaming query
query [WC3] was linked to connection 'Sheet1!WC3_' leading to cells ($A$1:$D$27)

Anil

ASKER

The named ranged cannot be renamed , it seems using the name manager.

Arno Koster

found it !

try this :

Sub find_used_connections()

    For Each Item In ActiveSheet.QueryTables
            MsgBox "Found reference named '" & Item.Name & "' connecting to " & Item.Connection
            '-- activate next line to remove the connection
            'item.delete
    Next Item

End Sub

Open in new window

Anil

ASKER

Yes, confirmed. I ran the short 9 line code in the attachment I provided and using the steps you modified described, I was able to redefine a connection (and an associated named range) called W3C in the same spreadsheet.

That solves it and every combination of problems in that scenario. No more named ranges and connection that can't be renamed.

Thanks a lot!

A.

Arno Koster

you're welcome.

Excel - &quot;A Query with this name already exists on this sheet&quot; - Error Message

Excel - "A Query with this name already exists on this sheet" - Error Message