Retreve Text box (Formatted)

[Middlemarch]

The text in my text box may be a long line of text which Access wordwraps.
How can I find the wordwrap positions?
I want to replicate the text in another app... but of course it becomes the initial long line of text again .



Thanks for any help/info.

[jojowhite]

Access automatically handles the wordwrap.
the simplest one i can think is to create Exact dimension
of the Original textbox that wrap to the other app.

or if you are VBA savvy, you can compute the TextWidth and TextHeight
of the Text and compare it to the TextWidth and TextHeight of
the Textbox.

[Middlemarch]

Hi Jo, the destination (for data backup) is an Excel Comment. I found it also can do wordwrap
so playing around with that. It seems do-able but ChatGPT has given me a line of Excel code that errors!

[orange]

I have found, and other posts have experienced, that Chatgpt does not produce error free vba. Often getting/returning/suggesting functions or constructs that do not exist in Access. These are"hallucinations" (made up constructs).

[Micron]

If you need to control it you'll have to do it manually (insert your own wrap points) or with code. If code, you'll have to use a fixed width font because an "i" is not as wide as a "W" and you can't have that.

[moke123]

I have found, and other posts have experienced, that Chatgpt does not produce error free vba. Often getting/returning/suggesting functions or constructs that do not exist in Access. These are"hallucinations" (made up constructs).

I would venture a guess that chatgpt doesn't know the difference and trains on whatever it finds including bad code.

Following what Micron said, I have this code which limits the length of long strings

Function LoopString(strIN As String, strLen As Integer) As String
   
    Dim  K As Variant, strOut As String
    Dim strA As String, strB As String, strLeftOver As String
    
    Dim col As New Collection
    
    Do While InStr(strIN, "  ") > 0
        strIN = Replace(strIN, "  ", " ")
    Loop


    Do While InStr(strIN, vbCrLf) > 0
        strIN = Replace(strIN, vbCrLf, " ")
    Loop
    
    strLeftOver = strIN
    
    Do Until Len(strLeftOver) = 0


        strA = Mid(strLeftOver, 1, strLen)


        If Len(strLeftOver) < strLen Then
        
            col.Add strLeftOver
            strLeftOver = Right(strLeftOver, Len(strLeftOver) - Len(strLeftOver))


        Else


            strB = Mid(strA, 1, InStrRev(strA, " "))
            col.Add strB


            strLeftOver = Right(strLeftOver, Len(strLeftOver) - Len(strB))


        End If

         Loop

    For Each K In col
         'Debug.Print K
        strOut = strOut & K & vbNewLine
    Next
 
    LoopString = strOut
    
End Function

?LoopString("Hope you are doing well. I have a pdf/word form which has description text to be entered into three lines therefore I want to split the description string into multiple lines of characters say 40 chrs.",40)

Hope you are doing well. I have a 
pdf/word form which has description 
text to be entered into three lines 
therefore I want to split the 
description string into multiple lines 
of characters say 40 chrs.

?LoopString("Hope you are doing well. I have a pdf/word form which has description text to be entered into three lines therefore I want to split the description string into multiple lines of characters say 40 chrs.",75)

Hope you are doing well. I have a pdf/word form which has description text 
to be entered into three lines therefore I want to split the description 
string into multiple lines of characters say 40 chrs.

[Middlemarch]

That coulg be very, very useful. Thanks @moke123 will give it a go.

[Middlemarch]

It worked VERY nicely. Solved a big problem.
If no space chars in strIN would if instr(strIN," ") = 0 or if instr(Left(strIN,strlen), " ")= 0 be the best trap?Or maybe doesn't matter.

[moke123]

It worked VERY nicely. Solved a big problem.
If no space chars in strIN would if instr(strIN," ") = 0 or if instr(Left(strIN,strlen), " ")= 0 be the best trap?Or maybe doesn't matter.

If no spaces it would be a problem. (Unless the string is less than the desired line length)
It's purpose is really a single paragraph sentence.

What the code does is replace any double spaces with a single space and replaces any vbcrlf with single spaces.
Then it loops through the string getting as close to the desired line length without splitting a word in half. It uses the space to separate whole words.
As the length of each line is determined it adds that piece to a collection along with a vbCrLf and removes it from the string and the loop continues.
Lastly it loops through the collection concatenating the new string as the functions result.

