How to save excel file using VBA

 To save excel file we must know file extension because in excel too many format for example *.xls, *.xlsx, *.xlsm and etc. For XlFileFormat enumeration we can refer Here. 

Complete syntax we can refer here

Workbook.SaveAs (FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

The most importance input is FileName and FileFormat the rest is optional. For FileName we must provide full path and file name. For example .xlsx (51),.xlam (55) and etc.

Below completed code to create Workbook and Save instantly:

Option Explicit
Sub CreateWorkBookAndSaveInSpecificFolder()
    
    'Variables Declaration
    Dim MstWB As Workbook
    Dim StrFullPath As String, StrFldrPath As String
    
    'Assign Variable according to folder path (Ex. folder name is "My Analysis" at Desktop
    StrFldrPath = VBA.Environ("UserProfile") & "\" & "Desktop" & "\" & "My Analysis"
    
    'Assign Variable according to full path
    StrFullPath = StrFldrPath & "\" & "mytemplate.xlsx"
    
    'To check folder exist or not and create if none
    If Dir(StrFldrPath, vbDirectory) <> "" Then
        GoTo Line1
    Else
        MkDir StrFldrPath
    End If
Line1:
    
    'Create New Workbook with normal template
    Set MstWB = Workbooks.Add(1)
    
    'Create Workbook Content for ex. Header
    With MstWB.ActiveSheet
        .Range("A1") = "No."
        .Range("B1") = "Name"
        .Range("C1") = "Address"
        .Range("D1") = "Phone No."
    End With
    
    'Stop prompt user to replace file or not but it will replace instantly
    Application.DisplayAlerts = False
    
    'Save workbook according to file extension
    MstWB.SaveAs Filename:=StrFullPath, FileFormat:=51, CreateBackup:=False
    
    'Reset Stop prompt user back to default
    Application.DisplayAlerts = True
    
    'Close Workbook without saving
    MstWB.Close False

End Sub

Thank You

Microsoft Reference (Save As)
Microsoft Reference (File Format)
Microsoft Reference (Display Alert)