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

 

[CharlesChuckieCharles]

what are
wrdApp.Selection
and
wrdDocSelection
??

 

[dean.rogers]

That is setting wrdDoc as the word.document as an object
Same goes for wrdApp. The .selection is allowing me to use the .find feature in the word app so I can execute a find and replace.

 

[Richard Schollar]

Hi Dean

What does D3 contain?

 

[dean.rogers]

D3 will be a value entered in manually which will be the company name

 

[dean.rogers]

The issue is when the .text = "[string_parameter_that_is_too_long]"
and the .Range("D4") is a 3-4 sentence paragraph, it returns the error 5854: String Parameter too long. I have researched online before and have read that there is a limit to the amount of characters when doing find/replace but there is a way around it. I just havent been able to find it yet. Not sure if anyone knows of a way.

 

[Firefly2012]

Through testing, it would appear that the maximum length of the replacement text is 255 characters. If you are exceeding this, then you may need to run multiple replaces. You could do it like this (this replaces your section starting "With wrdDocSelection.Find", I have also inserted a couple of new variable declarations (include these at the top of your module):

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
    Else
        .Replacement.Text = strReplacement
        .Execute , , , , , , , , , , wdReplaceAll
    End If
End With

 

[ZVI]

Try also this:

With wrdDoc
  .Content = Replace(.Content, "[company_name]", Sheets("Sheet2").Range("D3"))
End With

 

[dean.rogers]

So I tried your code ZVI and it worked however, when using it, it completely wiped out every bit of formatting in the template which completely kills it. Do you know any way around that?

Firefly2012.. Your code worked flawlessly!! thank you. The only reason i would consider the other code is for the fact that the code is much shorter.

Thank you so much for your help!!

 

[dean.rogers]

Through testing, it would appear that the maximum length of the replacement text is 255 characters. If you are exceeding this, then you may need to run multiple replaces. You could do it like this (this replaces your section starting "With wrdDocSelection.Find", I have also inserted a couple of new variable declarations (include these at the top of your module):

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
    Else
        .Replacement.Text = strReplacement
        .Execute , , , , , , , , , , wdReplaceAll
    End If
End With

Firefly if you could help me out one more time. This code seems to have an issue when there are more than one cell in excel that is greater than 255 characters. When I perform this, It will work for one variable is Word that is > 255 characters but the next variable it will not. Is there a way around this?