VBA & Macro Problems

How to Split an Excel Workbook into Text Files by Row Count with VBA

Guest Writer Oct 8, 2026 871 views

Question details

The user needs to divide an Excel worksheet into multiple text files, with each output file containing a specific number of rows, utilizing a VBA macro.

How to Split an Excel Workbook into Text Files by Row Count with VBA

Product: Excel
Device & OS: not provided
Scenario: Exporting large datasets into smaller, manageable text-compatible files based on exact row counts for database imports or system integration.
Observed behavior: Requires automating the process of copying data batches to temporary workbooks and saving them sequentially as unique text files (e.g., file-1.txt).

Before you start

Verify whether your target system requires headers in each divided text file, and make sure to save a backup of your original Excel workbook before running any macro scripts.

Solution 1Recommended

Use VBA Loop to Batch Export Rows to Text Files

This solution involves writing a VBA macro that loops through your dataset, copies chunks of rows into a temporary workbook, and saves each workbook as a Unicode text file.

By setting a variable like RowsToCopy, you can accurately control the size of each text file. The process utilizes a temporary workbook to isolate the batch data before saving it in the xlUnicodeText format, preventing unwanted formatting or multi-sheet warnings.

Open the Visual Basic Editor

Press Alt + F11 on your keyboard, or navigate to the Developer tab and click Visual Basic to open the VBA editor.

Insert a New Module

In the Project Explorer panel on the left, right-click on your workbook's name, select Insert, and then click Module to create a blank script window.

Define Row Count and Loop Logic

Write your macro code, establishing a variable such as RowsToCopy = 1000. Create a For loop that runs from the first data row to the last used row in increments of the RowsToCopy value.

Copy Batches to Temporary Workbooks

Within the loop, copy the current range of rows and paste them into a newly added workbook (Workbooks.Add) so the batch is isolated.

Save as xlUnicodeText

Save the temporary workbook using the active workbook SaveAs method. Set the FileFormat parameter to xlUnicodeText and dynamically generate a unique name for each batch, such as "file-" & batchCounter & ".txt".

Close and Suppress Alerts

Use Application.DisplayAlerts = False before saving, and ensure you close the temporary workbook without saving further changes (ActiveWorkbook.Close False) so it is ready for the next loop iteration.

Use VBA Loop to Batch Export Rows to Text Files

Preserving Headers: If each output text file needs a header row, adjust your macro so that Row 1 is copied and pasted into the temporary workbook before copying the batch data into Row 2.

Powerful Spreadsheet Software

Effortlessly Run VBA Macros to Split Datasets with WPS Office

WPS Spreadsheet provides comprehensive support for VBA macros, allowing you to easily execute custom scripts to split large workbooks, format text files, and automate tedious data workflows.

1. Open Your Workbook: Launch WPS Spreadsheet and open the large dataset file you need to split.
2. Access Developer Tools: Navigate to the Tools tab and click on Developer to launch the built-in VBA editor.
3. Execute Your Script: Paste your row-splitting macro into a Module, run the code, and instantly generate your split text files.

Full native support for executing complex VBA scripts and macrosSeamless compatibility with Microsoft Excel formats (.xlsm, .xlsx, .csv)Lightweight architecture handles large datasets without laggingUser-friendly interface makes Developer tools easily accessible

microsoft office alternative - wps office

Frequently Asked Questions

Can I save the split files as CSV instead of text documents?

Yes. To output comma-separated values instead of tab-delimited text, change the FileFormat parameter in your VBA macro from xlUnicodeText to xlCSV.

Why do I get a warning prompt when the macro saves the text file?

Excel warns you that saving as a text file will strip away formatting and multiple sheets. You can bypass this prompt by adding Application.DisplayAlerts = False at the beginning of your macro and turning it back to True at the end.

How do I handle the final batch if it has fewer rows than the target row count?

Your VBA code should dynamically check the remaining rows. Instead of blindly selecting the next 1,000 rows, use a dynamic end-row variable that stops at the LastUsedRow of the worksheet, preventing empty rows from being exported.

Excel & Data Problems