Excel changing external link worksheet name on close

rspindalis

New Member
Joined
Jul 19, 2022
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
  2. Web
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:
1658287442430.png


Clicking on Edit Links...
1658287485375.png


Then clicking Update Values
1658287542855.png


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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top