Importing a text file to a range

The following subroutine in Listing 27-4 reads the text file created in the previous example and then stores the values beginning at the active cell. The code reads each character and essentially parses the line of data, ignoring quote characters and looking for commas to delineate the columns.

Listing 27-4: Reading the Text File from Listing 27-3 and Storing the Values Beginning at the Active Cell

Sub ImportRange()

Dim ImpRng As Range

Dim Filename As String

Dim r As Long, c As Integer

Dim txt As String, Char As String * 1

Dim Data

Dim i As Integer

Set ImpRng = ActiveCell On Error Resume Next

Filename = "c:\windows\desktop\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 = ""

Application.ScreenUpdating = False Do Until EOF(1)

Char = Mid(Data, i, 1) If Char = "," Then 'comma

ActiveCell.Offset(r, c) = txt c = c + 1 txt = ""

ElseIf i = Len(Data) Then 'end of line

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

Application.ScreenUpdating = True End Sub

The procedure above has a flaw: It doesn't handle data that contains a comma or a quote character. In addition, an imported date will be surrounded by number signs: for example, #2001-05-12#.

This example is available on the companion CD-ROM.

0 0

Post a comment