Excel changing external link worksheet name on close

[rspindalis]

Hi All,

I've been have quite the time working out why the link keeps being unable to update. No idea why but have dialled down to this change that may be relevant. I am at a loss and stumbling through while teaching myself these skills

Note: COMPANY and the black box in images are the same string of text hidden for privacy.

The aim is to pull a table in workbook 'COMPANY ANZ Member Data.xlsx' on sheet 'COMPANY Profile' into the target workbook, but only rows when the column 'Active Profile' ='Yes' OR 'XX'.

Link was created through Data > Get External Data > From Other Sources > From Microsoft Query

Link is happy and no errors until I save and close. On reopening the target workbook I get the prompt to update from external sources (Select Update) then the following error occurs:

Clicking on Edit Links...

Then clicking Update Values

Selecting COMPANY Profile from the list stops error messages but the table has not updated any changes from 'COMPANY ANZ Member Data.xlsx' that have occurred while the target workbook was closed.

Excel prompts to save changes on close but on reopening the same issue occurs. Prompting for saving also occurs if Don't Update is selected on opening of the file and immediately closing

Other information that may be helpful:
Target workbook is Macros Enabled which are implemented on Sheet 2, none on Sheet 1 where the self updating table is placed.

Connection String
DSN=Excel Files;DBQ=C:\Users\r.spillane\Documents\Work Files\Member Information\COMPANY ANZ Member Data.xlsx;DefaultDir=C:\Users\r.spillane\Documents\Work Files\Member Information;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Command Text
SELECT `'COMPANY Profile$'`.`Company Name`, `'COMPANY Profile$'`.`Brand Name`, `' COMPANY Profile$'`.`Active Profile`, `' COMPANY Profile$'`.Country, `' COMPANY Profile$'`.`Profile Level`, `' COMPANY Profile$'`.`Education Added`

FROM `' COMPANY Profile$'` `' COMPANY Profile$'`

WHERE (`' COMPANY Profile$'`.`Active Profile`='Yes') OR (`' COMPANY Profile$'`.`Active Profile`='XX')


Any help would be greatly appreciated! If you know a way to achieve the aim with a different method I would be open to that as well.

Solutions that would work after files are uploaded to a Teams Sharepoint File would also be appreciated but I understand that is a whole other can of worms and happy if that is a far bigger task that needs to be new threads.

Thanks in advance for your time