Add a workbook from a template and change its name

Question

How to Save As specifying the file type and without excel displaying a prompt

I want to loop through all excel files (specifically *.xlsm) from a given folder and for each one, create/open a new workbook FROM a template, make some modifications to it (mainly data copy) then save the created template-based file with a new name based the original files in the folder I'm looping over WIHOUT triggering a dialog.

I found how to loop through files in a folder thanks to this How to loop through workbooks in a folder, the issue lies in the renaming process. The created template-based file automatically receives the template's name.

    If Fnum > 0 Then
        For Fnum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
            Set tworkbook = Workbooks.Add("C:\template\location\template name.xlsm")
            tworkbook.SaveAs Filename:="C:\looped over\folder\location\" & "Mod - " & mybook.Name & ".xlsm"

Since the name is read-only, I have to use saveAs, with which I get prompted to pick Yes or No for whether I want the saved file to run macros. I don't understand, the template is already an .xlsm and I get prompted to pick a name (which also defaults to the template's name)

Your question isn't entirely clear but, if you simply want to stop excel from displaying the prompt, you need to use `Application.DisplayAlerts = False` right before your `tworkbook.SaveAs` line and then turn it back on afterwards with `Application.DisplayAlerts = True` — Glenn G, Nov 02 '21 at 13:26
@GlennG What I want is changing the created file automatically without any prompt during the loop (as of right now, its name defaults to the template name + n). By adding Application.DisplayAlerts = True and False, I simply don't receive the notification to save as macro-enabled xl file. — IKindaNeedAHand, Nov 02 '21 at 13:39

Glenn G · Accepted Answer · 2021-11-02T14:01:44.023

Specify the file format as part of the save as function using FileFormat:=xlOpenXMLWorkbookMacroEnabled

 If Fnum > 0 Then
        For Fnum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
            Set tworkbook = Workbooks.Add("C:\template\location\template name.xlsm")
            Application.DisplayAlerts = False
            tworkbook.SaveAs Filename:="C:\looped over\folder\location\" & "Mod - " & mybook.Name & ".xlsm", _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
            Application.DisplayAlerts = True

Add a workbook from a template and change its name

1 Answers1

Linked