Copy Long Sheet2 contents an SaveAsText

[abmark]

I am trying to use the following macro to save the contents of Sheet2 to a text file.

The problem is that I have long text in Sheet2 that has more than 1095 characters.

Is there a way to change this macro in order to use it?

If I simply copy the whole Sheet2 with copy and paste it to textpad it is working fine, but if I copy it to a different workbook it seems also that it gets truncated at 1024, but in reality there is more contents after that.


Thanks in advance for any feedback,
Mark


Sub SaveAsText()
Dim lngRow As Long, lngLastRow As Long
Open "c:\temp\" & Range("A1").Value For Output As #1
With Worksheets("Sheet2")
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For lngRow = 1 To lngLastRow
Print #1, .Cells(lngRow, 1).Text
Next lngRow
End With
Close #1
MsgBox "Database stored under " & "c:\temp\" & Range("A1").Value
End Sub

 

[Cindy Ellis]

If your goal is simply to save the contents of sheet2 to a text file, you can do that pretty simply (with some "cons" noted below) by making Sheet2 the active sheet, then using File SaveAs, and choose one of the text options as the format. Something like the following:

    Sheets("Sheet2").Select
   SaveFileName = "c:\temp\" & Range("A1").Value
    ActiveWorkbook.SaveAs Filename:= _
        "" & SaveFileName & "", FileFormat:=xlTextMSDOS _
        , CreateBackup:=False
    ActiveWindow.Close
 MsgBox "Database stored under " & SaveFileName

Pros...you don't lose any characters from your cells.
Cons...
1. If there is data is columns other than "A" it will get saved along with the column A data.
2. You will get one prompt asking whether you want to save 'filename' (whatever your filename is), that needs to be answered "no"...The prompt isn't disabled by Application.EnableEvents = False.
3. If other sheets of the workbook have changes that need to be saved, you would need to save the workbook before running the macro, as it will only save the active sheet.

Hope this helps,
Cindy