[Middlemarch]

Do you have a preference for how a No Space condition is handled?
Its ideal for what I'm doing. No spaces should never happen, but it if does it's an endless loop.

[orange]

Do you want to simply report that this input string does not have a space or carriage return?
OR
Do you want to check the length of such an input string (no space or carriage return) and
IF
The length is less/equal your desired length, simply output the string.
Else
If the length is greater than your desired length, then cut the string at your desired length, and output the string fragment.
Repeat with remainder of input string until the entire string has been processed.

Update for consideration:

Here is a small revision to Moke's LoopString code to allow for a long text string with or without a space character.

' ----------------------------------------------------------------
' Procedure Name: LoopString
' Purpose: To shorten a long string into parts of <= strLen.
'          Maintain punctuation and no splitting of words
' Revision: Allow a long text string with/without space character(s)
'
'Moke's comment from https://www.accessforums.net/showthread.php?t=90689&p=531637#post531637
'
'What the code does is replace any double spaces with a single space and replaces any vbcrlf with single spaces.
'Then it loops through the string getting as close to the desired line length without splitting a word in half.
'It uses the space to separate whole words.
'As the length of each line is determined it adds that piece to a collection along with a vbCrLf and
'removes it from the string and the loop continues.
'Lastly it loops through the collection concatenating the new string as the functions result.
'
' Procedure Kind: Function
' Procedure Access: Public
' Parameter strIN (String): Long input string
' Parameter strLen (Integer): Max length to output lines
' Return Type: String
' Author: Moke
' Revision: Jack
' Date: 07-Mar-25
' From Moke  https://www.accessforums.net/showthread.php?t=90689&p=531630#post531630
' ----------------------------------------------------------------
Function LoopString(strIN As String, strLen As Integer) As String
   
    Dim K As Variant, strOut As String
    Dim strA As String, strB As String, strLeftOver As String
    Dim col As New Collection
    ' replace double spaces to single space
    Do While InStr(strIN, "  ") > 0
        strIN = Replace(strIN, "  ", " ")
    Loop
    ' replace carriage returns to single space
    Do While InStr(strIN, vbCrLf) > 0
        strIN = Replace(strIN, vbCrLf, " ")
    Loop
    'revised string to adjust/parse
    strLeftOver = strIN
    
    ' Parse the revised string and add the line segments to the collection col
    Do Until Len(strLeftOver) = 0
        strA = Mid(strLeftOver, 1, strLen)
        
 ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 ' 7/Mar/25 Revison to allow long text with/without a space character
 '          If space char exists, split at the space char
 ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        If InStr(strA, " ") = 0 Then
            'process nonspace string
            'add string fragment to col
            col.Add strA
            strLeftOver = Mid(strLeftOver, strLen + 1)
        Else
        
            If Len(strLeftOver) < strLen Then
                col.Add strLeftOver
                strLeftOver = Right(strLeftOver, Len(strLeftOver) - Len(strLeftOver))
            Else
                strB = Mid(strA, 1, InStrRev(strA, " "))
                col.Add strB
                strLeftOver = Right(strLeftOver, Len(strLeftOver) - Len(strB))
            End If
        End If
    Loop
    
    ' Process the lines in the collection col

    For Each K In col
        'Debug.Print K
        strOut = strOut & K & vbNewLine
    Next
 
    LoopString = strOut
    Debug.Print strOut 'for testing
End Function

Sample test/result:

With a space char

?loopstring("abcdefghijklmnopqr stuvwxyz",11)
abcdefghijk
lmnopqr 
stuvwxyz

?loopstring("abcdef ghijklmnopqrstuvwxyz",11)
abcdef 
ghijklmnopq
rstuvwxyz

Without a space char

?loopstring("abcdefghijklmnopqrstuvwxyz",9)
abcdefghi
jklmnopqr
stuvwxyz

With multiple spaces(as per original)

?loopstring("If the length   is greater than    your desired length, then cut the string.",20)
If the length is 
greater than your 
desired length, 
then cut the 
string.

[moke123]

Thanks Orange. I didn't have a chance to get back to this.