Save sht as text file - problem
 

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

Save sht as text file - problem
 

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

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Steph wrote:
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