How to automatically save Excel worksheets as separate files without VBA macros.

XLTools Automation tool allows you to automate tasks in Excel by creating automation commands in a table, without having to create VBA macros.
This tutorial will take you through the setup of the SaveSheetsAsFiles automation command. Use it when you need to save worksheets in a workbook as separate files as a part of your routine process.
For non-recurring tasks, use the standard Workbook Organizer tool on XLTools ribbon.

Before you begin, add the Automation tool to Excel

Automation is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2024, 2019, 2016, 2013, 2010, and Microsoft 365.
Get started with XLTools ribbon
Download XLTools for Excel
 – free trial gives you 14 days of full access to all features.

Step-by-step example: how to use the SaveSheetsAsFiles command

Say, you have an Excel table with many worksheets. Every week or so, you need to save all worksheets as a separate file.
When you automate this process with the SaveSheetsAsFiles command, you can save worksheets in a button click. Follow the steps below to execute the command yourself.
Feel free to download the sample file used in this tutorial.
Download sample file (xlsx)
Download sample file (xlsx)

1. Insert the SaveSheetsAsFiles command

  1. Let's take a look at the source Excel spreadsheet with orders. In our example, the spreadsheet contains four worksheets "January", "February", "March", "April" with orders in each month.
  2. Open the folder with the source workbook → In the same folder create an Excel spreadsheet → Name one of the worksheets, for example, "AUTO". This worksheet will hold automation commands.
  3. Select a starting cell where to place the command, e.g. A1.
  4. Open XLTools tab → In the Automation group, open the drop-down list → Select SaveSheetsAsFiles.
  5. See the table with the automation command is now added on the worksheet.

2. Specify parameters and define how to save worksheets as separate files

Once you've inserted the SaveSheetsAsFiles command, specify its parameters. This automation table defines how to save each worksheet as a separate file.
XLTools.SaveSheetsAsFiles
Workbook:Orders-source-file-en.xlsx
OutputFolder:C:\Documents\Result
OpenOutputFolder:TRUE
FileFormat:.xlsx
What each line in this command reads:
  • Workbook: take all worksheets in the source workbook and save as separate files.
  • OutputFolder: save the resulting files to the folder C:\Documents\Result.
  • OpenOutputFolder: select TRUE to open the output folder after the command is executed.
  • FileFormat: select .xlsx to save worksheets as Excel files.
See other available options to use in parameters.

3. Execute the command and review the resulting worksheets

  1. Select the table of the automation command → In the Automation group, click Execute Commands.
  2. Review the result:
  • Each worksheet from the source workbook is saved as a separate XLSX file
  • All files are saved to the destination folder of your choice
  • Files are named after the corresponding source worksheets
Done! Now you can assign a custom button to this command.

4. Assign a custom button to the command

If you need to run the command over and over again, add a custom button on the ribbon. So that next time you can execute the command in a click.
  1. In the Automation group, click Add Custom Buttons.
  2. Name the button, e.g. "Split by paid/due" → Specify the range of the SaveSheetsAsFiles command → Click Save.
  3. Done! The custom button will appear on XLTools tab. Now every time you click this button, you will execute the command.
Note:
each time you execute the command, the newly generated files will replace the previously generated files in the destination folder. So before running the command next time, you can change the parameters of the folder.

Parameters and available options

When you create automation commands in Excel, this file with commands must be located in the same destination as the source data:
  • Add automation commands in the same workbook as your source data. We recommend that you place the commands on a separate worksheet not to interfere with your data.
  • Or, add automation commands in a separate workbook. Save this automation file in the same folder as the source data.
Below are all parameters and available attributes for the SaveSheetsAsFiles command.
Tip:
select a cell and see a helpful tooltip. Some attributes also have a drop-down list of available options to choose from.

Workbook:

This parameter specifies the source workbook whose worksheets should be saved as separate files.
  • Required parameter, cannot be blank
OptionsMeaning
Workbook.xlsx
The source workbook. Must be located in the same destination as the automation file.
C:\Documents\Workbook.xlsx
Specify the file path to the source workbook

OutputFolder:

This parameter specifies where to save the resulting files.
  • Required parameter, cannot be blank
OptionsMeaning
C:\Documents\ResultSave the resulting files to the specified folder

OpenOutputFolder:

This parameter defines whether to open the output folder after the command is executed.
  • Optional parameter, you can leave it blank or remove from the command altogether
OptionsMeaning
TRUEOpen the output folder after the command is executed
FALSEDon't open the output folder
(blank)Same as FALSE

FileFormat:

This parameter defines the file format for the resulting files.
  • Required parameter, cannot be blank
OptionsMeaning
.xlsxSave as Excel workbook
.csvSave as CSV file
.pdfSave as PDF file
Explore other automation commands
Any questions or suggestions?
How can we help?