Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))

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 #1End Sub