Exporting Text File

  • Thread starter Thread starter Ray Ray
  • Start date Start date
R

Ray Ray

Hello all

I am writing an Excel macro for exporting worksheet data into text file.
However, both statements "PRINT #" and "WRITE #" exports text in delimited
format (i.e. Chr(13) + Chr(10) added at the end each time these statements
are used). How can I get rid of the Chr(13) + Chr(10) codes?

Thanks in advance.

Raymond Lee
 
You could do something like:

Sub testme01()

Dim iRow As Long
Dim iCol As Long

Open "textfile.txt" For Output As #1

With ActiveSheet
For iRow = 1 To 10
For iCol = 1 To 5
Print #1, .Cells(iRow, iCol).Value; ",";
Next iCol
Next iRow
End With
Close 1

End Sub

And all the output data is one line.

But maybe you were looking for something like:

Option Explicit
Sub testme02()

Dim iRow As Long
Dim iCol As Long
Dim myStr As String

Open "textfile.txt" For Output As #1
With ActiveSheet
For iRow = 1 To 10
myStr = ""
For iCol = 1 To 5
myStr = myStr & "," & .Cells(iRow, iCol).Value
Next iCol
myStr = Right(myStr, Len(myStr) - 1)
Print #1, myStr
Next iRow
End With

Close 1

End Sub

This'll put each row on a separate line.

You may want to look at how Chip Pearson implemented a version:
http://www.cpearson.com/excel/imptext.htm

And Earl Kiosterud's Text Write program is available at:

It's available at Tushar Mehta's site:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

Earl has a bunch of options (including, IIRC, wrapping each cell in double
quotes)

You may find some of the ideas in each useful.
 
Back
Top