...is very tough! It's as though as soon as you go near them everything goes wrong. It's not helped by the fact that you can define the query in an external IQY file, but if that file doesn't exist (the spreadsheet was built by user A, but is passed to user B), Excel falls back to using the definition embedded within the spreadsheet itself. So there doesn't have to be a connection between the values shown on the screen and what is in the file (and that sneaky option which says "keep instep with file" always seems to be grey-ed out!)
What's worse is that when you look at a query, or change the query definition, Excel silently resets the parameters to some defaults. Losing whatever you had. This can screw up formatting and make the standard "prompt for values" pop-up at execution time. No good if your spreadsheet is being run by the (useless) Task Scheduler at 3 in the morning.
Basically, leave well alone.
Which is a problem when you're trying to change a spreadsheet which uses them. One which refreshes data on the Load event and just doesn't work if that doesn't happen.
My latest cunning solution (attempt) is to edit the file outside of Excel. As you know, the new 'x' formats (.xlsx/m and .docx/m) are really zip archives with the data described in XML format within that archive. The data connections are in a connections.xml file in the 'xl' folder. Looking at that it's fairly easy to make the necessary changes, without Excel getting a chance to 'help' you be auto-updating lots of other things.
I'm hopeful that there's no checksum in the file contents that verify whether the archive has been tampered with. I suspect not; these are standard zip archives I believe.
