Excel: Save or Export All Sheets to Separate CSV or Text Files (2 easy ways)
In the world of data analysis, reporting, and information sharing, Microsoft Excel is often used to manage workbooks containing multiple worksheets. Frequently, there arises a need to save or export each worksheet as its own separate CSV (Comma-Separated Values) or text (.txt) file. Such requirements are common, whether it’s for sharing data with colleagues, importing to other databases or software applications, system integrations, or archiving individual datasets for compliance or backup. However, Excel currently does not offer a native feature to export all sheets at once as separate files, making it somewhat cumbersome to repeat the manual save-as process for each worksheet.
This article guides you through practical and efficient ways to automate the export or conversion of all (or selected) Excel sheets into individual CSV or text files, ensuring your workflow remains streamlined and error-free.
➤ Save or Export all sheets to CSV or Text files with VBA code
➤ Save or Export all / specific sheets with Kutools for Excel
Save or Export all sheets to CSV or Text files with VBA code
While Excel does not provide a built-in function for batch exporting all worksheets as separate CSV or text files, you can automate this process with VBA code. This solution is particularly useful for those comfortable with basic scripting or anyone needing more control over the export process, such as specifying file locations or custom file naming. The following VBA scripts enable the automatic export of every worksheet in your workbook to its own individual file.
Export or convert all sheets to separate CSV files
1. Press Alt + F11 simultaneously to open the Microsoft Visual Basic for Applications (VBA) editor. If your Excel doesn’t display the Developer tab, you still can use this shortcut to access the VBA environment.
2. Within the Microsoft Visual Basic for Applications window, click Insert > Module in the menu. This creates a new module in which you can enter scripts. Next, copy and paste the following VBA code into the newly created window:
Sub ExportSheetsToCSV()
Dim xWs As Worksheet
Dim xcsvFile As String
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.Copy
xcsvFile = CurDir & "\" & xWs.Name & ".csv"
Application.ActiveWorkbook.SaveAs Filename: = xcsvFile, _
FileFormat: = xlCSV, CreateBackup: = False
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next
End Sub3. To run the code, press the F5 key or click the Run button in the toolbar. After execution, you will find that each worksheet has been exported as an individual CSV file and stored in your Documents folder. Each file will be automatically named after its respective worksheet, streamlining the organization of your exported data. Refer to the screenshot below for an example of the exported files:
Notes & Tips: Depending on workbook size and number of worksheets, the export process may take a few seconds. Save your workbook before running any VBA macros to avoid unintended data loss. Ensure that any formulas in your sheets are converted to values (if required) before export, as CSV/text format saves values only, not formulas.
Export or convert all sheets to separate Text files
The following VBA code allows you to export each worksheet in your workbook as an independent text (.txt) file. This is beneficial for scenarios where plain text file import or processing is needed in other systems or platforms.
Sub ExportSheetsToText()
Dim xWs As Worksheet
Dim xTextFile As String
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.Copy
xTextFile = CurDir & "\" & xWs.Name & ".txt"
Application.ActiveWorkbook.SaveAs Filename: = xTextFile, FileFormat: = xlText
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next
End SubAs with the CSV export, the resulting Text files will be saved in your Documents folder, and each will be automatically named after the worksheet it originated from. Refer to the screenshot below for an example.
Important Considerations: When exporting to text, Excel will use the default delimiter (usually tabs) unless the code specifies otherwise. Check the exported files for proper formatting, especially if your data includes commas, line breaks, or special characters. Save your workbook before running scripts, and be aware that file overwriting can occur if files of the same name already exist in the export folder.
Advantages and Limitations of the VBA Method: The VBA method is suitable when automating repetitive exports or when you need custom scripting (for example, exporting to user-selected folders or handling specific file formats). However, it requires enabling macros, which can be restricted in certain corporate environments due to security policies. If your workbook contains sensitive data or macros are disabled, consider alternative solutions described below.
Troubleshooting: If the macro does not export files as expected, ensure that:
- The workbook is saved before running the macro to prevent unexpected closure or errors.
- You have permissions to write to the Documents folder (or modify the code to specify a different location).
- All worksheet names are valid as Windows file names. Characters such as /, \, : in sheet names can cause file creation errors.
- Macro security settings in Excel allow you to run VBA code. Enable macros if prompted.
Save or Export all / specific sheets to CSV or Text files with Kutools for Excel
For users seeking a convenient solution without writing or running VBA scripts, Kutools for Excel provides an intuitive and streamlined method to export all or a selection of worksheets to separate CSV, text, or other file formats with just a few clicks. This approach is ideal for users across all proficiency levels, including those who do not wish to interact with code or who need a quick batch export function frequently.
1. Navigate to the Excel ribbon and click Kutools Plus > Workbook > Split Workbook. This will launch the batch export interface. See screenshot below:
2. In the Split Workbook dialog box, you’ll find all worksheet names listed with checkboxes:
- 1). To export all worksheets, keep all items checked in the Worksheet name section. If you only want to export certain sheets, deselect those you wish to exclude.
- 2). In the Options area, enable the Save as type dropdown and select either Unicode Text (*.txt) or CSV (Macintosh)(*.csv) as your file format of choice. Choosing the appropriate format allows for seamless import into most major databases, other spreadsheet programs, or text editors.
- 3). Once selections are complete, click the Split button to continue.
3. When prompted by the Browse For Folder dialog box, select the folder where you want to save the exported files. Click OK to start the export. Pay attention to the target directory to ensure easy access to your exported documents.
Kutools will immediately process your selection, saving all or chosen worksheets as individual CSV or text files in your specified location. File naming is automatic and matches the worksheet names. This is especially useful for organizing and tracking your data exports with minimal effort.
Compared to manual exporting or using VBA macros, Kutools for Excel offers unique advantages for both individual users and organizations:
- ⚡ Batch export with minimal effort – Export all or select worksheets efficiently, achieving in seconds what could take much longer through native Excel or manual processing.
- 📁 Versatile output choices – Supports multiple formats, such as CSV, TXT, PDF, XLSX, and XLS, making your exports compatible with a wide variety of platforms and business requirements.
- 🔍 Select sheets flexibly – Ability to include or exclude hidden and empty worksheets, keeping your exports concise and focused on your relevant data.
- 🧾 Clear file naming – By default, each output file uses its worksheet name, helping to avoid confusion and unnecessary renaming after export.
- 🧩 Zero scripting knowledge needed – The process is fully graphical and accessible, allowing anyone to export data regardless of technical background.
Demo: Save, export or convert multiple/all sheets to CSV or text files with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in