Save Individual Cells as Separate Text Files

I solved it myself. Firstly I had to use the following formula to overcome the 1024 character limit: =MID($B2,1025,1024) and incrementing the '1025' by 1024 across a few columns until I had captured all of the text. I found out the longest text using a MAX() and created enough columns to suit. Then I used the following code to create the text files (modified from Chip Pearson at http://www.cpearson.com/excel/ImpText.aspx):

Sub ExportToTextFile()

Dim FNum As Integer
Dim CellValue As String
Dim FName As String
Dim Counter As Integer

Application.ScreenUpdating = False
On Error GoTo EndMacro:

For Counter = 2 To 1487
Worksheets("Sheet1").Cells(Counter, 2).Select
FName = "C:\Documents and Settings\My Documents\MY Stuff\Text Files\" & Cells(Counter, 1).Text & ".txt"
FNum = FreeFile

CellValue = Cells(Counter, 2).Text & Cells(Counter, 3).Text & Cells(Counter, 4).Text & Cells(Counter, 5).Text

Open FName For Output Access Write As #FNum

Print #FNum, CellValue
Close #FNum

Next Counter

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub


I hope this helps someone in the future!

Neil.

Hi Neil

Always good when you solve something yourself - you'll certainly remember it better and thanks for taking the time to paste your solution to the board. One question though - what is this 1024 character limit you are talking about? Cells can hold (in all versions of Excel that I have ever dealt with) 32767 characters. String variables can hold 2 billion +, so I am surprised you had to chop up the contents of your cells.

Good question - I have no answer to this, but it may just be that when you pull the string the way I did, only the first 1024 characters are recognized? Just guessing...

Happy New Year!

Sorry, yes - you're using the Text property no the the Value property of the range object. This will limit you to 1024 characters.

That's interesting Richard...Presumably the Text property is "as displayed in the cell" and the cell is limited in what it can display...

Yep - something which I have only just managed to get into my head is that the Text property is read only too. Seems ridiculous I know, but it's one of those things that just doesn't "stick" in my brain so time after time I try and write to a range using Text - D'Oh!