Al Arafat Siddique
This article illustrates how to import a text file to Excel using VBA code with 3 suitable examples. We’ll use Excel’s built-in Split, InStr, and Replace functions to configure our code. Let’s dive into the examples to explore the techniques that can get your job done fast and easily.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Easy Ways to Import Text File Data to Excel Worksheet Using VBA
Let’s say we have a dataset in a comma–separated text file. The dataset looks like this-
The dataset represents sale details for a shop. Here, each of the data is separated by a comma. We want to import this data from the text file to an Excel spreadsheet.
Introduction to VBA Split and InStr Function:
We’re going to use the VBA Split and InStr functions to configure the VBA code to import text files to an Excel worksheet. Let’s introduce the functions first.
VBA Split Function: The Split function in Excel VBA is used to split a string into substrings. The function returns a zero-based one-dimensional array. Each element of the array is a substring separated by a predefined delimiter. The syntax of the VBA function is-
Split(expression, [delimiter, [limit, [compare]]])
Here,
expression– This required parameter represents a text string that contains substrings and delimiter. If the string is empty, the function will also return an empty array.
delimiter– A string character that is used to split the string into substrings. If omitted the function will use a space character as the delimiter. And if it is an empty string, it’ll return the original string as the output.
limit– It represents the number of substrings to return in the output. If omitted, the function will return all the substrings.
compare– It has several values. We can use vbBinaryCompare for a case–sensitive delimiter and vbTextCompare for a case-insensitive delimiter in the Split function.
VBA InStr Function: We use the InStr function in Excel VBA to search a specific string within a given string from a predefined position. The syntax is-
InStr([start], string 1, string 2, [compare])
Here,
[start]- The position from which it starts searching. The default is 1 if omitted.
string 1- The given string from which the function searches for the desired string.
string 2- The specific string that the function searches within the given string.
[compare]- The type of comparison. The default is Binary Comparison.
Write Code in Visual Basic Editor
To import text files into Excel, we need to open and write VBA code in the visual basic editor. Follow the steps to open the visual basic editor and write some code there.
- Go to the Developer tab from the Excel Ribbon.
- Click the Visual Basic option.
- In the Visual Basic For Applications window, click the Insert dropdown to select the New Module
Now put your code inside the visual code editor and press F5 to run it.
1. Develop a VBA Code to Import Text File to Excel with Specific File Location
Task: Import the text file data into the active worksheet using VBA code. For this, we need to use the specific text file location in our code.
Solution: To get the exact text file location, we can do this-
- Go to the text file location.
- Press the Shift key and right–click on the text file.
- Paste the location in the textFileLocation variable in the code.
Code: Insert the following code in the visual basic editor and press F5 to run it.
Sub ImportTextFileToExcel()
Dim textFileNum, rowNum, colNum As Integer
Dim textFileLocation, textDelimiter, textData As String
Dim tArray() As String
Dim sArray() As String
textFileLocation = "D:\Exceldemy\SampleData.txt"
textDelimiter = ","
textFileNum = FreeFile
Open textFileLocation For Input As textFileNum
textData = Input(LOF(textFileNum), textFileNum)
Close textFileNum
tArray() = Split(textData, vbLf)
For rowNum = LBound(tArray) To UBound(tArray) - 1
If Len(Trim(tArray(rowNum))) <> 0 Then
sArray = Split(tArray(rowNum), textDelimiter)
For colNum = LBound(sArray) To UBound(sArray)
ActiveSheet.Cells(rowNum + 1, colNum + 1) = sArray(colNum)
Next colNum
End If
Next rowNum
MsgBox "Data Imported Successfully", vbInformation
End SubImported Dataset:
Read More: How to Import Text File to Excel Automatically (2 Suitable Ways)
Similar Readings
- Excel VBA to Import CSV File without Opening (3 Suitable Examples)
- How to Import CSV into Existing Sheet in Excel (5 Methods)
- Excel VBA to Convert CSV File to XLSX (2 Easy Examples)
- How to Read CSV File in Excel (4 Fastest Ways)
- Open CSV File in Excel with Columns Automatically (3 Methods)
2. Import Text File to Excel with File Selection Option Using VBA
Task: Import the text file data into the active worksheet with the file selection option using VBA code.
Solution: We need to use the Application.GetOpenFilename method to open a dialogue box to select our desired text file. That’ll get the filename and location without opening it in the first place.
Code: Insert the following code in the visual basic editor and press F5 to run it.
Sub ImportTextFileToExcel()
Dim textFileNum, rowNum, colNum As Integer
Dim textFileLocation, textDelimiter, textData As String
Dim tArray() As String
Dim sArray() As String
textFileLocation = Application.GetOpenFilename()
textDelimiter = ","
textFileNum = FreeFile
Open textFileLocation For Input As textFileNum
textData = Input(LOF(textFileNum), textFileNum)
Close textFileNum
tArray() = Split(textData, vbLf)
For rowNum = LBound(tArray) To UBound(tArray) - 1
If Len(Trim(tArray(rowNum))) <> 0 Then
sArray = Split(tArray(rowNum), textDelimiter)
For colNum = LBound(sArray) To UBound(sArray)
ActiveSheet.Cells(rowNum + 1, colNum + 1) = sArray(colNum)
Next colNum
End If
Next rowNum
MsgBox "Data Imported Successfully", vbInformation
End SubRunning the above code would open the file explorer to select the text file.
Imported Dataset:
Read More: Excel VBA: Read Text File into String (4 Effective Cases)
3. Import Text File Data with Multiple Delimiters into Excel Worksheet
Task: Import a text file having multiple delimiters into an Excel worksheet. Here is a dataset with commas and semicolons as delimiters in it.
Solution: In the following code, we’ll replace the semicolons of the text file with commas by using the VBA Replace function.
Code: Insert the following code in the visual basic editor and press F5 to run it.
Sub ImportTextFileDatatoExcel()
Dim fileLocation As String, textData As String
Dim rowNum As Long
folderLocation = "D:\Exceldemy"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set folder = FSO.GetFolder(folderLocation)
rowNum = 1
Close #1
For Each textFile In folder.Files
fileLocation = folder & "\" & textFile.Name
Open fileLocation For Input As #1
Do While Not EOF(1)
Line Input #1, textData
textData = Replace(textData, ";", ",")
If InStr(textData, ",") = 0 Then
Cells(rowNum, 1) = textData
Else
tArray = Split(textData, ",")
nColumn = 1
For Each element In tArray
Cells(rowNum, nColumn) = element
nColumn = nColumn + 1
Next element
End If
rowNum = rowNum + 1
Loop
Close #1
Next textFile
End SubImported Dataset:
Read More: Excel VBA: Import Comma Delimited Text File (2 Cases)
Things to Remember
- The code we used in the third example can import multiple text files into an Excel worksheet from the specified folder.
- If the specified delimiter doesn’t exist in the source string, the Split function will return the string as it is.
- If the compare argument of the Split function is omitted, the default value is
Conclusion
Now, we know how to import text files to Excel worksheets using VBA in Excel with the help of suitable examples. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.
