I have a number of .TXT files that are output data. Each file is formatted as 1 long string of characters. I have sourced out some code to add CR's every 100 characters. I will need to modify the code for each file because the spec file for each is different (some are 513 characters long, others are 96 characters long).

The code I have found is below. The problem is, I do not know how to utilize it in this situation. Can someone provide a step-by-step to use this code using Access?

Dim sr As New StreamReader(...)
Dim sb As New StringBuilder(sr.ReadToEnd())
Dim currentIndex As Int32 = 100

While currentIndex < sb.Length
sb.Insert(currentIndex, vbCrLf)
currentIndex += 102 '100 characters + vbCrLf.Length (2)
End While

I have a number of .TXT files that are output data. Each file is formatted as 1 long string of characters. I have sourced out some code to add CR's every 100 characters. I will need to modify the code for each file because the spec file for each is different (some are 513 characters long, others are 96 characters long).

The code I have found is below. The problem is, I do not know how to utilize it in this situation. Can someone provide a step-by-step to use this code using Access?

Dim sr As New StreamReader(...)
Dim sb As New StringBuilder(sr.ReadToEnd())
Dim currentIndex As Int32 = 100

While currentIndex < sb.Length
sb.Insert(currentIndex, vbCrLf)
currentIndex += 102 '100 characters + vbCrLf.Length (2)
End While


This is VB code not VBA code and in this instance I don't believe the StreamReader class can be used in VBA. Look at FileSystemObjects in the VBA world.

Can anyone provide a VBA version of this code?

probably easiest way is to read in the whole file to a string, then remove every crlf, and finally replace the crlfs at appropriate points.

this last bit is easily the trickiest as you want the new crlfs after the last space before the max line length. (i think)

it sounds interesting, so if you don't get it done i'll have a play over the weekend

Alternatively if you display the data in a text box, it will wrap anyway, so do yuo really need the crlf's putting back in?

Regular Expressions?Function GetStringWithCrLf(ByVal v_InString As String, _
ByVal v_NumDec As Long) As String

Dim re As Object

Set re = CreateObject("VBScript.RegExp")
With re
.Global = True
.Pattern = "((.|\n){" & CStr(v_NumDec) & "})"
GetStringWithCrLf = .Replace(v_InString, "$1" & vbCrLf)
End With

End FunctionPass it the string and the number of characters you wish between CRLF, and it should return a string according to spek.