Results 1 to 10 of 10
  1. #1
    DMT Dave is online now VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Line size

    Hi Guys, is there a method of counting the length of a sentence including spaces then if the text is too long, add a comma and create a new line



    So if a client adds to an additional info field on our website, this is transferred to an excel file (job Sheet)

    If the text is too long, it runs over the outlined border

    So for example, if the character length including spaces are 25 then the 26th space or word runs over the border, I then want to add a comma and move to next line and same again etc...

    Already outputs >> .Worksheets(1).Cells(30, 1) = ClientNote

    example >> .Worksheets(1).Cells(31, 1) = ClientNote (26 to 49 Characters)

    example >> .Worksheets(1).Cells(32, 1) = ClientNote (50 to 75 Characters)

    Not necessarily 25 characters, see snip below

    Hoping this makes sense ?
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Using a loop and some VBA you need to go down the route of

    Using the Len() function will return the total length of a string.
    If it's over your max length the find the first space in the string before that using the Instr function.
    Add a comma and a Carriage return.
    From that position rinse and repeat.

    If you really need the next cell to be populated you'll need to add each line content to a temporary store, either a recordset or an array?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is anyone looking at the file after it is populated? If so, they could simply select the cell(s) and click the Wrap Text button on the ribbon in Excel?
    EDIT - Come to think of it, WrapText is also a property of the Range and CellFormatObject objects. Perhaps if you are using the Range object in code you could simply set this property to True. Just guessing here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by Micron View Post
    Is anyone looking at the file after it is populated? If so, they could simply select the cell(s) and click the Wrap Text button on the ribbon in Excel?
    I did think of that but thought it might be too simple...
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    DMT Dave is online now VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Guys, yes, it is our staff that reads after printed, i will have to check what wrap text is as i have never used that as an option before, i am unsure if i was trying to go the hard around it

    A Client can write 100 words in ClientNote field so i guess i was going to try and count the characters including spaces, ie: in the snip in post 1 and break it after the word (text)
    Cells 31, 1 would say "is too long to fit 1 page wide by 1 page tall (if this line didn't exceed a char count then great, if it did exceed a char count then do another break onto Cells 32, 1

    So i am guessing is it something like

    Dim iWords as Integer
    iWords = me.ClientNote
    Is SelSart an option from here or InStr ????
    Select Case iWords
    Case 25 (if 25 is the max on one line)
    Split Function is this possible ?

    I am unsure how to go about it correctly!!!

    Thanks again though guy's

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Personally I think you're wasting your time with a code solution as it isn't as simple as you seem to think.

    First, your line length depends on the column width, thus even if the sheet is a template it's likely that anyone could fudge with the column widths. What happens to your wrapped text now? Messed up.

    Second, you will have a max character value (e.g. 30) but you want to break the line between words, not characters, which would be far easier. That means if the code has figured out that you're in the middle of a word when you hit 30 it has to know how far back to go to find the end of the last complete word that it found.

    Could be done I suppose but as I mentioned, there is a button in the Excel ribbon for this. At worst, I might try setting the wrap property dynamically from Access, but I imagine there is also a possibility of setting that property on the entire notes column (again, just using the ribbon) and using a template. That way if anyone fudges the column width Excel should automatically adjust the wrapping.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I'm with Micron

    the other problem you have by using vba to break the text into rows - the spreadsheet will only show the first row per your example - so you will still need to go into excel after to adjust the row height and column width for multiple lines of text.

    Further tip - excel by default sets the text position to the bottom of the cell, so in wrapped text or your method the text 'scrolls up' for the number of rows of text. The tip is to set the text position to the top of the cell - that way single row cells will line up with multiple row cells in a more readable way.

    i.e. which do your prefer?

    colA colB ColC
    val1
    val2 val4
    val3 val5 val6
    or

    colA colB ColC
    val1 val4 val6
    val2 val5
    val3

  8. #8
    DMT Dave is online now VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thanks guys, will play around with wrapping text, thanks again

  9. #9
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Dave,

    If you want to have the control of the lines length independent of the Wraptext property of a range, here you are:
    Code:
    Function Wrapped(ByVal strText As String, intChars As Integer) As String
        'Split the strText in lines with length <= intChars
        Dim strRet As String
        Dim strLf As String
        Dim intPos As Integer
    
        If intChars > 0 Then
            If Len(strText) Then
                While intChars < Len(strText)
                    strLf = vbLf
                    intPos = InStrRev(strText, " ", intChars)
                    If intPos = 0 Then
                        intPos = intChars
                        strLf = "-" & strLf 'Brake word
                    End If
                    strRet = strRet & Trim(Left(strText, intPos)) & strLf
                    strText = Mid(strText, intPos + 1)
                Wend
                strRet = strRet & strText
            End If
            Wrapped = strRet
        End If
    End Function
    In Excel, you may use this function as seems below:
    Code:
    Sub WrapClientNote(intChars As Integer, Optional MultiRow As Boolean)
        'Split the text of a cell in lines
        Dim x As Variant
        Dim r As Range
    
        Set r = Worksheets(1).Range("A30")
        If Len(r) Then
            x = Wrapped(r, intChars)
            If MultiRow Then
                'Lines in rows
                x = Split(x, vbLf)
                r.Offset(2).Resize(UBound(x) + 1) = Application.WorksheetFunction.Transpose(x)
            Else
                'All wrapped text in one cell
                r.Offset(2) = x
            End If
        End If
    End Sub
    The procedure above, could be a method of a template workbook.

    Make a test:
    Code:
    WrapClientNote 50
    Cheers,
    John

  10. #10
    DMT Dave is online now VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Wow thank you John, will have a play around soon as i get chance but great thank you

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 10-21-2018, 05:49 PM
  2. Replies: 4
    Last Post: 08-22-2017, 07:14 PM
  3. Replies: 4
    Last Post: 02-10-2015, 01:07 AM
  4. Replies: 10
    Last Post: 06-10-2014, 09:03 AM
  5. Replies: 2
    Last Post: 03-20-2013, 04:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums