VBA Code to Convert Text File to Excel (7 Methods)

Sometimes we have loads of data stored in text files. If we can transfer the contents from those text files to an Excel workbook in an organized and meaningful way, then it is easier to understand and work on those data. Implementing VBA macro is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you 7 implementations of VBA code to convert the contents of a text file to Excel.

How to Use VBA Code to Convert Text File to Excel: 7 Demonstrations

Following this section, you will learn 7 different ways to convert the contents from a text file to an Excel workbook with VBA code.

1. Embed VBA Code to Convert Contents from a Text File to a New Excel Workbook

Consider the following image. As you can see, we have a Text file named “Test File” in our system. In this text file, we have some random lines. Line breaks are also available in the contents.

We will see how to convert this text file to a new Excel file with the same name. Line breaks from the text file will be represented as empty rows in the Excel workbook.
Let’s see how to get that with the VBA code.

Steps:

• In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• Next, in the pop-up code window, from the menu bar, click Insert -> Module.

• Then, copy the following code and paste it into the code window.

Sub ConvertToNewWorkbook()
    Dim iBook As Workbook
    Dim iTexts As Workbook
    Dim iSheet As Worksheet
    Set iBook = ThisWorkbook
    Set iSheet = iBook.Sheets(1)
    Set iTexts = Workbooks.Open("C:\ExcelDemy\Test File.txt")
    iBook.Sheets(1).Cells.Copy iSheet.Cells
    iBook.Close SaveChanges:=True
End Sub

Your code is now ready to run.

• Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the successful code execution, you will see that a new Excel workbook will open up. The workbook name and the sheet name where data are stored are the same.

For better understanding, we have provided an image below of both the Excel file and the Text file together.

As you can see from the above image, contents from the “Test File” text file are exactly copied in the “Test File” Excel workbook. The columns are joined together to make appropriate spaces for long lines. Line breaks from the text files are represented as empty rows in the workbook.

Sub ConvertToNewWorkbook()

Dim iBook As Workbook
Dim iTexts As Workbook
Dim iSheet As Worksheet

Set iBook = ThisWorkbook

Set iSheet = iBook.Sheets(1)

Set iTexts = Workbooks.Open("C:\ExcelDemy\Test File.txt")

iBook.Sheets(1).Cells.Copy iSheet.Cells
    iBook.Close SaveChanges:=True

End Sub

Read More: How to Convert Text File to Excel Automatically

2. Implement Macro to Convert Contents from a Text File to the Active Workbook

The previous code creates a new workbook while storing the converted text data. However, the VBA code that will be provided in this section will convert the contents from a text file to an active Excel workbook.
The steps to execute this are shown below.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub ConvertToActiveWorkbook()
    Dim iBook As Workbook
    Dim iTexts As Workbook
    Dim iSheet As Worksheet
    Set iBook = ThisWorkbook
    Set iSheet = iBook.Sheets(1)
    Set iTexts = Workbooks.Open("C:\ExcelDemy\Test File.txt")
    iTexts.Sheets(1).Cells.Copy iSheet.Cells
    iTexts.Close SaveChanges:=True
End Sub

Your code is now ready to run.

• After that, Run the macro as we showed you in the above section. The result is shown in the image below.

As a result, contents from the “Test File” text file are exactly copied in Sheet1 of the active Excel workbook. The columns are joined together to make appropriate spaces for long lines. Line breaks from the text files are represented as empty rows in the workbook.

Sub ConvertToActiveWorkbook()

Dim iBook As Workbook
Dim iTexts As Workbook
Dim iSheet As Worksheet

Set iBook = ThisWorkbook

Set iSheet = iBook.Sheets(1)

Set iTexts = Workbooks.Open("C:\ExcelDemy\Test File.txt")

iTexts.Sheets(1).Cells.Copy iSheet.Cells
    iTexts.Close SaveChanges:=True

End Sub

3. Apply VBA Macro to Convert Contents from a Text File to an Existing Workbook

Suppose you want to convert contents from a text file to another Excel workbook stored in your computer system. For instance, we have an Excel workbook named “Test File” on our computer drive. We will see how to convert all contents from the “Test File” text file to the “Test File” Excel book with VBA code.

