VBA & Macro Problems

Bulk Convert XLSX Files to XLTX Templates in Excel with VBA

Aamir Naveed AkramAamir Naveed Akram Nov 13, 2026 868 views

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.

Bulk Convert XLSX Files to XLTX Templates in Excel with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Prepare the source and target folders

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\.

2
Open the Visual Basic Editor

In Excel, press Alt+F11 to open the Visual Basic for Applications editor.

3
Insert a new VBA module

In the editor, click Insert > Module to add a standard module to the current workbook.

4
Paste and customize the VBA macro

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

5
Run the macro

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.

Run an Excel VBA Macro to Convert All XLSX Files to XLTX
Batch conversion completed: Check the target folder after the macro finishes to confirm that each .xlsx workbook has a matching .xltx template.
Free Microsoft Office alternative

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. 1. Download WPS Office: Go to the official WPS Office website and download the free WPS Office installer for your device.
  2. 2. Open an XLSX workbook: Launch WPS Spreadsheets, click Menu > Open, and select an existing .xlsx workbook.
  3. 3. Save as an Excel template: Click Menu > Save As and choose Excel Template (.xltx) to create editable template files in WPS Office.
Opens and saves Microsoft Excel .xlsx and .xltx template filesFamiliar ribbon interface and spreadsheet features for Excel usersFree, lightweight installation compared with Microsoft ExcelSeamless migration from existing Excel workbooksCompatible with Microsoft Office file formats including XLSX and XLTX
microsoft office alternative - 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.