I am reading lines of a text file, which has values in many columns, and
dividing these into their elements by writing them one by one in a cell in a
worksheet and using texttocolumns with space as the delimiter and treating
consecutive delimiters as one. I don't need the values in the worksheet, and
once I've extracted the particular elements I need I write these to another
text file.

This seems to take a while so I wondered if there was a quicker way of doing
it in code, without using a worksheet. There is the split function, but I
can't find a way of getting it to treat consecutive spaces as a single
delimiter. Is there a way?

I am using Excel 2000.

Grateful for any ideas.

Sounds like a "table" created by using spaces in a plain text document. If
that's the case, I have a macro I use in Word that allows me to select the
"plain text table" and make it into a Word table. This can then be copied
directly in Excel. Would this help?

Ed

"simonc" wrote in message
...
I am reading lines of a text file, which has values in many columns, and
dividing these into their elements by writing them one by one in a cell in
a
worksheet and using texttocolumns with space as the delimiter and treating
consecutive delimiters as one. I don't need the values in the worksheet,
and
once I've extracted the particular elements I need I write these to
another
text file.

This seems to take a while so I wondered if there was a quicker way of
doing
it in code, without using a worksheet. There is the split function, but I
can't find a way of getting it to treat consecutive spaces as a single
delimiter. Is there a way?

I am using Excel 2000.

Grateful for any ideas.

Siman,

As long as they are really spaces:

Dim myString As String
Dim blah As Variant
Dim i As Integer

myString = "hello there"
blah = Split(Application.WorksheetFunction.Trim(myString) , " ")
For i = LBound(blah) To UBound(blah)
MsgBox blah(i)
Next i

HTH,
Bernie
MS Excel MVP


"simonc" wrote in message
...
I am reading lines of a text file, which has values in many columns, and
dividing these into their elements by writing them one by one in a cell in a
worksheet and using texttocolumns with space as the delimiter and treating
consecutive delimiters as one. I don't need the values in the worksheet, and
once I've extracted the particular elements I need I write these to another
text file.

This seems to take a while so I wondered if there was a quicker way of doing
it in code, without using a worksheet. There is the split function, but I
can't find a way of getting it to treat consecutive spaces as a single
delimiter. Is there a way?

I am using Excel 2000.

Grateful for any ideas.

Hi
i don't know this would meet your demand and don't think a quicker way
of doing.
assume a original file name is hoge.txt, then this would make a file
named as hoge_temp.txt in the same directory as the original file.

Sub parsefile2()
Dim rec
Dim filename As String, ftmp As String
Dim Fin As Integer, Fout As Integer
Dim WorkResult As String, tmp As String
Dim i As Long

On Error GoTo ErrorCheck
filename = Application.GetOpenFilename(FileFilter:="All File
(*.*),*")
If filename = "False" Then
Exit Sub
End If

pos = InStrRev(filename, ".")
If pos 0 Then
ftmp = Left(filename, pos - 1) & "_temp" & Mid(filename, pos)
Else
ftmp = filename & "_temp"
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Fin = FreeFile()
Open filename For Input As #Fin
Fout = FreeFile()
Open ftmp For Output As #Fout
Application.ScreenUpdating = False
Do While Not EOF(Fin)
Line Input #Fin, WorkResult
tmp = ""
rec = Split(WorkResult, Space(1))
For i = LBound(rec) To UBound(rec)
If rec(i) < "" Then
tmp = tmp & rec(i) & Space(1)
End If
Next
tmp = Trim(tmp) & Chr(13) & Chr(10)
Print #Fout, tmp;
Loop
Application.StatusBar = False
Application.EnableEvents = True
Application.ScreenUpdating = True
Close #Fin
Close #Fout
Exit Sub
ErrorCheck:
Application.StatusBar = False
Application.EnableEvents = True
Application.ScreenUpdating = True
Close #Fin
Close #Fout
MsgBox "An error occured in the code."
End Sub

keizi

"simonc" wrote in message
...
I am reading lines of a text file, which has values in many columns,
and
dividing these into their elements by writing them one by one in a
cell in a
worksheet and using texttocolumns with space as the delimiter and
treating
consecutive delimiters as one. I don't need the values in the
worksheet, and
once I've extracted the particular elements I need I write these to
another
text file.

This seems to take a while so I wondered if there was a quicker way of
doing
it in code, without using a worksheet. There is the split function,
but I
can't find a way of getting it to treat consecutive spaces as a single
delimiter. Is there a way?

I am using Excel 2000.

Grateful for any ideas.

I'm very sorry. This macro doesn't work correctly. Ignore my post.

"kounoike" wrote in message
...
Hi
i don't know this would meet your demand and don't think a quicker way
of doing.
assume a original file name is hoge.txt, then this would make a file
named as hoge_temp.txt in the same directory as the original file.

Sub parsefile2()
Dim rec
Dim filename As String, ftmp As String
Dim Fin As Integer, Fout As Integer
Dim WorkResult As String, tmp As String
Dim i As Long

On Error GoTo ErrorCheck
filename = Application.GetOpenFilename(FileFilter:="All File
(*.*),*")
If filename = "False" Then
Exit Sub
End If

snip