Save Text to File
 

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

Save Text to File
 

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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194015

http://www.thecodecage.com/forumz

Save Text to File
 

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

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

--

Dave Peterson

Save Text to File
 

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


"joel" wrote:


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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194015

http://www.thecodecage.com/forumz

.

Save Text to File
 

Hi Dave,
testme2 Works Perfectly! Thanks!

"Dave Peterson" wrote:

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

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

--

Dave Peterson
.