Excel VBA string limit

[lostweekend]

Hi,

I'm trying to run a relatively simple excel to word text replace but several of my strings are over 255 characters. So far, none of the work arounds Ive attempted have panned out but I'm I dont work with VBA very frequently and am pretty clueless. Can a work around be accomplished here? My code is copied below.

'Sub TextReplace()
   
    Dim path As String
    Dim oCell  As Integer
    Dim from_text As String, to_text As String
    Dim WA As Object
   
'Set path to template
    path = "C:\Users\100156\Desktop\Tester.docx"
 
    Set WA = CreateObject("Word.Application")
    WA.Documents.Open (path)
    WA.Visible = True
 
    For oCell = 1 To 10
        from_text = Sheet1.Range("C" & oCell).Value
        to_text = Sheet1.Range("D" & oCell).Value
        With WA
            .Activate
            With .Selection.Find
              .ClearFormatting
              .Replacement.ClearFormatting
 
              .Text = from_text
              .Replacement.Text = to_text
              .Execute Replace:=wdReplaceAll
            End With
        End With
    Next

 

[frank_AL]

What version of Excel are you using? While the column width maximum is 255 the "Total number of characters that a cell can contain" has been 32,767 from at least Excel 2007 forward.

 

[lostweekend]

Looks like I'm using Excel 2013

 

[frank_AL]

lostweekend, posting solution if others are searching for a similar solution.

I found another MrExcel Forum question that provided the solution I have used which is to set up Bookmarks in the Word document where you want to replace text. Click here to see that question/solution.

Sub TestCode()


    Dim path As String
    Dim oCell  As Integer
    Dim BkMkName As String, to_text As String
    Dim WA As Object
    
'Set path to template- MUST BE CHANGED
    path = "C:\Users\100156\Desktop\Tester2.docx"
    Set WA = CreateObject("Word.Application")
    WA.Documents.Open (path)
    WA.Visible = True


    For oCell = 2 To 5
        BkMkName = Sheet1.Range("C" & CStr(oCell)).Value
        to_text = Sheet1.Range("D" & CStr(oCell)).Value & " "
        With WA
            .Activate
            If BkMkName = "Synopsis" Then
                .ActiveDocument.Bookmarks(BkMkName).Range = to_text & vbCrLf
            Else
                .ActiveDocument.Bookmarks(BkMkName).Range = to_text
            End If  '  BkMkName = Synopsis
        End With
    Next
    
End Sub