Problems/ Errors with Find and Replace in VBA

[Neenas19]

Hi There,
I am working on a small find and replace macro that references an excel spreadsheet for the "replace" portion. My problem is that when the "replace" portion in excel gets to be a bit long it errors out with "String Parameter too long". Which I do not understand b/c none of them are over 255 characters.... Here is what I have
Set xls = CreateObject("Excel.Application&quot
xls.Workbooks.Open "\\koop\shared\TESTTBL.xls"
xls.Visible = True

n = 1
xls.Range("e2&quot.Select
Do While n < 131
With Selection.Find
.Text = xls.ActiveCell.Value
.Replacement.Text = xls.ActiveCell.Offset(0, 1)
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
n = n + 1
xls.ActiveCell.Offset(1, 0).Select
Loop
Any ideas why this would be erroring out on me?? Any help is greatly appreciated!
Nina

 

[hermanlaksko]

Hi Nina
I have had a little look at your code and tested it here, but got all kinds of errors (just to let you know that we try), now a poss solution.
When I have problems with excel I record a macro in excel (as you know the recording creates vba code) doing exactly what I want the code to do, this, if not allways work, gives me some clue to where I should be looking and witch stones to turm over.
Hope this helps you somewhat.
Rgds Herman

 

[Neenas19]

Hi Herman,
Thx for your reply. Yes I do the same. We originally did so and that is how we got the find and replace piece of this code. I needed to add a bit in order to reference the excel spreadsheet instead of only the word doc. Otherwise this is just as it would be recorded in vba. Any other suggestions? The only place I error out is when either of these two lines has more than 255 characters.
.Text = xls.ActiveCell.Value
.Replacement.Text = xls.ActiveCell.Offset(0, 1)

Nina

 

[hermanlaksko]

Hi Nina
Try: .Text = left(xls.ActiveCell.Value,254)
Herman

 

[Neenas19]

HI Herman,
Thanks for your reply. But that will only allow me to read 254 characters right? I want to be able to read/replace ALL of the characters. This could be well over 255. Any other ideas?
Nina

 

[ratman]

No time to test today but try this after each sucessfull find.


Selection.Find.ClearFormatting Thank you,
Dave Rattigan

 

[hermanlaksko]

As I understand you, your excel will not accept more than 255 car, please correct me if I am wrong here.

If the above is true then the Left(..... will do the trick

Herman

 

[dwilson01]

Nina,
I have run into this in the past using the Copy command and I don't know why but this works for me.

Instead of

com.Cells(iRow, 14) = gen.Cells(i, 8)

Use

strTemp = gen.Cells(i, 8)
com.Cells(lRow, 14) = strTemp

So, I think it would look something like
...
With Selection.Find
.Text = xls.ActiveCell.Value
strText = xls.ActiveCell.Offset(0, 1)
.Replacement.Text = color red]strText[/color]
...

I have been able to copy large text, up to ~500 characters using this technique. Hope this helps