String too long

Question

Hi,

I've got a few lines of code which find some placeholders on a Word document i.e. <<name>> and then it replaces that placeholder with a cell within Excel.

The issue is that the string length can be quite large and bigger than the 256-character max length.

I use this sub to action the replacement:

Sub ReplaceText(ByRef wdoc As Object, ByVal placeholder As String, ByVal replacement As String, ByVal count As Long)

' This is the code to be recalled for each replacement throughout thedoc creators 

Dim i As Long 

For i = 1 To count 

    With wdoc.Content.Find 

        .Text = placeholder 

        .replacement.Text = replacement 

        .Execute Replace:=2 

    End With 

Next i 

End Sub

I've underlined the line which gets highlighted in the DEBUG. Is there a work around so that I can make the replacement work for longer texts.

The search and replacement in the actual sub is below:

ReplaceText wdoc, "<<description>>", Sheet22.Cells(r, 6).Text, 2

This is the only line really that will keep popping up this error, but it would be ideal if we could get it to work for all the replacements just in case. Hope that helps.

Thanks in advance for any help.

Answer 1

I am not senior on VBA code.

You can refer to this article: Office VBA support and feedback | Microsoft Docs to go to Newest 'vba' Questions - Stack Overflow which is specail channel for handing VBA programming issue.

Thank you for your understanding.

Answer 2

The file holds personal data so i can't send a test file sorry. The code is finding a cell in excel, which in some cases has more than 255 characters in it. What i need, is for all the text from that cell to replace the placeholder. Currently, it debugs the code if it is over. Or in the example i just gave you, it just enters the christ 200 characters and nothing else.

Answer 3

Could you upload a test file? Then we may test macro.

You may upload file in private message.

Answer 4

My place holder is <<description>> do i just put several of those next to each other to keep replacing the placeholder?

I tried your workaround as it stands with 1 placeholder, and it adds 1 chunk... it cuts out 200 characters, and doesnt add any more...

Answer 5

One workaround you can try is to split the replacement text into smaller chunks and replace each chunk separately.

================================

Sub ReplaceText(ByRef wdoc As Object, ByVal placeholder As String, ByVal replacement As String, ByVal count As Long)

' This is the code to be recalled for each replacement throughout the doc creators

Dim i As Long

Dim chunkSize As Long

Dim numChunks As Long

Dim j As Long

Dim chunk As String

chunkSize = 200 ' set the chunk size to a value that works for your data

numChunks = Len(replacement) \ chunkSize + 1 ' calculate the number of chunks needed

For i = 1 To count

    For j = 1 To numChunks

        chunk = Mid(replacement, (j - 1) \* chunkSize + 1, chunkSize) ' get the current chunk

        With wdoc.Content.Find

            .Text = placeholder

            .replacement.Text = chunk

            .Execute Replace:=2

        End With

    Next j

Next i

End Sub

================================

This code splits the replacement text into chunks of 200 characters (you can adjust the chunk size as needed), and replaces each chunk separately. This should allow you to replace longer strings without hitting the maximum limit.