looping through comma delimited text

[DZ]

Hi

I need to do something with a comma delimited block of text.

.....like
text , text, text, etc

Can someone help me write a loop to loop through each block of text between
commas?

Thanks alot for any help

 

[Rick Rothstein \(MVP - VB\)]

Consider this...

Dim X As Long
Dim BlockOfText As String
Dim Fields() As String
BlockOfText = "text1, text2, text3, etc."
' The next statement assumes a comma followed
' by a space, as shown, is the delimiting the text
Fields = Split(BlockOfText, ", ")
' The zero-based Fields array now holds
' each text sub-string in its elements...
For X = 0 To UBound(Fields)
Debug.Print Fields(X)
Next

Rick

 

[Joel]

Here is some code you might use

Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const Delimiter = ","
Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"
ChDir (Folder)

FName = Application.GetOpenFilename("CSV (*.csv),*.csv")


RowCount = LastRow + 1
If FName <> "" Then
'open files
Set fread = fsread.GetFile(FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine <> ""
DelimiterPosition = InStr(InputLine, Delimiter)
If DelimiterPosition > 0 Then
Data = Trim(Left(InputLine, DelimiterPosition - 1))
InputLine = Mid(InputLine, DelimiterPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close
End If
End Sub