I'd suggest using a function to parse the long string into fields...

Good luck...

Mike Clare


Sub SampleMacro()
'
Dim r1 as Integer
Dim c1 as integer
Dim s1 As String
Dim s2 As String
Dim s3 As String
Dim fname As String
fname = Application.GetOpenFilename("Text Files (*.txt;*.csv)
*.txt;*.csv")
Open fname For Input As #1
r1 = 1
Do While Not EOF(1)
Line Input #1, s1
s2 = parseCSV(s1, 2)
if s2="Offers" then
c1 =1
s2=parseCSV(s1,c1)
do while len(s2)0
ActiveCell.Offset(r1, c1) = s2
c1=c1+1
s2=parseCSV(s1,c1)
loop
r1 = r1 +1
end if
Loop
End Sub

Private Function parseCSV(s0 As String, i1 As Integer)
Dim i0 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim i4 As Integer
Dim i5 As String
Dim q1 As String
Dim q2 As String
Dim s1 As String
Dim s2 As String
s1 = s0
q1 = Chr(34)
q2 = q1 & ","
i2 = 1 ' i2 is the pointer to the current field
i3 = Len(s1) ' i3 is the length of the field
i4 = 1 ' i4 is the field counter
i0 = InStr(s1, q1 & q1)
Do While (i0 < 0) ' this loop replaces d.dquote with unprintabl
characters
Mid(s1, i0, 2) = Chr(1) & Chr(1)
i0 = InStr(s1, q1 & q1)
Loop
Do While (i4 < i1 And i2 < i3) ' the first loop jumps through th
leading fields
If Mid(s1, i2, 1) = q1 Then ' does the field begin with a dquote
i5 = InStr(i2 + 1, s1, q2) ' i5 points to next '",'
If i5 = 0 Then ' if '0' then end of string
i5 = i3
i2 = i3
Else
i2 = i5 + 2 ' set the pointer to point to nex
field
End If
Else
i5 = InStr(i2 + 1, s1, ",") ' find the next comma
If i5 = 0 Then
i5 = i3 ' not found then eol
i2 = i3
Else
i2 = i5 + 1 ' set pointer to start of next field
End If
End If
i4 = i4 + 1 ' bump counter
Loop
If i2 = i3 Then ' when it gets here, it is pointing to the field
parseCSV = "" ' *or* it ran out of fields
Else
If Mid(s1, i2, 1) = q1 Then ' begin with a dquote?
i5 = InStr(i2 + 1, s1, q2) ' bump pointer
If i5 = 0 Then ' is field @ eol
i5 = i3
End If
s2 = Mid(s1, i2 + 1, i5 - i2 - 1) ' extract the target field
i2 = InStr(s2, Chr(1) & Chr(1)) ' look for previously flagge
d.dquote
Do While i2 < 0
s2 = Left(s2, i2) & Right(s2, Len(s2) - i2 - 1)
Mid(s2, i2, 1) = q1
i2 = InStr(s2, q1 & q1)
Loop
Else
i5 = InStr(i2 + 1, s1, ",") ' point to next comma
If i5 = 0 Then ' if eol, point to end
i5 = i3
End If
s2 = Mid(s1, i2, i5 - i2) ' extract feild
End If
parseCSV = s2
End If
End Functio

--
Message posted from http://www.ExcelForum.com