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 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.


Download Workbook

You can download the free practice Excel workbook from here.


7 Demonstrations of VBA Code to Convert Text File to Excel

Following this section, you will learn 7 different ways how 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.

VBA Code to Convert Text File to New Excel

  • 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.

Result of VBA Code to Convert Text File to New Excel

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.

VBA Code Explanation

Sub ConvertToNewWorkbook()

First, provide a name for the sub-procedure of the macro.

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

Then, declare the necessary variables for the macro.

Set iBook = ThisWorkbook

Next, specify the Excel workbook where the contents from the text file will be pasted.

Set iSheet = iBook.Sheets(1)

After that, specify the worksheet where the contents from the text file will be pasted.

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

Later, specify the path of your text file in the system.

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

Lastly, perform the copy operation.

End Sub

Finally, end the sub-procedure of the macro.


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

Previous code creates a new workbook while storing the converted text data. But the VBA code that will be provided in this section will convert the contents from a text file to the active Excel workbook.
Steps to execute that 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.

VBA Code to Convert Text File to Active Excel

  • 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 the 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.

VBA Code Explanation

Sub ConvertToActiveWorkbook()

First, provide a name for the sub-procedure of the macro.

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

Then, declare the necessary variables for the macro.

Set iBook = ThisWorkbook

Next, specify the Excel workbook where the contents from the text file will be pasted.

Set iSheet = iBook.Sheets(1)

After that, specify the worksheet where the contents from the text file will be pasted.

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

Later, specify the path of your text file in the system.

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

Lastly, perform the copy operation.

End Sub

Finally, end the sub-procedure of the macro.


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 that “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.

VBA Code to Convert Text File to Existing Excel

  • 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.

VBA Code Explanation

Sub ConvertToExistingWorkbook()

First, provide a name for the sub-procedure of the macro.

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

Then, declare the necessary variables for the macro.

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

Next, specify the Excel workbook where the contents from the text file will be pasted.

Set iSheet = iBook.Sheets(1)

After that, specify the worksheet where the contents from the text file will be pasted.

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

Later, specify the path of your text file in the system.

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

Lastly, perform the copy operation.

End Sub

Finally, end the sub-procedure of the macro.


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.

VBA Code to Convert Text File to a Cell in Excel

  • After you run this code, all the data from the text file will be copied in 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.

VBA Code Explanation

Sub ConvertToSingleCell()

First, provide a name for the sub-procedure of the macro.

Dim iObject As New FileSystemObject

Then, declare the necessary variable for the file object.

Set iObject = CreateObject("Scripting.FileSystemObject")

Next, create and specify the file object to work with the object later in the code.

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

After that, specify the path of the text file and open it to read-only.

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

Later, read the whole text file, close it and place the contents in cell A1 of the active Excel workbook.

End Sub

Finally, end the sub-procedure of the macro.


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:

  • At 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.

VBA Code to Convert Text File in Array to Excel

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

As a result, contents from the “Test File” text file are exactly copied in 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.

VBA Code Explanation

Sub ConvertArrayOfData()

First, provide a name for the sub-procedure of the macro.

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

Then, declare the necessary variables for the macro.

Separator = vbTab

Next, specify the delimiter that is used in the text file.

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

After that, specify the path of your text file in the system.

iRow = 1

To scan through the whole dataset.

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

Then, to return the file number with the FreeFile function, it takes the text file as the input parameter, opens it, returns its size with the LOF function and then closes the file.

LineArray() = Split(TextContents, vbNewLine)

Next, specify the line separator that is used in the text file. Modify this line from vbNewLine to vbCrLf or vbLf according to your dataset.

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

Lastly, perform the copy-paste operation. The contents from the text file are copied and pasted in the cell A1 of the active worksheet.

End Sub

Finally, end the sub-procedure of the macro.


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.

Dataset for VBA Code to Convert Text File to Excel

The Student Information” text file holds the data of students which are hard to understand. We will transfer the contents of this text file to an Excel file in a way that all the ID will be in Column A, Name 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.

VBA Code to Convert Text File to Excel Workbook

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

Result of VBA Code to Convert Text File to Excel

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

Result of VBA Code to Convert Text File to Excel Workbook

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

VBA Code Explanation

Sub ConvertWithSeparator()

First, provide a name for the sub-procedure of the macro.

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

Then, declare the necessary variables for the macro.

Set iObject = CreateObject("Scripting.FileSystemObject")

Next, create and specify the file object to work with the object later in the code.

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

After that, specify the path of the text file and open it.

Separator = " "

Next, specify the separator that is used in the text file. We used space (” “) as the separator in our text file. You must modify this line based on your dataset.

iData = 1

To scan through the whole dataset.

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

Later, it starts iterating from the smallest subscript to the largest subscript of the array, then splits the text contents based on the separator and later stores the value in the variable. It continues doing this until no value is left there to scan. Finally, close the source text file.

End Sub

Finally, end the sub-procedure of the macro.


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.

VBA Code to Convert Text File in Array Format to Excel

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

Result of VBA Code to Convert Text File in Array Format to Excel

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.

VBA Code Explanation

Option Explicit

First, forces to declare all the variables explicitly of the file.

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

Then, declare the necessary variables for the macro.

Private Sub Worksheet_Activate()
    ConvertTextToExcel
End Sub

After that, call the sub-procedure of the macro from the Worksheet_Activate event.

Sub ConvertTextToExcel()

Next, initiate the sub-procedure of the macro.

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

Specify the folder path where the source text file is stored. Then, define the object of the source text file and open it.

Do While Not TextObj.AtEndOfLine

To check whether the current position is the end of a line or not with the AtEndOfLine read-only property.

Dim iData As String
  iData = TextObj.ReadAll

Again, declare a variable to read all contents from the source file.

Dim ArrData
  ArrData = Split(iData, " ")

Declare another variable to specify the separator based on which the split operation will occur with the Split function and store the split data in that variable.

Dim ColumnCount
Dim RowCount
Dim CellCount

Again, declare the rest of the necessary variables for the macro.

CellCount = 0
RowCount = 1

To scan through the whole dataset.

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

Later, it starts iterating from 0 to the largest subscript of the array, then returns the position of the first occurrence of the strings with the InStr function and later stores the value in the variable. It continues doing this until no value is left there to scan. Finally, close the source text file.

End Sub

Finally, end the sub-procedure of the macro.


Key Point to Remember

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

  • First, go to the 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.


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.

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo