Save sht as text file - problem

[Steph]

Hi everyone. I have the below chunk of code that saves a single sheet in my
workbook as a text file. Row 1 of the excel file is very long (300+
characters). When the code runs, it truncates the contents of row 1 (don't
know how many characters - I just know all of the contents are not in the
text file). BUT, when I manually copy the contents of the excel sheet, and
past them into Notebook and save as a .txt file, all the contents of row 1
are there. Any idea why this is happening, and how I can fix it? Thanks!

Dim sh As Worksheet
Set sh = Worksheets("Sheet1")
sh.Copy
ActiveWorkbook.SaveAs Filename:="c:\Testfile.txt", FileFormat:=xlText

 

[Dick Kusleika]

Steph

That operation truncates cells at 255 characters. I think you'll need to
create your own text file in code. Here's a sub to get you started.

Sub SaveAsTextFile()

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 = Worksheets("Sheet1")

For Each rRow In sh.UsedRange.Rows
For Each rCell In rRow.Cells
sOutput = sOutput & rCell.Text & vbTab
Next rCell
sOutput = sOutput & vbNewLine
Next rRow

lFnum = FreeFile
sFname = "C:\Testfile.txt"

Open sFname For Output As lFnum

Print #lFnum, sOutput

Close lFnum

End Sub