Excel 365: Query connection not present in the "Queries & Connections" tap

Question

I am having a simple but very enoying issue with a spreadsheet and I have spent way to much time getting it to work. Hope you guys can help. I'm running excel 365.

The spreadsheet gets data from four other spreadsheets using the Power Query Editor. These connections broke somehow and when they are updated an error occurs. Here is an example for the query "QuerySdr":

This happens for all the connections.

Since multiple people use the file, its difficult to pin-point the time the error occured.

The connections can be seen by choosing "Get & Transform Data" -> "Existing Connections":

However, they dont appear. on the "Queries & Connections"-tab:

Can anybody tell me how to get rid of these Queries?

Answer 1

Hi George, thank you for taking the time to look at my issue.

1. I couldn't access the connections properties menu when asking this question. The data could therefore not be re-imported. After a restart (i guess) the option to enter the proporties menu worked. Making it possible to re-import the data in a pivot table; not in a table. The connection, however, does not appear in Queries & Connections.
2. Yes I can create a new connection. I could also do this before the restart.
3. Yes, this is what I ment. :-) Thank you for the script.
4. 

Note: I found a work-around:

• In a different spreadsheet - create a table with the same name as the connection
• Create a connection to that table in the worksheet having the broken connection
• Two connections now exist in the "Existing Connections". They have same name, but one of them ends with a (1).
• The new connection is now present in the Queries & Connections.
• Delete the connection.
• Excel deletes both connections.

Case solved. :-)

Answer 2

Hi ProcesSupport,

Thanks for posting in the community.

According to your description, there are no queries in a specific workbook when you click “Refresh All”, these error messages pop on. To troubleshoot the issue, I would like to collect some information.

1. What if you double-click the existing query in the Existing Connections window and import data again, does the query exist in Queries & Connections and work well?
2. What if you create a new query from the workbook, does it work well?
3. For “get rid of these Queries”, do you mean you want to remove the existing connections? If so, you can create a copy of the workbook and try the VBA code of this thread in the copied workbook.
4. Please provide your Office version

Click File> Account, take a screenshot including Subscription Product and About Excel.

Regards,

George

Answer 3

Hi ProcesSupport,

Thanks for posting back. I'm glad to know the issue is now resolved. To benefit other members who may have the same issue, I will mark your reply as answer. Also, if you encounter any issues while using our products, feel free to post in our community and we are glad to help.

Regards,

George