Using Macro to Reset MS Query Connection String

Hello everyone. I've newly joined this forum. However I've browsed through the forum many times trying to trouble shoot various issues.

I’m having a specific issue now regarding creating a pivot table to consolidate data from various named ranges throughout the workbook (same workbook). Overall this process is well documented and I’ve been able to create the pivot table. It pulls data from the various ranges using MS Query. I can get this to work, however the file that I’m putting this into is a template file used to create cost estimates for large industrial construction projects. Every time the template is copied and renamed, the connection string for the query no longer works and needs to be manually re-established. I’d like to set up a push button macro to reset this connection to whatever the current workbook file name and file path is. I know this is possible, but I don’t have nearly enough skill in VBA coding to figure it out.

For security reasons I’m not able to post the workbook, but I can list some information that should indicate what needs to be done.

The name of the query is “SumDataQuery”
The command text I need to execute is:

Select * from A01Table
union all
Select * from A02Table
union all
Select * from A03Table
union all
Select * from A04Table
union all
Select * from A04DTable
union all
Select * from A05Table
union all
Select * from A06Table
union all
Select * from A07Table
union all
Select * from A08Table
union all
Select * from A09Table
union all
Select * from E02Table

I would like to set up a button on the same worksheet as the pivot to reset the connection. I’ve named the button macro “ResetConnection”

Could someone please help me figure out the correcting coding. As stated earlier my VBA skill is poor at best. Getting this pivot to work properly has the potential to drastically improve the functionality of this spreadsheet.

I would greatly appreciate any help someone could provide.

Thanks, Kyle