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.
- 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.
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.
Read More : How to Convert Text File to Excel Automatically (3 Suitable Ways)
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.
- 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.
Read More: Convert Excel to Text File with Delimiter (2 Easy Approaches)
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.
- 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.
Read More: How to Convert Excel to Text File with Pipe Delimiter (2 Ways)
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 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.
Read More: How to Extract Data from Cell in Excel (5 Methods)
Similar Readings
- How to Import Data into Excel from Another Excel File (2 Ways)
- How to Import Text File with Multiple Delimiters into Excel (3 Methods)
- Excel VBA: Pull Data Automatically from a Website (2 Methods)
- How to Import Data from Secure Website to Excel (With Quick Steps)
- How to Extract Data from Image into Excel (With Quick Steps)
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.
- 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.
Read More: Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
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 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.
- Third, Run this code and let’s see what happened 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.
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.
Read More: How to Extract Data from Excel Based on Criteria (5 Ways)
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.
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.
Read More: How to Pull Data From Another Sheet Based on Criteria in Excel
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.
Related Articles
- Import Data from Excel into Word Automatically Using VBA (2 Ways)
- How to Extract Data from a List Using Excel Formula (5 Methods)
- Excel Macro: Extract Data from Multiple Excel Files (4 Methods)
- How to Extract Year from Date in Excel (3 Ways)
- How to Extract Month from Date in Excel (5 Quick Ways)
- Extract Text After a Character in Excel (6 Ways)