Read Files Match Number in Text File Vba

In this article I will explicate how y'all can read data from a text file. Yous tin can download the workbook and code for this article here.

Jump To:

• Example 1, Opening a Text File and Reading the First Line
• Example ii, Closing the Text File Afterwards Using it
• Instance 3, Reading All Lines of Text From a Text File
• Example 4, Selecting Text File With Dialog
• Instance five, Checking for Errors When Opening the Text File

–

---

Example 1, Opening a Text File and Reading the Showtime Line:

In this instance a text file with the path "D:test.txt" will be openend and the kickoff line will be displayed in a message box. The text file has the following data in information technology:

This can be achieved by the code below:

Sub Example1()
Dim FilePath As String
Dim strFirstLine Every bit String

FilePath = "D:exam.txt"
Open FilePath For Input As #1
Line Input #1, strFirstLine
MsgBox (strFirstLine)
End Sub


Result:

The starting time line of code sets the value of FilePath to the full path of the text file:

FilePath = "D:test.txt"


The line beneath opens the file at the specified location and assigns the index "1" to it:

Open up FilePath For Input As #1


FilePath:

The full path of the text file to to open

Input:

States that we are only going to use this text file for reading data.

1:

This would be the reference to the file we take opened. Instead of "i" we could have used whatever number between 1 and 511. Usually the number #1 is always used unless if we are attempting to read from multiple files at the same time.

The line beneath reads the first line of data from the file with the index "one" and assigns the value to the variable strFirstLine:

Line Input #1, strFirstLine


i:

The index of the file to read the first line from

strFirstLine:

The variable to assign the data from the first line of the text file to.

–

---

Example two, Closing the Text File Subsequently Using It:

The problem with Example 1 was that it didn't close the file after using it. If you endeavor to run the code in example ane a 2d time y'all will get the following error:

In order to forestall this error you would demand to close the file after using it. This can be done by adding the following line to the code in Case 1:

Close #1


Close #1 closes the file with the "1′ index. Had we used another number while opening the file the same number should be used when closing the file:

Sub Example2()
Dim FilePath As String
Dim strFirstLine As String
FilePath = "D:test.txt"
Open FilePath For Input Equally #ane
Line Input #1, strFirstLine
MsgBox (strFirstLine)
Close #1
End Sub


Instead of using the index "i" nosotros could have used the index "2":

Sub Example3()
Dim FilePath As String
Dim strFirstLine As String
FilePath = "D:exam.txt"
Open FilePath For Input As #2
Line Input #2, strFirstLine
MsgBox (strFirstLine)
Shut #two
End Sub


–

---

Example 3, Reading All Lines of Data in a Text File:

The following code opens the text file located at the path "D:test2.txt" and prints every line in a dissever jail cell. The file test2.txt contains the post-obit data:

The following lawmaking loops through all the lines in the text file and prints them in column A:

Sub Example4()
Dim FilePath As String
Dim strLine As String
Dim i Every bit Integer

FilePath = "D:test2.txt"
Open FilePath For Input As #ane
i = ane
While EOF(1) = False
'read the side by side line of data in the text file
    Line Input #1, strLine
'print the data in the current row
    Cells(i, 1) = strLine
'increment the row counter
    i = i + ane
Wend
Close #i
End Sub


Event:

–

---

Instance 4, Opening a Text File With a Dialog:

In the example below the text file is selected using an open file dialog. All the lines of data from the text file are read and printed in cavalcade A. For more information almost the open file dialog please run into Excel VBA Open File Dialog:

Sub Example5()

Dim strLine As String
Dim i As Integer
Dim intResult As Integer
Dim strPath As String

Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intResult = Application.FileDialog(msoFileDialogOpen).Show
If intResult <> 0 Then
    strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Open strPath For Input Every bit #1
    i = i
    While EOF(1) = Imitation
'read the adjacent line of data in the text file
        Line Input #1, strLine
'print the data in the electric current row
        Cells(i, i) = strLine
'increment the row counter
        i = i + one
    Wend
Stop If
Close #1
Stop Sub


The file called had the following data in it:

Result:

–

---

Example five, Checking for Errors When Opening the Text File:

When opening a text file several things could go wrong which could crusade the program to crash. It's always a good idea to add an exception handler to handle such cases. For more information on this event please see VBA, Check if Text File is Open. The code beneath asks the user to select a file to open. If the file is opened successfully  all the data in the text file printed in cavalcade A. If there is an fault while opening the file, a message box is displayed:

Sub Example6()
Dim strLine As String
Dim i As Integer
Dim intResult As Integer
Dim strPath Every bit String

Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intResult = Application.FileDialog(msoFileDialogOpen).Evidence
If intResult <> 0 And so
    strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    On Error GoTo lblError:
    Open strPath For Input As #1
    i = one
    While EOF(1) = Fake
'read the side by side line of data in the text file
        Line Input #1, strLine
'impress the data in the current row
        Cells(i, 1) = strLine
'increment the row counter
        i = i + 1
    Wend
Stop If
Close #i
Go out Sub

lblError:
MsgBox ("In that location was an mistake opening the file. Implement the necessary deportment")
Err.Clear
Close #i
End Sub


You tin can download the workbook and code for this article here.

See also:

• VBA Excel Open File Dialog
• VBA, Change Existing Text File
• VBA Excel, Writing to a Text File
• VBA, Cheque if Text File is Open
• Discover and Listing All Files and Folders in a Directory

If you need assist with your code, or you lot are looking for a VBA programmer to rent feel free to contact me. Also please visit my website  www.software-solutions-online.com

trinidadforombity.blogspot.com

Source: https://software-solutions-online.com/excel-vba-reading-text-files/

Share this post