VBA & Macro Problems

How to Create Text Files from Excel Rows Using VBA

Bushra ParveenBushra Parveen Nov 2, 2026 869 views

Question details

The user needs to generate one separate text file for each populated row in a spreadsheet, dynamically assigning the filename and file content based on specific columns.

How to Create Individual Text Files from Excel Rows Using VBA
Product
Excel
Device & OS
not provided
Scenario
Automating the extraction of row data where Column A dictates the name of the exported file, and Column B provides the actual text body.
Observed behavior
Instead of manually copying and pasting row data into individual Notepad files, the user seeks an automated VBA script to output plain text files directly to the workbook folder.
Before you start

Ensure you have the Developer tab enabled in your spreadsheet software to access the VBA Editor, and make sure to save your workbook as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use a VBA Macro to Loop Through Rows and Export Text Files

Writing a VBA script is the most direct and efficient method to iterate through populated rows, extract data, and save plain text (.txt) files into your workbook's directory.

This script will systematically read each row. It captures the value in Column A to name the file and the value in Column B to write the text content. It utilizes standard VBA file handling commands to quickly generate plain text outputs.

1
Open the VBA Editor

Press Alt + F11 in your spreadsheet application to open the Visual Basic for Applications (VBA) editor window.

2
Insert a New Module

In the left-hand Project Explorer pane, right-click on your workbook name, select 'Insert', and then click 'Module'.

3
Write the Loop and File Handling Code

Write a `For` loop to iterate through the populated rows. Inside the loop, assign the value of Column A to a filename variable and Column B to a content variable.

4
Append the File Extension

Ensure you append `.txt` to your filename variable (e.g., `FileName = Cells(r, 1).Value & ".txt"`) so the output saves as a readable text file.

5
Execute the Macro

Press F5 or click the 'Run' button on the toolbar. The macro will process the rows and save the resulting text files in the same folder where your workbook is currently saved.

Use a VBA Macro to Loop Through Rows and Export Text Files
Verify File Names: Double-check that the cells in Column A do not contain illegal characters (like \ / : * ? " < > |) as Windows cannot use them in file names.
Efficient Spreadsheet Automation

Automate Data Extraction Seamlessly in WPS Office

WPS Spreadsheet offers robust VBA and macro support, allowing you to run custom scripts that generate individual text files from row data effortlessly. Experience advanced spreadsheet automation without heavy software.

  1. 1. Open Your Data File: Launch WPS Spreadsheet and open the workbook containing the row data you want to convert.
  2. 2. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on 'Visual Basic' or 'Macros' to open the script editor.
  3. 3. Input the VBA Script: Insert a new module and paste your text-file generation macro into the editor.
  4. 4. Run the Automation: Click 'Run' to execute the script and watch as your text files are instantly generated in your workbook's directory.
Full support for VBA macros and advanced developer toolsHigh compatibility with Microsoft Excel macro-enabled workbooks (.xlsm)Lightweight, fast, and free to use for everyday office tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why are my output files saving without an extension?

If you do not explicitly add the file extension in your VBA code, the system will save them as generic files. Make sure to append `& ".txt"` to the variable reading from Column A.

Can I change the destination folder for the generated text files?

Yes. By default, macros often use `ThisWorkbook.Path` to save files in the current directory. You can easily change this by hardcoding a specific folder path, such as `"C:\Users\YourName\Desktop\Output\" & FileName`.

How do I fix file path errors when running the VBA macro?

Path errors usually occur when Column A contains characters that Windows restricts in file names (e.g., asterisks, question marks, slashes). Clean your data in Column A to ensure valid naming conventions before running the script.

Can I include data from multiple columns in the text file?

Absolutely. You can concatenate values in your VBA script before writing them to the file. For example, `OutputString = Cells(r, 2).Value & vbCrLf & Cells(r, 3).Value` will write the contents of both Column B and C into the text file on separate lines.