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

[CraZ_CZ]

I'm saving my updates in my workbook automatically to a new template:

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:

Application.ActiveWorkbook.TemplateRemoveExtData = True
Application.ActiveWorkbook.SaveAs fileToSave, xlOpenXMLTemplateMacroEnabled

But if I use

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.

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:

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

 

[jbamps1974]

Hi, i am having the same issue now. Did you ever find a solution to your problem? Or a workaround to have the NO button pressed?

 

[CraZ_CZ]

Yes, I've found it.

When saving the template, do not confirm you want to clear the data.

See more info at my question and answer at SO:

Disable loading Power Query sources after opening an Excel spreadsheet (source doesn't exist error)

I load a local Json file using Power Query in Excel. The source file is dynamic (the source file is picked up via button using FileDialog and then updated via VBA in the query formula). The query l...

stackoverflow.com