With xlSheet.Cells REPLACE not working

[legend99]

For Gods sake......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 they get blown away...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?!

 

[xlbo]

just use error trapping

on error resume next

...

your code

...

on error goto 0

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[legend99]

I thought of that alright...wasn't going to go with it because I'm not sure that VB will see it as an error? it is Excel that is generating the error no?

 

[xlbo]

If you are using VBA then you are using the replace method in VBA - why not try it out - it's only 2 lines...

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[legend99]

yea, tried it out. Excel itself is the one throwing the message box so looks like I can't stop it.
I have realised that it is the same message box that you get from Excel 2003 when using Contol-H(find and replace) if the date you ask to replace does not exist.

The message you get in Excel 2000 for the same thing is different. I assume from that that whatever happens under the covers has also changed and that with Excel 2003 it is forcing up the message box but that in Excel 2000 whatever way the interaction works it is not popping up the message under the same circumstances.

 

[xlbo]

But you are controlling excel so it shouldn't matter - unless of course you are programming in VB rather than VBA - in which case you need a different forum. Either that or it is a new 2003 "feature". The REPLACE function in XP does not throw an error if it can't find anything to replace.

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question