CODEWITHSUNDEEP
excelvbams-word
heroxhere
heroxhere

Reputation: 1

Excel to Word Find and Replace - How to bypass 255 Character Limit

I have a table with the list of words to be replaced with on a word document and used this code below. It worked perfectly until recently I got an error saying "String Parameter Too Long" ie beyond 255 characters. Can someone please fix this for me?

My knowledge on coding/VBA is zero and I haven't put in the time to learn it as I only need to be on word processing.

The word document is name as doc within folder on the desktop. The excel sheet is named as book within folder on the desktop.

Thanks a lot.

Option Explicit

Public Sub WordFindAndReplace()
    Dim ws As Worksheet, msWord As Object, itm As Range

    Set ws = ActiveSheet
    Set msWord = CreateObject("Word.Application")

    With msWord
        .Visible = True
        .Documents.Open "/Users/NAME/Desktop/folder/doc.docx"
        .Activate

        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            For Each itm In ws.UsedRange.Columns("A").Cells

                .Text = itm.Value2                          'Find all strings in col A

                .Replacement.Text = itm.Offset(, 1).Value2  'Replacements from col B

                .MatchCase = False
                .MatchWholeWord = False

                .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
            Next
        End With
        .Quit SaveChanges:=True
    End With
End Sub

Upvotes: 0

Views: 1169

Answers (2)

macropod
macropod

Reputation: 13505

One way is to copy the replacement content to the clipboard. For example:

Public Sub WordFindAndReplace()
    Dim ws As Worksheet, msWord As Object, wdDoc As Object, itm As Range
    Set ws = ActiveSheet
    Set msWord = CreateObject("Word.Application")
    With msWord
        .Visible = True
        Set wdDoc = .Documents.Open("/Users/NAME/Desktop/folder/doc.docx")    
        With wdDoc.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .MatchCase = False
            .MatchWholeWord = False
            .Replacement.Text = "^c"
            For Each itm In ws.UsedRange.Columns("A").Cells
                itm.Offset(, 1).Copy  'Replacements from col B
                .Text = itm.Value2                          'Find all strings in col A
                .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
            Next
        End With
        .Quit SaveChanges:=True
    End With
End Sub

Upvotes: 1

heroxhere
heroxhere

Reputation: 1

I used Excel's Data -> Text to Column -> Fixed Width option to split the lengthy columns and then replace them with the same code I questioned with. But the best way would be to fix the code and go with copying the replacement content to the clipboard. as suggested by @macropod

Upvotes: 0

Related Questions