I am trying to copy 3 data strings from a text file and paste them
into 3 adjacent cells in the calling sheet. I have code so far that
opens the file, finds the line with the correct data and tries to
parse it.

my datafile is in the following format:
Data1|Data2|Data3|Data4|Data5|Data6|Data7|Data8

where the separater is "|" and the data is both text and numbers.

I need extract Data5, Data6, Data7 out of the line and paste into
cells A1,A2,A3 or where ever I need them. But I am hoping to get them
into a format that I can paste values from the clipboard.

I am having trouble parsing the data line extracting the correct lines
using string commands.
Is there an easier way to get this info?


Public Sub ImportTextFile()

Dim WholeLine As String
Dim WholeLineArray(7) As String

Application.ScreenUpdating = False

FName = "Y:\2007 Project Files\Sample Database.txt"
Open FName For Input Access Read As #1
sepcount = 0
While Not EOF(1)
Line Input #1, WholeLine
If Left(WholeLine, 8) = "27125001" Then
For I = 1 To Len(WholeLine)
C$ = Mid(WholeLine, I, 1)
If C$ = "|" Then
sepcount = sepcount + 1
WholeLineArray(sepcount) = Mid(WholeLine, sepcount, I - 1)
MsgBox WholeLineArray(sepcount)
End If
Next I
GoTo EndMacro
End If
Wend

EndMacro:
Application.ScreenUpdating = True
Close #1

End Sub

On 21 Apr 2007 11:01:10 -0700, gtslabs wrote:

I am having trouble parsing the data line extracting the correct lines
using string commands.
Is there an easier way to get this info?

The Split function should do what you want:

==================================
Sub ParseLine()
Const sTest As String = "Data1|Data2|Data3|Data4|Data5|Data6|Data7|Dat a8"
Dim arrData As Variant
Dim i As Long

arrData = Split(sTest, "|")

For i = 5 To 7
Debug.Print i, arrData(i - 1)
Next i

End Sub
================================
--ron