Excel VBA: How to Extract Data from Excel to Text File

In this post we are going to see how to export data from excel to text file using VBA.

Below is the VBA Code.

Sub ExceltoText()

'Declaring the variables
Dim FileName, sLine, Deliminator As String
Dim LastCol, LastRow, FileNumber As Integer

'Excel Location and File Name
FileName = Thisworkbook.path & "\ExceltoText.txt"

'Field Separator
Deliminator = "|"

'Identifying the Last Cell
LastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
FileNumber = FreeFile

'Creating or Overwrighting a text file
Open FileName For Output As FileNumber

'Reading the data from Excel using For Loop
For i = 1 To LastRow
For j = 1 To LastCol

'Removing Deliminator if it is wrighting the last column
If j = LastCol Then
sLine = sLine & Cells(i, j).Value
Else
sLine = sLine & Cells(i, j).Value & Deliminator
End If
Next j

'Wrighting data into text file
Print #FileNumber, sLine
sLine = ""
Next i

'Closing the Text File
Close #FileNumber

'Generating message to display
MsgBox "Text file has been generated"

End Sub

See it in action.