Reading from an External File

---

The procedure to read a file is quite similar to the procedure used to write to a file. The steps required to read a text file using VBA are listed here:

1. Open the file using the Open statement.

2. Specify the position in the file using the Seek function, which is optional.

3. Read the data from the file using the Input, Input #, or Line Input # statement.

4. Close the file using the Close statement.

The following example reads the text file that was created in the previous example and stores the values beginning in the active cell. The code reads each character and separates the line of data, ignoring quote characters and looking for commas to deliminate the columns.

Sub ImportRange()
    Dim ImpRng As Range
    Dim FileName As String
    Dim r As Long
    Dim c As Integer
    Dim txt As String
    Dim Char As String * 1
    Dim Data
    Dim i As Integer
    
    Set ImpRng = ActiveCell
    On Error Resume Next
    FileName = "C:\textfile.txt"
    Open FileName For Input As #1
    If Err <> 0 Then
        MsgBox "Not found: " & FileName, vbCritical, "ERROR"
        Exit Sub
    End If
    r = 0
    c = 0
    txt = ""
    Do Until EOF(1)
        Line Input #1, Data
        For i = 1 To Len(Data)
            Char = Mid(Data, i, 1)
            If Char = "," Then
                ActiveCell.Offset(r, c) = txt
                c = c + 1
                txt = ""
            ElseIf i = Len(Data) Then
                If Char <> Chr(34) Then txt = txt & Char
                ActiveCell.Offset(r, c) = txt
                txt = ""
            ElseIf Char <> Chr(34) Then
                txt = txt & Char
            End If
        Next i
        c = 0
        r = r + 1
    Loop
    Close #1
End Sub

Note 

The procedure is a starting point. It doesn’t take into account how to handle data that might contain commas or a quote character. You’ll also notice that if a date is imported, number signs appear around the date.

---