VBA to remove vbCRLF from text

[Sean McPoland]

Hi,

I have several tables which I am looping through with no
problem, however need to remove the vbcrlf from the
strings (there are some at the end of the lines and in the
middle of the text)

How do I do this generically, ordinarliy I would use
regular expresssions in vbscript but here I am using VBA.

Thanks in advance
regards
Sean

 

[Malcolm Smith]

Sean

What you could do is to go through each cell as you are doing and then
place the text from the cell into a string variable.

Then if you are running Word 2000 upwards I would then use the Replace()
function to replace the vbCrLf character with a zero length character and
then put the string back into the cell.

If you are using Word 97 then the Replace() function isn't there, but it's
easy to knock up one.

Hope that this helps.
- Malc
www.dragondrop.com

 

[fdde]

http://www.xbeat.net/vbspeed/c_Replace.htm

You can find a replace function in the above link if you don't want to write
one.

 

[Guest]

Malcolm,

thanks you are stating exactly what I am doing however it
still does not work.....what I have is

(office 2003)

for each table
for each row
for each cell
txtvariable = replace(cellContents, vbCrLf, "")
next cell
next row
next table

however I still get a pile of nonsense characters (most of
which are vbCrLf's (I use a msgbox to display the string
and if they were removed I would get contiguous text)

I have tried to do a Filter to see what is happening but
this does not give me anything either.

The data is actually going into a Access database.

what ever hep you can give would be greatfully received.

Thanks
Sean

 

[Malcolm Smith]

You could always check to see what these characters are. Perhaps these
could be vbCr or vbLf characters as well. Quite often I replace all
three to be sure.

Also, bear in mind that the last character in the cell will also be a
special character and that will need removing.

- Malc