Excel query update

I have an Excel spreadsheet that contains several hundred rows and about 50 columns.
Some of these columns are yes/no type.
I have created a query to extract a subset of data based on the value of one of the yes/no columns.
So far so good - everything works and I see the desired subset in another worksheet.
If I move the workbook to a different folder the query does not work as the data paths in the Connection still point to the old location.
I can manually edit the Connection to point to the correct location but I will have 20 or more subsets to extract and the workbook location will change again.
I want to use vba to change the Connection to point to the current file location and refresh the subset on the click of a button.
I have tried using relative file locations but that doesn't work.
I worked out the vba to make the changes but the changes are not being saved.
Have I missed something or am I doing something wrong or stupid?
This is the vba, the two msgbox lines return the same string:

Please Login or Register  to view this content.