Vba Error - String parameter too long

[Imtiaz1313]

Hello, I've been trying to use a code to search a MS word file for some words in a column in excel and get the page numbers that contains the words in it.

I found this code online. When I try to run it, it gives an error - "String parameter too long". There's probably a limit of 250 characters.

Can someone help me break down the string into small chunks of less than 250 characters ?

The code is -

Sub OpenWordDoc()
Dim wordapp As Word.Application
Dim findRange As Excel.Range
Dim findCell As Excel.Range
Dim rngFound As Word.Range

Set wordapp = CreateObject("word.Application")
wordapp.Visible = True
wordapp.Activate
wordapp.Documents.Open "filename.docx"
Set findRange = Sheet1.Range("D4:D8")
For Each findCell In findRange.Cells
Set rngFound = wordapp.ActiveDocument.Range
With rngFound.Find
.Text = findCell.Value
.Execute
End With
If rngFound.Find.Found Then
findCell.Offset(columnOffset:=1) = rngFound.Information(wdActiveEndPageNumber)
Else
findCell.Offset(columnOffset:=1) = findCell.Value
End If
Next findCell
wordapp.Quit

Set rngFound = Nothing
Set findCell = Nothing
Set findRange = Nothing
Set wordapp = Nothing
End Sub

 

[6StringJazzer]

What line of code is highlighted when the error occurs? I do not see a String parameter or even a String variable here. What String do you have that is probably over 250 characters? A String in VBA can be extremely long, like 2B characters, although some functions will not accept parameters over 255 characters.

 

[Imtiaz1313]

Thanks for the reply. Sorry, I mistakenly pasted another code that I tried. This code just opened the word file but didn't give any result at all.

The code that was giving - "Run-time error '5854', string parameter too long" is this one -

Sub OpenWordDoc2()

Set wordapp = CreateObject("word.Application")
wordapp.Visible = True
wordapp.Activate
wordapp.Documents.Open "C:\Users\Imtiazur\Desktop\Witness2.docx"
Set findRange = Sheet1.Range("D4:D6")
For Each findCell In findRange.Cells
Set rngFound = wordapp.ActiveDocument.Range.Find
rngFound.Text = findCell.Value
rngFound.Execute
If rngFound.Found Then
findCell.Offset(columnOffset:=1) = rngFound.Parent.Information(wdActiveEndPageNumber)
Else
findCell.Offset(columnOffset:=1) = findCell.Value
End If

wordapp.Quit
Set wordapp = Nothing
End Sub


The problematic line is -
rngFound.Text = findCell.ভালুএ


It'd be a great help if you can solve this, or give me some other code that would do the job.

 

[Imtiaz1313]

Thanks for the reply. Sorry, I mistakenly pasted another code that I tried. This code just opened the word file but didn't give any result at all.

The code that was giving - "Run-time error '5854', string parameter too long" is this one -

Sub OpenWordDoc2()

Set wordapp = CreateObject("word.Application")
wordapp.Visible = True
wordapp.Activate
wordapp.Documents.Open "C:\Users\Imtiazur\Desktop\Witness2.docx"
Set findRange = Sheet1.Range("D4:D6")
For Each findCell In findRange.Cells
Set rngFound = wordapp.ActiveDocument.Range.Find
rngFound.Text = findCell.Value
rngFound.Execute
If rngFound.Found Then
findCell.Offset(columnOffset:=1) = rngFound.Parent.Information(wdActiveEndPageNumber)
Else
findCell.Offset(columnOffset:=1) = findCell.Value
End If

wordapp.Quit
Set wordapp = Nothing
End Sub


The problematic line is -
rngFound.Text = findCell.ভালুএ


It'd be a great help if you can solve this, or give me some other code that would do the job.

rngFound.Text = findCell.Value

 

[6StringJazzer]

The limit appears to be 256. If you are doing a Find you cannot break it up.

Can you describe what you want your code to do?

 

[Imtiaz1313]

The limit appears to be 256. If you are doing a Find you cannot break it up.

Can you describe what you want your code to do?

I have around 1200 words in column A in Excel. I want to search a word file (docx) for those words and get the page numbers that contains those words.
For example, let the word in Excel cell A1 is "gentle". I want to search the doc file for the word " gentle" and get all the page numbers that contains the word gentle in the adjacent cell B1. Like - 3, 15, 94, 135, 267, 670.

 

[6StringJazzer]

What word are you searching for that has >256 characters?

 

[6StringJazzer]

One option might be to truncate the search term. It is unlikely that your search terms will differ only after the 256th character.

rngFound.Text = Left(findCell.Value, 256)

 

[Imtiaz1313]

rngFound.Text = Left(findCell.Value, 256)
This stopped the error. But I'm getting no outcome. The macro is opening the word file blinking for a few seconds and then closing the word file. Any idea ?

** I don't have any words that's greater than 20 characters.

 

[6StringJazzer]

I can't get any further without being able to see your Excel data and Word file. If you don't have any words greater than 20 characters then you should not have gotten this error.