Unable to change pivot data source to connection

I have an idea and it might not work but what do we have to lose by experimenting?

Go to Data -> Queries and Connections. The external source might show up as a connection. If so, right click on it and select properties from the pop-up menu.

Click on the definition tab in the dialog box that comes up. You should see a connection string and a command text string. If you do, let know and take a snapshot of the Connections Property dialog box. I'll give you some code to run that will get the true connection string (for some kinds of connections some things are hidden), and then tell you how to change this to your new source.

Or you may get lucky. Copy the contents of the Connection string and paste it into an editor such as notepad. Manually edit the connection replacing the old path name with the new one. Copy and paste the edited results back into the connection string box. Sometimes that works.

How many spreadsheets do you have to convert?