I added a few lines to the macro to rename the sheets according to the
texts following "Some ID:"
Also added a few lines at the end to delete the last created sheet
(which is never used)

Hope this helps / Lars-Åke

Sub import_from_text_file()
Dim myWords
Filename = <<<<<<<< your filename goes here
Open Filename For Input As #1
copying = False
Set wb = Workbooks.Add()
Set ws = wb.Worksheets.Add
wsrow = 1
While Not EOF(1)
If InStr(readline, "Some ID:") = 1 Then
ws.Name = Mid(readline, Len("Some ID:") + 1)
End If
Line Input #1, readline
If InStr(1, readline, "Result") Then
copying = True
ElseIf InStr(1, readline, "-------") Then
copying = False
wsrow = 1
Set ws = wb.Worksheets.Add
Else
If copying Then
myWords = Split(readline, " ")
For i = 1 To UBound(myWords)
ws.Cells(wsrow, i) = myWords(i)
Next i
wsrow = wsrow + 1
Else
End If
End If
Wend
Close #1
alertsave = Application.DisplayAlerts
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = alertsave
End Sub

On Tue, 18 Aug 2009 08:49:01 -0700, Sheila
wrote:

That's brilliant. The macro worked, with small modification.
Thank you so much.

Now I would like to name the sheets with the ID information ( "xxxxx")
written after "Some ID:". It is there in the second row of the text file.

How do I incorporate this into the code?

Thanks a lot for your help.
Sheela

"Lars-Åke Aspelin" wrote:

On Mon, 17 Aug 2009 13:45:01 -0700, sheela
wrote:

How do I automate the process of importing data from a text file into excel?

The text file contains multiple types of results including other summary
information. I would like to import the each test results into each excel
tab/sheet.
I would like to implement a VBA code with the following criteria.

1) to search for a row with a single word: “Result” ( the search word
should be case sensitive)and start import the text from next row
2) Import each row of the text file into each row of the excel file.
3) The text to import is delimited by space character.
4) And stop importing once it encounters a row of “-----“ .
5) Then continue the search for next row with the text “Result” and import
the text starting from next row into another tab of the same sheet until it
hits a series of “----“
6) Continue this process until end of the text file

Is it possible to do this at all in excel? If not could you suggest another
program to do this?

My text file to import into excel looks something like this.

Date test was run: xxxxxxx
Some ID:xxxxx
Someother ID : xxxxxx
Someother text:xxxxxxxxx
Summary :
XXXXX
XXXXXXXXXXXX
XXXXXXXXXX
Result
1 White 2.141 Star 1.000
1 White 0.703 Star 1.000
2 Red 0.594 Star 1.000
2 White 0.734 Star 1.000
2 White 0.657 Star 1.000
3 Black 0.610 Star 1.000
------------------------------
Date test was run: xxxxxxx
Some ID:xxxxx
Someother ID : xxxxxx
Someother text:xxxxxxxxx
Summary :
XXXXX
XXXXXXXXXXXX
XXXXXXXXXX
Result
1 Triangle 2.141 AAA 1.000
2 Triangle 0.703 AAA 1.000
3 Round 0.594 ABC 1.000
3 Rectangle 0.734 DSA 1.000
3 Square 0.657 WQA 1.000
4 Round 0.610 RVG 1.000
--------------------------------------
And so on..


Try this macro:

Sub import_from_text_file()
Dim myWords
Set wb = Workbooks.Add()
Set ws = wb.Worksheets.Add
wsrow = 1
Open "example.txt" For Input As #1
copying = False
While Not EOF(1)
Line Input #1, readline
If InStr(1, readline, "Result") Then
copying = True
ElseIf InStr(1, readline, "-------") Then
copying = False
wsrow = 1
Set ws = wb.Worksheets.Add
Else
If copying Then
myWords = Split(readline, " ")
For i = 1 To UBound(myWords)
ws.Cells(wsrow, i) = myWords(i)
Next i
wsrow = wsrow + 1
End If
End If
Wend
Close #1
End Sub

Hope this helps / Lars-Åke