Steps:

• As previously shown, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub ConvertToExistingWorkbook()
    Dim iBook As Workbook
    Dim iTexts As Workbook
    Dim iSheet As Worksheet
    Set iBook = Workbooks("Test File.xlsx")
    Set iSheet = iBook.Sheets(1)
    Set iTexts = Workbooks.Open("C:\ExcelDemy\Test File.txt")
    iBook.Sheets(1).Cells.Copy iSheet.Cells
    iBook.Close SaveChanges:=True
End Sub

Your code is now ready to run.

• Before running this code, you must open the workbook first.
• After that, Run this piece of code, and the result is shown in the following image.

Contents from the “Test File” text file are exactly copied in the already existing Excel workbook “Test File”. The columns are joined together to make appropriate spaces for long lines. Line breaks from the text files are represented as empty rows in the workbook.

Sub ConvertToExistingWorkbook()

Dim iBook As Workbook
Dim iTexts As Workbook
Dim iSheet As Worksheet

Set iBook = Workbooks("Test File.xlsx")

Set iSheet = iBook.Sheets(1)

Set iTexts = Workbooks.Open("C:\ExcelDemy\Test File.txt")

iBook.Sheets(1).Cells.Copy iSheet.Cells
    iBook.Close SaveChanges:=True

End Sub

4. Insert VBA Code to Convert All Contents from a Text File to a Single Cell in Spreadsheet

If you want to get all the contents from a text file to a single cell in Excel, then follow the steps provided in this section.

Steps:

• Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Secondly, copy the following code and paste it into the code window.

Sub ConvertToSingleCell()
    Dim iObject As New FileSystemObject
    Set iObject = CreateObject("Scripting.FileSystemObject")
    Set SourceFile = iObject.OpenTextFile("C:\ExcelDemy\Test File.txt", ForReading)
    TextString = SourceFile.ReadAll
    SourceFile.Close
    ThisWorkbook.Sheets(2).Range("A1").Value = TextString
End Sub

Your code is now ready to run.

• After you run this code, all the data from the text file will be copied into Cell A1 in the active sheet of the Excel file.

You can adjust the height and width of the row and column to make the contents of the cell more readable.

Sub ConvertToSingleCell()

Dim iObject As New FileSystemObject

Set iObject = CreateObject("Scripting.FileSystemObject")

Set SourceFile = iObject.OpenTextFile("C:\ExcelDemy\Test File.txt", ForReading)

TextString = SourceFile.ReadAll
    SourceFile.Close
    ThisWorkbook.Sheets(2).Range("A1").Value = TextString

End Sub

5. Embed VBA to Convert an Array of Data from a Text File to Excel Workbook

When you have a load of data in your text file to convert, then it is feasible to convert those in array format.
This section will show you how to do that with VBA code in Excel.

Steps:

• First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub ConvertArrayOfData()
    Dim Separator As String
    Dim SourceFile As Integer
    Dim iDirectory As String
    Dim TextContents As String
    Dim LineArray() As String
    Dim DataArray() As String
    Dim TempArray() As String
    Dim iRow As Long
    Dim iColumn As Long
    Separator = vbTab
    iDirectory = "C:\ExcelDemy\Test File.txt"
    iRow = 1
    SourceFile = FreeFile
    Open iDirectory For Input As SourceFile
    TextContents = Input(LOF(SourceFile), SourceFile)
    Close SourceFile
    LineArray() = Split(TextContents, vbNewLine)
    For i = LBound(LineArray) To UBound(LineArray)
        If Len(Trim(LineArray(i))) <> 0 Then
           TempArray = Split(LineArray(i), Separator)
           iColumn = UBound(TempArray)
   ReDim Preserve DataArray(iColumn, iRow)
           For j = LBound(TempArray) To UBound(TempArray)
       DataArray(j, iRow) = TempArray(j)
       Cells(i + 1, j + 1).Value = DataArray(j, iRow)
           Next j
        End If
        iRow = iRow + 1
     Next i
