Bulk Convert XLSX Files to XLTX Templates in Excel with VBA
Question details
The user needs to convert a large number of Excel .xlsx workbooks into .xltx template files without opening and saving each file manually.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user has hundreds of Excel XLSX workbooks in folders and wants to turn them into XLTX template files for reuse or distribution.
- Observed behavior
- Converting each workbook manually through Save As is time-consuming and repetitive.
Place all source .xlsx files into one dedicated source folder and create an empty target folder for the generated .xltx templates. Note the full folder paths and make sure they include a trailing backslash before running the macro.
Run an Excel VBA Macro to Convert All XLSX Files to XLTX
Use this method when you have many XLSX workbooks in one folder and want a single VBA script to open, convert, and close each file automatically.
The macro uses the Dir function to loop through every .xlsx file in the source folder, opens each workbook, saves it as an .xltx template with FileFormat:=xlOpenXMLTemplate, and closes the workbook without saving changes.
Use the following macro and adapt the source and target folder paths to your own locations.
Create a source folder that contains all the XLSX workbooks and a separate empty target folder for the XLTX templates. Keep the full paths ready, for example C:\Excel\Workbooks\ and C:\Excel\Templates\.
In Excel, press Alt+F11 to open the Visual Basic for Applications editor.
In the editor, click Insert > Module to add a standard module to the current workbook.
Paste the following macro into the module window, then replace SourceFolder = "C:\Excel\Workbooks\" and TargetFolder = "C:\Excel\Templates\" with your actual paths, keeping the trailing backslash: Sub xlsx2xltx() Dim SourceFolder As String Dim TargetFolder As String Dim SourceFile As String Dim TargetFile As String Dim WrkBook As Workbook SourceFolder = "C:\Excel\Workbooks\" TargetFolder = "C:\Excel\Templates\" Application.ScreenUpdating = False SourceFile = Dir(SourceFolder & "\*.xlsx") Do While SourceFile <> "" Set WrkBook = Workbooks.Open(Filename:=SourceFolder & SourceFile) TargetFile = Replace(SourceFile, ".xlsx", ".xltx") WrkBook.SaveAs Filename:=TargetFolder & TargetFile, FileFormat:=xlOpenXMLTemplate WrkBook.Close SaveChanges:=False SourceFile = Dir Loop Application.ScreenUpdating = True End Sub
Click inside the macro, press F5, or go to Run > Run Sub/UserForm. Wait until the macro completes; processing hundreds of workbooks may take considerable time.

Use WPS Office as a Free, Lightweight Excel Alternative
If you often create and manage Excel workbooks and templates, WPS Office is a free, lightweight Office alternative that opens, edits, and saves .xlsx and .xltx files with a familiar ribbon interface. It offers strong Microsoft Excel format compatibility and easy migration from Excel without a Microsoft 365 subscription.
- 1. Download WPS Office: Go to the official WPS Office website and download the free WPS Office installer for your device.
- 2. Open an XLSX workbook: Launch WPS Spreadsheets, click Menu > Open, and select an existing .xlsx workbook.
- 3. Save as an Excel template: Click Menu > Save As and choose Excel Template (.xltx) to create editable template files in WPS Office.

Frequently Asked Questions
Can I convert hundreds of XLSX files to XLTX without opening each one?
Yes. Put all source workbooks in one folder, run an Excel VBA macro that loops through each .xlsx file, and saves it as an .xltx template with FileFormat:=xlOpenXMLTemplate.
What should I check before running the batch conversion macro?
Confirm the source and target folder paths exist, use trailing backslashes, and close any unnecessary workbooks. Back up the source files first because processing many workbooks may take time.
Where do I change the source and target folder paths in the macro?
Edit the SourceFolder and TargetFolder variables in the VBA module. For example, change SourceFolder = "C:\Excel\Workbooks\" and TargetFolder = "C:\Excel\Templates\" to your own folder paths.
Does WPS Office support opening and saving Excel template files?
WPS Office can open and save Microsoft Excel formats including .xlsx and .xltx, so you can use it as a free alternative for viewing and managing Excel-compatible templates.
Why does the macro use FileFormat:=xlOpenXMLTemplate?
xlOpenXMLTemplate is the Excel file format constant that tells Excel to save the workbook as an Office Open XML template with the .xltx extension instead of a standard .xlsx workbook.




