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

 

[Imtiaz1313]

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.

Is it possible to share the files with you via mail or any other means.
I am searching the internet for last 5 days. No clue.

 

[6StringJazzer]

I sent you my email address in a PM. I do not want to post it in a public post.

 

[Imtiaz1313]

I sent you my email address in a PM. I do not want to post it in a public post.

Thank you. I've mailed you the files.

 

[6StringJazzer]

Your code is looking for words in Sheet2.Range("D2:D60") but your actual word list is inSheet2.Range("D6:D1295"). That is going to cause you some problems because rows 2, 3, 4 are merged, which is never a good idea.

Second, your code only finds each term one time and inserts the page number. It sounds like you want to find all occurrences.

I changed the revised line of code I gave you back to

           RngFound.Text = FindCell.Value

I also added Option Explicit and declared all your variables. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

Then I ran your code, and it ran to completion with no errors, giving correct results in column E.

Here is the file as run. Words to index sonjit 220517 0214.xlsm

Words to index sonjit 220517 0214.xlsm
	C	D	E	F	G	H
1
2	Index Table for Page Number
3
4
5	Letters	Words	Words2	First Supplementary Bundle	Pleadings Bundle	Witness Bundle
6	A
7		Aback	Aback	N/A	N/A	N/A
8		Able	5	9, 14. 60. 61. 63, 69, 70, 71, 72, 73, 79.	251, 326, 331, 346, 347.	131, 181.
9		Absent	Absent	N/A	N/A	N/A
10		Absolutely	Absolutely	12, 18, 29.	329, 335	N/A
11		Abundance	Abundance	N/A	N/A	N/A
12		Accept	Accept			185,
13		Access	Access			193.
14		Accrue	Accrue			N/A
15		Acquired	Acquired			76.
16		Act	7			84, 181, 188.
17		Action	Action			84, 188, 191, 192, 193, 196.
18		Activate	Activate			N/A
19		Activated	Activated			63, 118.
20		Active	Active			N/A
21		Actual	Actual			180.
22		Add	Add			N/A
23		Added	Added			N/A
24		Addition	Addition			38, 39, 83, 93, 130.
25		Address	Address			76, 83, 125, 181, 182, 183, 192.
26		Addressing	Addressing			N/A
27		Adjourn	Adjourn			N/A
28		Adjourned	Adjourned			N/A
29		Adjournment	Adjournment			N/A
30		Adjourns	Adjourns			N/A
Indexing