Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you save an excel file to be read as IBM-type text file ? | Excel Worksheet Functions | |||
Save CSV file as Pipe delimited text file | Excel Discussion (Misc queries) | |||
How can I save a file as a comma-delimited text file in Excel? | Excel Discussion (Misc queries) | |||
Excel VBA - open text file, replace text, save file? | Excel Programming | |||
Save As - Multiple Sheets fails to save as text file | Excel Programming |