End Sub

Your code is now ready to run.

• Lastly, Run this code and notice the image below.

As a result, contents from the “Test File” text file are exactly copied into the active sheet of the Excel workbook. The columns are joined together to make appropriate spaces for long lines. Line breaks from the text files are represented as empty rows in the workbook.

Sub ConvertArrayOfData()

Dim Separator As String
Dim SourceFile As Integer
Dim iDirectory As String
Dim TextContents As String
Dim LineArray() As String
Dim DataArray() As String
Dim TempArray() As String
Dim iRow As Long
Dim iColumn As Long

Separator = vbTab

iDirectory = "C:\ExcelDemy\Test File.txt"

iRow = 1

SourceFile = FreeFile
    Open iDirectory For Input As SourceFile
    TextContents = Input(LOF(SourceFile), SourceFile)
    Close SourceFile

LineArray() = Split(TextContents, vbNewLine)

For i = LBound(LineArray) To UBound(LineArray)
        If Len(Trim(LineArray(i))) <> 0 Then
           TempArray = Split(LineArray(i), Separator)
           iColumn = UBound(TempArray)
   ReDim Preserve DataArray(iColumn, iRow)
           For j = LBound(TempArray) To UBound(TempArray)
       DataArray(j, iRow) = TempArray(j)
       Cells(i + 1, j + 1).Value = DataArray(j, iRow)
           Next j
        End If
        iRow = iRow + 1
     Next i

End Sub

6. Implement Macro to Convert Contents from a Text File to Excel Based on Separator

Let’s change our text file. Let’s make the text file a bit more advanced this time. The text file we will use in this section holds data that must be presented in an organized way while converting them into an Excel workbook. An example is shown in the image below.

The “Student Information” text file holds the data of students which is hard to understand. We will transfer the contents of this text file to an Excel file in a way that all the IDs will be in Column A, Names in Column B, Department in Column C and Marks will be in Column D. We will use space (“ “) as the separator; meaning whenever the code finds any space (“ “) in the text file, it will place the next word from the text file in the next column in the Excel workbook and move forward like that.
The steps to get with the VBA code are given below.

Steps:

• First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Second, copy the following code and paste it into the code window.

Sub ConvertWithSeparator()
    Dim iTexts As String
    Dim iObject As New FileSystemObject
    Dim SourceObj As Object
    Dim TextContents As Variant
    Dim iData As Long
    Dim i As Long
    Dim Separator As String
    Set iObject = CreateObject("Scripting.FileSystemObject")
    Set SourceObj = iObject.OpenTextFile("C:\ExcelDemy\Student Information.txt")
    Separator = " "
    iData = 1
    Do While SourceObj.AtEndOfStream = False
       iTexts = SourceObj.ReadLine
       TextContents = Split(iTexts, Separator)
       For i = LBound(TextContents) To UBound(TextContents)
           Cells(iData, i + 1).Value = TextContents(i)
       Next i
       iData = iData + 1
    Loop
SourceObj.Close
End Sub

Your code is now ready to run.

• Third, Run this code, and let’s see what happens as the result.

Finally, all the data from the text file are shifted to the active Excel book in an organized and meaningful way.

For better understanding, we have provided an image above of both the Excel file and the Text file together.

Sub ConvertWithSeparator()

Dim iTexts As String
Dim iObject As New FileSystemObject
Dim SourceObj As Object
Dim TextContents As Variant
Dim iData As Long
Dim i As Long
Dim Separator As String

Set iObject = CreateObject("Scripting.FileSystemObject")

Set SourceObj = iObject.OpenTextFile("C:\ExcelDemy\Student Information.txt")

Separator = " "

iData = 1

Do While SourceObj.AtEndOfStream = False
       iTexts = SourceObj.ReadLine
       TextContents = Split(iTexts, Separator)
       For i = LBound(TextContents) To UBound(TextContents)
           Cells(iData, i + 1).Value = TextContents(i)
       Next i
       iData = iData + 1
    Loop
SourceObj.Close

End Sub

7. Insert Macro to Convert an Array of Data from a Text File to Excel Based on Separator

