Save as txt - extra carriage return
 

Hi everyone. The below code taskes a worksheet and saves it as a txt file.
There is a loop in there (sOutput = sOutput & vbNewLine) that adds a
carriage return. I am having a problem with my txt file in that there is 1
extra carriage return at the bottom that I can't get rid of, and it's
messing up my database. And ideas on how I can get rid of it, or maybe edit
the loop so it doesn't add a carriage return when the last line is written?
Thanks in advance!!


Sub Saveastxt()
Dim sh As Worksheet
Dim rRow As Range
Dim rCell As Range
Dim sOutput As String
Dim lFnum As Long
Dim sFname As String

Set sh = ActiveWorkbook.Worksheets("Sheet1")

For Each rRow In sh.Range("A1:A" & Range("A65536").End(xlUp).Row)
For Each rCell In rRow.Cells
sOutput = sOutput & rCell.Text
Next rCell
sOutput = sOutput & vbNewLine
Next rRow
'*******remove extra blank row at bottom of text file - doesn't remove
last carriage return!!
sOutput = Left(sOutput, Len(sOutput) - 2)

lFnum = FreeFile
sFname = "\\server\folder\file.txt"

Open sFname For Output As lFnum

Print #lFnum, sOutput

Close lFnum
ActiveWorkbook.Close False

End Sub

Save as txt - extra carriage return
 

Looks like it's just one of those loop issues. If you put the return-adding
line inside an IF/ELSE statement, you can check to see if the next, future
row has anything in it (using the Offset method). That way, if you're on the
last existing entry in column A, the following row will be null, and you can
avoid adding the carriage return.

Hfly

"Steph" wrote:

Hi everyone. The below code taskes a worksheet and saves it as a txt file.
There is a loop in there (sOutput = sOutput & vbNewLine) that adds a
carriage return. I am having a problem with my txt file in that there is 1
extra carriage return at the bottom that I can't get rid of, and it's
messing up my database. And ideas on how I can get rid of it, or maybe edit
the loop so it doesn't add a carriage return when the last line is written?
Thanks in advance!!


Sub Saveastxt()
Dim sh As Worksheet
Dim rRow As Range
Dim rCell As Range
Dim sOutput As String
Dim lFnum As Long
Dim sFname As String

Set sh = ActiveWorkbook.Worksheets("Sheet1")

For Each rRow In sh.Range("A1:A" & Range("A65536").End(xlUp).Row)
For Each rCell In rRow.Cells
sOutput = sOutput & rCell.Text
Next rCell
sOutput = sOutput & vbNewLine
Next rRow
'*******remove extra blank row at bottom of text file - doesn't remove
last carriage return!!
sOutput = Left(sOutput, Len(sOutput) - 2)

lFnum = FreeFile
sFname = "\\server\folder\file.txt"

Open sFname For Output As lFnum

Print #lFnum, sOutput

Close lFnum
ActiveWorkbook.Close False

End Sub

Save as txt - extra carriage return
 

For Each rRow In sh.Range("A1:A" & Range("A65536").End(xlUp).Row)

If sOutput<"" then
sOutput = sOutput & vbNewLine
end if

For Each rCell In rRow.Cells
sOutput = sOutput & rCell.Text
Next rCell

Next rRow

--
Tim Williams
Palo Alto, CA


"Steph" wrote in message
...
Hi everyone. The below code taskes a worksheet and saves it as a txt
file.
There is a loop in there (sOutput = sOutput & vbNewLine) that adds a
carriage return. I am having a problem with my txt file in that there is
1
extra carriage return at the bottom that I can't get rid of, and it's
messing up my database. And ideas on how I can get rid of it, or maybe
edit
the loop so it doesn't add a carriage return when the last line is
written?
Thanks in advance!!


Sub Saveastxt()
Dim sh As Worksheet
Dim rRow As Range
Dim rCell As Range
Dim sOutput As String
Dim lFnum As Long
Dim sFname As String

Set sh = ActiveWorkbook.Worksheets("Sheet1")

For Each rRow In sh.Range("A1:A" & Range("A65536").End(xlUp).Row)
For Each rCell In rRow.Cells
sOutput = sOutput & rCell.Text
Next rCell
sOutput = sOutput & vbNewLine
Next rRow
'*******remove extra blank row at bottom of text file - doesn't remove
last carriage return!!
sOutput = Left(sOutput, Len(sOutput) - 2)

lFnum = FreeFile
sFname = "\\server\folder\file.txt"

Open sFname For Output As lFnum

Print #lFnum, sOutput

Close lFnum
ActiveWorkbook.Close False

End Sub