Add a workbook from a template and change its name-CodePudding

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 all sheets in all workbooks within 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)

CodePudding user response：

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