Reading a text file line by line-How do I open the text file with VBA

[stressman]

I am looking to bring data into a excel file as a string one line at a time so I can evaluate the type of data on the line. When I have found the right data I can brake the string up into the right cells. The line might have the load case, data type or loads. How do I open the text file with VBA in Excel and read the file line by line into a string?

[Chip Pearson]

Try something like the following:

Dim S As String
Open "H:\test.txt" For Input As #1 ' change filename
Do Until EOF(1)
Line Input #1, S
Debug.Print S
Loop
Close #1


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"stressman"
<[email protected]> wrote in
message
news:[email protected]...
>
> I am looking to bring data into a excel file as a string one
> line at a
> time so I can evaluate the type of data on the line. When I
> have found
> the right data I can brake the string up into the right cells.
> The line
> might have the load case, data type or loads. How do I open
> the text
> file with VBA in Excel and read the file line by line into a
> string?
>
>
> --
> stressman
> ------------------------------------------------------------------------
> stressman's Profile:
> http://www.excelforum.com/member.php...o&userid=28139
> View this thread:
> http://www.excelforum.com/showthread...hreadid=476545
>

[Gary Keramidas]

i have used this and then modified it to only bring in the line i needed,
but this brings in every line of every .eml file in the folder
it will put it in consecutive lines in column A

Dim ColNdx As Integer
Dim WholeLine As String
Dim FileDir As Variant
Dim FilesInPath As String
Dim MyFiles() As String
Dim NumberOfFiles As Long
Sub import()
Sheets("sheet1").Range("A1").Select
ColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
sRow = RowNdx
FileDir = Environ("USERPROFILE") + "\Desktop\Cats\" ' change this
' determine # of files
FilesInPath = Dir(FileDir & "\*.eml") ' change this location
NumberOfFiles = 0
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

' perform import of email files
Do While FilesInPath <> ""
Open FileDir & FilesInPath For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine
Cells(RowNdx, ColNdx).Value = WholeLine
RowNdx = RowNdx + 1
Wend
Close #1
' after import, delete unwanted rows
' Range(Rows(sRow), Rows(sRow + 23)).Delete
' Range("A" & sRow).Offset(1, 0).Select
' sRow = sRow + 1
' RowNdx = RowNdx +1
NumberOfFiles = NumberOfFiles + 1

ReDim Preserve MyFiles(1 To NumberOfFiles)
MyFiles(NumberOfFiles) = FilesInPath
FilesInPath = Dir()
Loop
End Sub
--


Gary


"stressman" <[email protected]> wrote
in message news:[email protected]...
>
> I am looking to bring data into a excel file as a string one line at a
> time so I can evaluate the type of data on the line. When I have found
> the right data I can brake the string up into the right cells. The line
> might have the load case, data type or loads. How do I open the text
> file with VBA in Excel and read the file line by line into a string?
>
>
> --
> stressman
> ------------------------------------------------------------------------
> stressman's Profile:
> http://www.excelforum.com/member.php...o&userid=28139
> View this thread: http://www.excelforum.com/showthread...hreadid=476545
>

[stressman]

Thanks that 'Line Input' command done the trick.
I need to get a new book it did not have that command in it.