VBA: How to suppress external data dialog when saving to a template?

CraZ_CZ

New Member
Joined
Sep 16, 2022
Messages
1
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I'm saving my updates in my workbook automatically to a new template:
VBA Code:
Application.ActiveWorkbook.SaveAs fileToSave, xlOpenXMLTemplateMacroEnabled

However, Excel always asks me if I want to clear external data and refresh it after template is opened:
This workbook contains external data. Do you want Microsoft Excel to clear the data before saving the template, and then automatically refresh the data whenever the template is opened?
Yes No Cancel
I don't want Excel to clear external data (I'm clearing them by myself before saving a new template) and update them after opening the template. Therefore the expected answer is No.

However, I cannot automate this behaviour (No answer) in VBA. I can simulate only Yes answer using the code below:
VBA Code:
Application.ActiveWorkbook.TemplateRemoveExtData = True
Application.ActiveWorkbook.SaveAs fileToSave, xlOpenXMLTemplateMacroEnabled

But if I use
VBA Code:
Application.ActiveWorkbook.TemplateRemoveExtData = False
, then the dialog appears again when saving the template. False is simply ignore.

I can suppress the dialog (it won't show up), but the Excel understands it as Yes answer anyway.
VBA Code:
Application.DisplayAlerts = False
Application.ActiveWorkbook.TemplateRemoveExtData = True
Application.ActiveWorkbook.SaveAs fileToSave, xlOpenXMLTemplateMacroEnabled

I also tried to deactivate updating external contents/links before saving the template but none of these help:
VBA Code:
ActiveWorkbook.UpdateRemoteReferences = False
ActiveWorkbook.UpdateLinks = xlUpdateLinksNever

Would you know how to suppress the dialog in question and answer it as No (i.e. without clearing the date and updating later when template is opened)? Thanks.

Btw, this question is follow up of my original question on StackOverflow. But this new question is more difficult and you guys here seem to be much more technically skilled in Excel then SO users ;)
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,214,659
Messages
6,120,785
Members
448,992
Latest member
prabhuk279

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