string parameter too long error

[dean.rogers]

Hey all,

I am running into this error which is making things difficult when trying to find and replace.

The error is:
Run-time error '5854':
String parameter too long

I take it there is a character limit when using the find and replace and if there is a way around it, it would be GREAT!!

here is the code that i currently use to find/replace from excel to word:
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Set wrdApp = CreateObject("Word.Application")

wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open("C:Templates\Template1.docx")
Set wrdDocSelection = wrdApp.Selection

With wrdDocSelection.Find
.Text = "[company_name]"
.MatchWholeWord = False
.Replacement.Text = ActiveWorkbook.Sheets("Sheet2").Range("D3")
.Execute , , , , , , , , , , wdReplaceAll

Thanks for the help

 

[Firefly2012]

If you are trying to run this a second time within the same piece of code then you will need to initialise the cnt variable so that it starts at zero again - this can be achieved by inserting the following line in the code:

Dim strReplacement As String, strFragment As String
Dim cnt As Long
 
'rest of code
'...
 
'then:
 
 
With wrdDocSelection.Find
    .Text = "[company_name]"
    .MatchWholeWord = False
 
    strReplacement = ActiveWorkbook.Sheets("Sheet1").Range("D3").Value
    If Len(strReplacement) > 255 Then
        strFragment = Mid(strReplacement, cnt + 1, 230)
        strFragment = strFragment & "@@@@@@@@@@"
        cnt = cnt + 230
        .Replacement.Text = strFragment
        .Execute , , , , , , , , , , wdReplaceAll
        .Text = "@@@@@@@@@@"
        Do
           strFragment = Mid(strReplacement, cnt + 1, 230)
           cnt = cnt + 230
           If Len(strFragment) > 0 Then strFragment = strFragment & "@@@@@@@@@@"
           .Replacement.Text = strFragment
           .Execute , , , , , , , , , , wdReplaceAll
        Loop While Len(strFragment) > 0
        cnt = 0    
    Else
        .Replacement.Text = strReplacement
        .Execute , , , , , , , , , , wdReplaceAll
    End If
End With

Relevant amendment is in red.

 

[dean.rogers]

That worked perfectly, thanks firefly!!