How to replace word character to excel character

[Johnosley]

I have a word document which is copy/paste to excel using VBA macro.

Basically, the word document consists of tables with data. The VBA macro get each data from specific cell and then copy the data to a specific cell in excel.

It works well. Thanks Tinbendr and Gmaxey for your great help on that.

Now I have an other problem... word character !

In word I could have: « in word » or I’m

But in excel I would like to see "in word" or I'm

The data from excel will be uploaded to an other software which doesn't recognize the « and ’ and the upload will generate errors.

Is there a way to avoid such problem. And I guess my examples are not the only one. May be. Did someone encounter that problem before ?

Thank you

I know that the substitute function works but it has to be done one specific character by one and also in VBA what could be the code for word/excel character. Are they the same ?

[gcomyn]

Probably use a 'replace' or 'substitute' function when you bring the cell values over.

GComyn
:sleuth:

[Tinbendr]

That is a good suggestion.

You could just perform aFind/Replace on the whole document right after you've opened Word. Just don't save the doc.

Sub FindReplace(aDoc As Word.Document)
Dim Rng As Word.Range
Set Rng = aDoc.Content
With Rng.Find
.Execute Findtext:="« ", replacewith:=Chr(34), Replace:=wdReplaceAll
.Execute Findtext:=" »", replacewith:=Chr(34), Replace:=wdReplaceAll
End With

End Sub

[GTO]

Greetings John,


...It works well. Thanks Tinbendr and Gmaxey for your great help on that.

Now I have an other problem... word character !

In word I could have: « in word » or I’m

But in excel I would like to see "in word" or I'm

The data from excel will be uploaded to an other software which doesn't recognize the « and ’ and the upload will generate errors...cel character. Are they the same ?


Thank you for recognizing/thanking the time and effort of others thus far. Maybe just my opinion, but graciousness and appreciation count, and kudos for your relayance (I can make up words if I want to) of said.

To your present issue, maybe just the blonde/thick-headed guy, but could you post an example WB in .xls format showing the 'before' and what you'd like 'after'?

Thank you,

Mark

[Johnosley]

Greetings John,
To your present issue, maybe just the blonde/thick-headed guy, but could you post an example WB in .xls format showing the 'before' and what you'd like 'after'?

Thank you,

Mark

See attached. hope it helps. I will try the code from Tinbendr (again :yes)

John