VBA parsing Word document into Excel with problems [resolved]

[ethyachk]

Hello all,

As part of the "next big thing" project, my team (me and one other guy) needs to take a huge and very ugly Word document, grab some data out of tables in the document, and populate an Excel worksheet with the results. If this sounds like it shouldn't be that hard, I'm inclined to agree.

However there is a fairly agravating snag:

Whoever created the Word document decided to use a non Windows standard font. Over all this has not created too many problems, but in an unfortunate number of cases somewhere between Word and Excel an unkown character gets converted into a square character and placed in the current cell amidst the text or numbers. If you can see a square here ->  , these are the little buggers we're having troubles with. If not, imagine a small square that fills you with rage and you'll get my meaning.

We have tried to do any number of code workarounds to be rid of our friends the squares. We've tried Triming the line, we've written a new trim function that's more robust, we've written a trim function that theoretically will change any ASCII character outside of certain ranges into a blank space. We've used ASCII funtions to take care of line feeds, new lines, tabs, carriage returns, and all manner of other more obscure ASCII characters. Some of this worked, but ultimately we can't get rid of some of the squares.

I'm sure by now you're thinking, "Change the stupid font and be done with it." Well, there are two problems. First of all we're not supposed to even be allowed to see this document, and second we tried that already anyways, so no such luck. The most important detail is we need a dash "-" from this wacky font to be replaced with a dash from a normal font. If we could detect it we'd just replace it, but for everything we've tried we just cannot get the parser to even acknowledge the existance of that square and.

Personally I'm expecting the answer to be, "Wow dude, you're hosed." However, I'm throwing hope to the winds and asking anyway. After reading through a lot of posts here I've been quite impressed with the knowledgeable people here. Hopefully one of you has a solution or some suggestion of something we haven't tried yet.

Thanks for any help!

-S