Cannot refer to a dynamic range in a closed workbook

[BNCOXUK]

Hi Sir,

After some search, I found that it is true that Excel will not accept a reference to a dynamic named range in a closed workbook. Otherwise, this will lead to such warning message as below:

Microsoft Excel cannot find 'MIPS' on "Data.xlsx". There are two possible reasons:

The name you specified may not be defined.
The name you specified is defined as something other than a rectangular cell reference.
Check the name and try again.

My question is: how to use some Excel macro or available setting to suppress this kind of warning message?

I have a file which is linked to the dynamic ranges of many external links. Every time this warning message pops up when there is such issue and I have to manually dismiss the warning message before the file update can continue. For this problem, I have to sit in front of the computer for 1 hour. Because the external files with dynamic ranges are made by my colleagues, I cannot change their file. The only thing I can do is to ask Excel to ignore these warning messages and just do the update as much as possible automatically.

[Alf]

Normaly in a macro the line

Please Login or Register  to view this content.

would block pop up messages but I'm a bit unsure since you have a file that is only linked to a number of other files and there is no macro involved in this, perhaps adding an auto open macro to your "master" file with the "displayalerts" line would work?

Please Login or Register  to view this content.

Alf

[BNCOXUK]

Hi Alf,

Thanks a lot for the macro. I'll try to implement it.

Can I ask: if I add the macro to ThisWorkbook, then all alert messages will be suppressed. In this case, when the master file is opened, will the master file be able to automatically updated with data from the external links? I hope the code Application.DisplayAlerts = False will not stop the master from being updated when opened?

Thank you.

Normaly in a macro the line

Please Login or Register  to view this content.

would block pop up messages but I'm a bit unsure since you have a file that is only linked to a number of other files and there is no macro involved in this, perhaps adding an auto open macro to your "master" file with the "displayalerts" line would work?

Please Login or Register  to view this content.

Alf

[Alf]

I hope the code Application.DisplayAlerts = False will not stop the master from being updated when opened?

No it will only block pop-up messages.

Alf

[BNCOXUK]

Hi Alf,

Thanks for your help. I am now trying to make it be implemented.

In the Trust Center window, I need to select the option of 'Enable all Data Connections (not recommended)'. If otherwise, I select 'Prompt user about Data Connections', then I found the master file failed to be updated from external links (all external data remain the same as before), and also the ask-for-update-links window will not pop up as usual. In this case, can I ask if the choice of the option 'Enable all Data Connections (not recommended)' is absolutely needed to allow automatic update of all links?

Besides, when the master file is opened, there is a message of Serurity Warning: Macros have been disabled. So the macro Auto_Open() will not run automatically, and I need to enable the macro by changing the setting. Is this right?

[Alf]

So the macro Auto_Open() will not run automatically, and I need to enable the macro by changing the setting. Is this right?

Yes that is correct.

In "Trust Center Window" you need to set "Enable all Data Connections" but as soon as you have updated the links you could reset this to "Prompt user about Data Connections"

Perhaps you also should add a scond macro to your master file

Please Login or Register  to view this content.

This will reset the the "DisplayAlerts" funtion to default state i.e. showing "Alerts"

Alf

[billj]

found this nice topic.

[BNCOXUK]

Thanks for your help, Alf. Very helpful and I'll shortly implement it to my file.

Yes that is correct.

In "Trust Center Window" you need to set "Enable all Data Connections" but as soon as you have updated the links you could reset this to "Prompt user about Data Connections"

Perhaps you also should add a scond macro to your master file

Please Login or Register  to view this content.

This will reset the the "DisplayAlerts" funtion to default state i.e. showing "Alerts"

Alf