Using Replace when opening Excel from VB OCX

[legend99]

I've spent days trying to get my Excel populated from ASP in an OCX and now it seems to turn out that Excel 2003 throws an error when using Replace if it is trying to Replace data that does not exist.
So I have this in for every report:

With xlSheet.Cells
.Replace What:="vbCRLF", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="<*>", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="&nbsp;", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With


So if any report has imbedded any of the above text they get blown away and hence are not visible...works perfect in Excel 2000 on my machine....broken on Clients machine as it says Unable to Replace Data that does not exist...I assume other people have come accross this?!

 

[EBGreen]

You should link to the original thread so that people don't have to go through the same troubleshooting process again:

thread707-1072771

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[legend99]

Sorry about that, new here, and busy tearing my hair out over this, as its such a stupid problem.
I HATE when MS suddenly change things from one version to another!

 

[legend99]

hang on, I might be onto something.....you can stop Excel i think popping messages using:
xlApp.DisplayMessages = false


Right now in Ireland its going home time but I will give this a go tomorrow and update you.

Thanks for everyones help.

 

[SDuke]

Just check first IF data exits (use Len() for strings or IsNull() on variants) prior to using the function.