CODEWITHSUNDEEP
excelvbasave-asexcel-external-data
LouFapp
LouFapp

Reputation: 1

SaveAs Template With External Data Connections

I have been hunting for an answer to this for almost an hour now with no luck. I have an Excel Macro Enabled Template which saves the template as a new name (still as a .xltm) but unfortunately this new file becomes corrupt because my template contains external data connections and I am using Application.DisplayAlerts = False to bypass any prompts upon saving. I do not want users to have to deal with a prompt, but when saving a template file with external data connections a prompt appears stating "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?" with Yes, No and Cancel buttons. What I need is for the No to be selected so the user does not see this prompt and the template is saved without that feature. Is this possible?

Note: If I just leave the Application.DisplayAlerts as False, when the new template file is opened an error message is received stating the file is damaged because that external data prompt was never addressed. Not what I want to see at all.

Example of code:

Application.DisplayAlerts = False

Dim strSaveTool as String

strSaveTool = [TL_Loc] & "\New Template Name.xltm" 'where [TL_Loc] holds the directory path

ThisWorkbook.SaveAs Filename:=strSaveTool, FileFormat:=53

Upvotes: 0

Views: 524

Answers (1)

Nej Sanerkin
Nej Sanerkin

Reputation: 11

@PEH I am using sendKeys "N" ... Using your code add SendKeys "N" before the line that raises the prompt. DisplayAlerts needs to be on otherwise the prompt assumes Yes.

To figure out what Key to send experiment on the prompt concerned .. for the - 'This workbook contains external data etc' prompt ... Typing N is accepted in place of clicking NO

Application.DisplayAlerts = TRUE ' False

Dim strSaveTool as String strSaveTool = [TL_Loc] & "\New Template Name.xltm" 'where [TL_Loc] holds the directory path

SendKeys "N"
ThisWorkbook.SaveAs Filename:=strSaveTool, FileFormat:=53

Upvotes: 0

Related Questions