When you have a load of information in your text file to convert in an organized and meaningful way, then it is feasible to convert those in array format.
This section will show you how to do that with VBA code in Excel. We will use the example from the above section in this case too.

Steps:

• First, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Option Explicit
Dim iObject As New FileSystemObject
Dim TextObj As TextStream
Dim iDirectory As String
Private Sub Worksheet_Activate()
    ConvertTextToExcel
End Sub
Sub ConvertTextToExcel()
    iDirectory = "C:\ExcelDemy"
    Set TextObj = iObject.OpenTextFile(iDirectory & "/Student Information.txt")
    Do While Not TextObj.AtEndOfLine
        Dim iData As String
        iData = TextObj.ReadAll
        Dim ArrData
        ArrData = Split(iData, " ")
        Dim ColumnCount
        Dim RowCount
        Dim CellCount
        CellCount = 0
        RowCount = 1
        For ColumnCount = 0 To UBound(ArrData)
            If InStr(ArrData(ColumnCount), vbNewLine) > 0 Then
                Cells(RowCount, CellCount + 1) = Mid(ArrData(ColumnCount), 1, InStr(ArrData(ColumnCount), vbNewLine) - 1)
                RowCount = RowCount + 1
                CellCount = 1
                Cells(RowCount, CellCount) = Mid(ArrData(ColumnCount), InStr(ArrData(ColumnCount), vbNewLine) + 1, Len(ArrData(ColumnCount)))
            Else
                Cells(RowCount, CellCount + 1) = ArrData(ColumnCount)
                CellCount = CellCount + 1
            End If
        Next ColumnCount
    Loop
    TextObj.Close
End Sub

Your code is now ready to run.

• After that, Run this code, and the result is shown in the following image.

Finally, all the data from the text file are shifted to the active Excel book in an organized and meaningful way – all the IDs from the text file are in Column A, Name in Column B, Department in Column C, and Marks in Column D. We used space (“ “) as the separator.

Option Explicit

Dim iObject As New FileSystemObject
Dim TextObj As TextStream
Dim iDirectory As String

Private Sub Worksheet_Activate()
    ConvertTextToExcel
End Sub

Sub ConvertTextToExcel()

iDirectory = "C:\ExcelDemy"
    Set TextObj = iObject.OpenTextFile(iDirectory & "/Student Information.txt")

Do While Not TextObj.AtEndOfLine

Dim iData As String
  iData = TextObj.ReadAll

Dim ArrData
  ArrData = Split(iData, " ")

Dim ColumnCount
Dim RowCount
Dim CellCount

CellCount = 0
RowCount = 1

For ColumnCount = 0 To UBound(ArrData)
    If InStr(ArrData(ColumnCount), vbNewLine) > 0 Then
       Cells(RowCount, CellCount + 1) = Mid(ArrData(ColumnCount), 1, InStr(ArrData(ColumnCount), vbNewLine) - 1)
       RowCount = RowCount + 1
       CellCount = 1
       Cells(RowCount, CellCount) = Mid(ArrData(ColumnCount), InStr(ArrData(ColumnCount), vbNewLine) + 1, Len(ArrData(ColumnCount)))
    Else
       Cells(RowCount, CellCount + 1) = ArrData(ColumnCount)
       CellCount = CellCount + 1
     End If
Next ColumnCount
    Loop
    TextObj.Close

End Sub

Read More: How to Import Data from Text File into Excel

Key Point to Remember

When you work with the FileSystemObject in your VBA code then you must do the following modification in your VBA code window.

• First, go to Tools -> References… from the menu bar of the code window.

• Then, check the Microsoft Scripting Runtime box and click OK.

Otherwise, you will get errors if you don’t turn on the Microsoft Scripting Runtime while working with file objects in Excel VBA.

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

This article showed you 7 implementations of VBA code to convert the contents of a text file to Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

Related Articles

• How to Import Text File with Multiple Delimiters into Excel
• How to Convert Notepad to Excel with Columns

<< Go Back to Import Text File to Excel | Importing Data in Excel | Learn Excel