Save Text to File

[MikeZz]

Hi,
I have a very long string that has Line Feeds (Char-10) already in it. It
could easily have close to a 1000 lines.

Is there a way to just save the string to a text file or do I have to parse
the string into lines and write each one individually?

I guess I should also ask what the max length of a string could be in Excel
2003 VBA but I can probably find that out with a google search.

Thanks for any advice
Mike Zz

 

[joel]

Strings in VBA can be up to 32,768 which is the maximum nuimber oi
character in a cell, although in a cell you can only see the 1st 1024.

 

[Dave Peterson]

Do you want those vblf's (alt-enters) converted to vbcrlf (the standard end of
line marker in DOS files)?

If yes:

Option Explicit
Sub testme()

Dim FSO As Object
Dim RegEx As Object

Dim myFile As Object
Dim myContents As String
Dim myOutFileName As String

myOutFileName = "C:\testout.txt"

myContents = ActiveCell.Value

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.IgnoreCase = False
.Pattern = vbLf
myContents = .Replace(myContents, vbCrLf)
End With

Set FSO = CreateObject("Scripting.FileSystemObject")
Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub

If you don't want vbcrlf, you can drop all that RegEx stuff:

Option Explicit
Sub testme2()

Dim FSO As Object

Dim myFile As Object
Dim myContents As String
Dim myOutFileName As String

myOutFileName = "C:\testout.txt"

myContents = ActiveCell.Value

Set FSO = CreateObject("Scripting.FileSystemObject")

Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub

 

[MikeZz]

Hi Joel,
Thanks for the response. My string will be strictly in VBA and not placed
in a cell.
I've just done a test and I don't think there is a limit to length.

I ran the following test and stopped macro before the end and L was > 128,000.
Thanks again

Sub testStrSize()

Dim strng As String
Dim L

strng = Empty
For i = 1 To 10000
strng = strng & "AAAAAAAAA" & i
L = Len(strng)
Next i


End Sub

 

[MikeZz]

Hi Dave,
testme2 Works Perfectly! Thanks!

Do you want those vblf's (alt-enters) converted to vbcrlf (the standard end of
line marker in DOS files)?

If yes:

Option Explicit
Sub testme()

Dim FSO As Object
Dim RegEx As Object

Dim myFile As Object
Dim myContents As String
Dim myOutFileName As String

myOutFileName = "C:\testout.txt"

myContents = ActiveCell.Value

Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
.Global = True
.IgnoreCase = False
.Pattern = vbLf
myContents = .Replace(myContents, vbCrLf)
End With

Set FSO = CreateObject("Scripting.FileSystemObject")
Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub

If you don't want vbcrlf, you can drop all that RegEx stuff:

Option Explicit
Sub testme2()

Dim FSO As Object

Dim myFile As Object
Dim myContents As String
Dim myOutFileName As String

myOutFileName = "C:\testout.txt"

myContents = ActiveCell.Value

Set FSO = CreateObject("Scripting.FileSystemObject")

Set myFile = FSO.CreateTextFile(myOutFileName)
myFile.Write myContents
myFile.Close

End Sub