Edit Excel Connections in XML

I have come across a couple scenarios where it has been really helpful to be able to edit the data connections and queries in an Excel file directly.  In one case a connection was read-only and in another case there were multiple connections that needed to be updated to a new address and making the change through the Excel user interface would have been very time consuming. This post walks provides the procedure for directly editing these connections and queries, which are stored in an xml formatted file (.xml).

A Microsoft Excel xlsx file behind the scenes is really just a zipped file containing a number of individual xml files. The procedure below allows you to edit just the connections xml file using a text editor such as notepad or VS Code.

So to start with, you should have an Excel xlsx file saved to your pc or a file share you can work from. It’s a good idea to create a copy of the original file as a backup before proceeding.

First, open windows explorer to the directory that contains the Excel file you would like to edit.

You should have your windows explorer session configured to show file extensions (so you can see the .xlsx) If needed you can turn this on in windows explorer by going to the view tab, and clicking the check box to show extensions.

In windows explorer right click the file you would like to update click rename. Change the file’s extension from .xlsx to .zip.

Hit the enter key to proceed. You will receive a warning prompt asking you to confirm the change. Click the ‘Yes’ button.

Now the file should have a .zip extension and we can start to work with it.

Now here is the main trick to getting this to work: DO NOT extract the zip file. We need to work with the file in-place without extracting and re-zipping it.

Double click the zip file to navigate into it using windows explorer and then double click the ‘xl’ subfolder. You should now see a connections.xml file.

Right click the connections.xml file and select copy or left click once and use ctrl+c to copy the file.

Navigate back to the directory that contains the .zip file and paste the connections.xml file so we can work with it.

Next we can edit the connections.xml file by doing a right click and then open with notepad. (or open in another text editor of your preference such as VS Code).

You can now use notepad to make any updates to the connection info you would like. (and use shortcuts like search and replace to make multiple updates very quickly).

When done save the file. Next, we can replace the original connections.xml file with the new one. Back in windows explorer do a right click and copy the connections.xml file

Next navigate back into the zip file and move down to the ‘xl’ subfolder. Do a ‘paste’ to replace the existing connections.xml file.

Finally we’ll wrap up the process by renaming the .zip file back to the original .xlsx extension.

Hit the enter key to proceed. You will receive a warning prompt asking you to confirm the change. Click the ‘Yes’ button.

That wraps up the procedure. This can be handy for power users who need to make a bunch of connection or query edits. Again the two main tricks to getting this procedure to work are 1) work with the .zip file in place in windows explorer (don’t try extracting and compressing or it will probably fail for you) and 2) copy and edit the connections.xml file in another directory and then paste it back into the zip file to replace the original.