VBA: Read Text Files

Reading Text Files using VBA is one of the major development activity of programmers. There are multiple ways to read a file


1. Input # Statement

2. Input Function

3. Get Function

4. File System Object Functions

Input # Statement

Dim MyString, MyNumber

Open "c:\test.txt" For Input As #1 ' Open file for input.

Do While Not EOF(1) ' Loop until end of file.

     Input #1, MyString, MyNumber ' Read data into two variables.

     Debug.Print MyString, MyNumber ' Print data to the Immediate window.

Loop

Close #1 ' Close file.

However, the bug here is Input # does not take the leading or trailing spaces with it. That is, ' My Name is ' becomes 'My Name is'. This will not be the correct one as we need to get the spaces also

Then Input function comes handy

Dim MyChar

Open "c:\test.txt" For Input As #1 ' Open file.

Do While Not EOF(1) ' Loop until end of file.

>      MyChar = Input(1, #1) ' Get one character.

>      Debug.Print MyChar ' Print to the Immediate window.

Loop

Close #1 ' Close file.

However, the bug here will be the input that one needs - the number of
characters to be extracted.

The obvious option is File system object


Sub Read_text_File()



Dim oFSO As New FileSystemObject

Dim oFS



Set oFS = oFSO.OpenTextFile("c:\textfile.TXT")


Do Until oFS.AtEndOfStream
sText = oFS.ReadLine
Loop

End Sub

This will read line-by line. all you need to add the Microsoft Scripting Runtime in the reference

= Good Luck =