Need help with replace function in VBA

[jslomka]

Hi All,
I'm working with a word table pasted into excel. The dashes in the word table paste into Excel (2003) as small boxes. I can use the find and replace function in Excel and in the Excel environment the replace function works. I recorded the replace function so I could develop the action in VBA. The recoded action does not work. The data I've pasted in below looks fine, however when pasted into Excel all of the "-" paste in as small boxes. When I used the chr function the chr code equals 63 which is "?". I used chr(63) in the replace function. This causes the VBA replace function to replace all characters in the file with dashes. I've tried several other codes (127,129,141,143,144,157) (none worked) based on the following website:

http://www.planet-source-code.com/URLSEO/vb/scripts/ShowCode!asp/txtCodeId!9193/lngWid!4/anyname.htm

Cells.Replace What:=Chr(63), Replacement:="-" 'finds and replaces the - that converted to boxes

I can paste in the data using paste special "unicode text" but I loose the strike through formating which I need to identify obsolete data.

I have searched for several hours for a solution and have had no luck. If you have any suggestions please let me know. Thanks in advance.

Jeff

REV PART NUMBER QUANTITY
Kit A Kit B Kit C Kit D Kit E Kit F Kit G
3360700‑1 Ref ‑ ‑ ‑ ‑ ‑ ‑
3360700-3 ‑ Ref ‑ ‑ ‑ ‑ ‑
R25 3360700 Rev. A & PS*A1‑A5 1 1 ‑ ‑ ‑ ‑ ‑
DS2264 Rev. J 1 1 ‑ ‑ ‑ ‑ ‑

 

[VoG]

Jeff

Sorry for the delay in getting back to you.

When I copy the Word table and paste it into Excel it all formats correctly, the dashes appear as dashes and strikethroughs are preserved. I am using Office 2007 which I suppose might make a difference.

As I can't reproduce the problem I'm afraid I can't really help further.

 

[jslomka]

Peter,
Thank you for you help. I would assume the difference is in the 2007 vs the 2003. That piece of information is good to know, as we will be transitioning to 2007 in the near future. Thanks again. I'll have to keep trying to find a work around for 2003 until we transition to 2007.

Have a great day,
Jeff