Page 323 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Files

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 Chapter 13
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

297
Part 4: Advanced VBA