PDA

View Full Version : Solved: Help writing excel spradsheet to txt



SilverSN95
12-18-2009, 11:15 AM
Hello,
I am writing a macro that I need to write the content from one excel file to a newly created txt file. I can write lines to the text file, but the part I need help with might end up only being a line or two of code.
Lets say I have 3 columns, Name, Age, and DOB with n number of rows in each.
What is an easy way to write each row to the text file so it would look like :
Name1, Age1, DOB1
Name2, Age2, DOB2
etc.

So something like

For numRows = 0 to n
For each col
String = String + col.Range(currentRow) + ","
objTextFile.WriteLine (String)
Next numRows


Thanks!

p45cal
12-18-2009, 12:21 PM
For Each rw In Selection.Rows 'or:
'For Each rw In Range("A1:C10")
mystring = rw.Cells(1) & "," & rw.Cells(2) & "," & rw.Cells(3)
'write mystring
Next rw

or

For Each rw In Selection.Rows 'or:
'For Each rw In Range("A1:C10")
mystring = Join(Application.Index(rw.Value, 1, 0), ",")
'write mystring
Next rw or more in line with your idea:
For Each rw In Selection.Rows 'or:
'For Each rw In Range("A1:C10")
mystring = ""
For Each cll In rw.Cells
mystring = mystring & cll.Value & ","
Next cll
mystring = Left(mystring, Len(mystring) - 1)
'write mystring
Next rw

Saladsamurai
12-18-2009, 12:34 PM
Quick and dirty, but could be generalized a little:

Sub WriteToText()

Dim i As Long
Dim MyPath As String, FullPath As String
Dim nRow As Long

MyPath = ActiveWorkbook.Path
FullPath = MyPath & "\MyTextFile.txt"
nRow = 6

Open FullPath For Output As #1
With Sheet1
For i = 1 To nRow
Print #1, .Cells(i + 1, 1) & Chr(44) & .Cells(i + 1, 2) & Chr(44) & .Cells(i + 1, 3)
Next i
End With
Close #1

End Sub

SilverSN95
12-22-2009, 11:59 AM
Thanks for the replies...P45cal I am basing my loop on the last example you gave but have another simple question.
The number of rows for the loop to run through will not always be constant and will not be from a selection, so I need to loop through a variable rw= number of rows.
To get this, I could just loop through the rows of a column until I hit an empty cell and then save that to rw to loop over... If this sounds fine I'll just do this, but I wanted to ask if there is a better way (non-looping?) to set this rw variable.

Thanks again.

p45cal
12-22-2009, 12:21 PM
Dim rw As Range
rowNo = 2 'start on second row
Do
Set rw = Cells(rowNo, "A").Resize(, 3)
mystring = ""
For Each cll In rw.Cells
mystring = mystring & cll.Value & ","
Next cll
mystring = Left(mystring, Len(mystring) - 1)
'write mystring
rowNo = rowNo + 1
Loop Until Cells(rowNo, "A") = ""

SilverSN95
12-22-2009, 01:21 PM
Thanks p45 that works quite well for what I need.