Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Export Multiple Columns into Individual Text Files in Excel
Excel is a powerful spreadsheet program developed by Microsoft. It is widely used for organizing, analyzing, and manipulating data in various industries and professions. To export multiple columns into individual text files in Excel, you can use VBA (Visual Basic for Applications) macros.
Here's an example of how you can do it:
Step 1
Press Alt + F11 to open the Visual Basic Editor in Excel.
Insert a new module by clicking on "Insert" and selecting "Module."
In the module window, paste the following code:
Sub ExportColumnsToTextFiles()
Dim ws As Worksheet
Dim col As Range
Dim cell As Range
Dim outputPath As String
' Set the output path where the text files will be saved
outputPath = "E:\Assignments\3rd Assignments\How to export multiple columns
into individual text files in Excel\Output"
' Set the worksheet containing the columns you want to export
Set ws = ThisWorkbook.Worksheets("Sheet1")
' Set the range of columns you want to export (change the range as needed)
Set col = ws.Range("A:B")
' Loop through each column
For Each cell In col.Columns
' Get the last row in the column with data
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, cell.Column).End(xlUp).Row
' Check if the column has data
If lastRow > 1 Then
' Create a new text file
Open outputPath & cell.Column & ".txt" For Output As #1
' Loop through each cell in the column up to the last row and write
its value to the text file
For Each c In ws.Range(ws.Cells(1, cell.Column), ws.Cells(lastRow,
cell.Column))
Print #1, c.Value
Next c
' Close the text file
Close #1
End If
Next cell
MsgBox "Columns exported to individual text files."
End Sub
Step 2
Modify the outputPath variable to specify the folder where you want to save the text files. Make sure to include the trailing backslash ().
Modify the Set ws = ThisWorkbook.Worksheets("Sheet1") line to specify the worksheet containing the columns you want to export. Replace "Sheet1" with the name of your worksheet.
Modify the Set col = ws.Range("A:B") line to specify the range of columns you want to export. In this example, columns A to B are selected. Adjust the range as needed.
Close the Visual Basic Editor.
Step 3
Run the macro by pressing Alt + F8, selecting "ExportColumnsToTextFiles," and clicking "Run."
The macro will export each column into an individual text file named after the column number. The text files will be saved in the specified output folder.
Conclusion
The provided VBA code allows you to export multiple columns into individual text files in Excel. By specifying the output path, worksheet, and range of columns, the code loops through each column, determines the last row with data, and exports the column's values up to that row into a separate text file. The corrected version ensures that the loop runs correctly and avoids continuous looping. This solution enables efficient extraction of columnar data, facilitating further analysis or use of the data in external applications.
632 Views
