Comma delimited string in variable

[NNNNN]

Hi

In Excel I use vba to read a comma delimited line of text from a text file.

so my variable str would hold something like:

one,two,three,four etc

There is two things I wan to do

First I want to be able to put the contents of my variable in to individual cells e.g.
A1 B1
one two and so on

at the moment I can only dump the whole string in to one cell - I could do text to columns afterwards but I am sure there must be a better way.

The second thing I want to do is to delete the first line of text from the text file (i.e. the line I have read in to my variable) but only that line -the text file could contain many lines.

Any help on either or both problems would be greatly appreciated

Many thanks

 

[firefytr]

Hi there,

Your first question is rather easy. You can use the Split function (not available in 97 and prior) which will put your data into an array. You can then loop through that array to input your data into cells. Here is an example ...

Sub TestingSplit()
    Dim arrData() As String
    Dim strData As String
    Dim i As Long
    strData = "one,two,three,four,five,six,seven,eight,nine,ten"
    arrData = Split(strData, ",")
    For i = LBound(arrData) To UBound(arrData)
        Cells(1, i + 1).Value = arrData(i)
    Next i
End Sub

Now, the second question you asked highly depends on how you are controlling the text file with your automation. You would need to post your code so we could take a look at it.

HTH

-----------
Regards,
Zack Barresse

 

[NNNNN]

Thanks for the quick reply

Here it is


Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, fg
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("C:\test\myfile1.txt", ForReading,
TristateTRUE)

x = 1
Do While f.AtEndOfStream <> True
n = f.ReadLine
Range("A" & x).Select

'THis bit looks for the first blank row
' incase there is data in cell A1 etc
Do While ActiveCell.Value <> ""
x = x + 1
Range("A" & x).Select
Loop
' End checking for data in cell

ActiveCell.FormulaR1C1 = n


Loop
f.Close

MANY THANKS

 

[NNNNN]

The code I posted actually reads all lines one at a time

 

[firefytr]

This reads each line seperately..

Sub TextFileTest()
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Dim fs, f, x As Long
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile("C:\test\myfile1.txt", ForReading) ', TristateTRUE)
    x = 1
    Do While f.AtEndOfStream <> True
        x = x + 1
        Cells(Rows.Count, 2).End(xlUp).Offset(1).Value = f.ReadLine
    Loop
    f.Close
End Sub

You'll have to give me a little bit for the deleting of the text file (don't remember off the top of my head); I'll get back to you on that.

-----------
Regards,
Zack Barresse

 

[NNNNN]

thanks

 

[firefytr]

Okay, let me get this straight, you want basically a carbon copy of your text file but the new file must not have the first line in the original?

-----------
Regards,
Zack Barresse

 

[PHV]

Have you considered the TextToColumns method of the Range object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

Have you considered simply opening your comma delimited file in excel and deleting row 1 ???

Have a look at the Workbooks.OpenText method in help or simply record yourself going

File>Open and navigate to the text file. Set as delimited and choose comma - voila - there is your code

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question