clear table cell of junk

[Staroslav]

In MS Word
I noticed that whenever you center (Ctrl+E) a number/text in a cell that has
extra spaces or tabs, word automatically gets rid of all the extras, but
keeps numbers/ text. That is a very handy function. I would like to know who
to call it using VBA.
Thank you.
vs

 

[Ed]

Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter

 

[Helmut Weber]

Hi Ed,

welcome, but have you tried it?

There are cases, when recording some keystrokes
and playing them back, doesn't give the same result,
as Word recognizes whether they where typed or played back.

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Ed]

oh boy (wiping egg off face!!)
Obviously, no, I didn't try it - until just now, that is. So I guess Word
somehow automatically mixes the Center alignment function with the Trim
function?

In that case, you'd have to capture the cell contents in a string, back off
two for the cell markers, then Trim left and Right AND align center? I
remember dealing with something like this a while ago, but I thought that
was in Excel, and Word would be better at it.

Sorry, Staroslav, to have pointed you wrong.

Ed

 

[Helmut Weber]

Hi Ed

So I guess Word somehow automatically mixes the Center
alignment function with the Trim function?

Yes, and it better didn't.
Imagine people complaining about all extra spaces
and tabs gone after center aligning by Ctrl-E.

In that case, you'd have to capture the cell contents in a string, back off
two for the cell markers, then Trim left and Right AND align center?

Right, and not a big problem either.
And aligncenter only if you wish to do so.

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Staroslav]

Hello Ed,
Yes, it's a bit tricky.
I understand that something like that needs to be done, but I am not sure I
know how you do it. Have any code samples?
Thanks.
staroslav

 

[Ed]

Staroslav: This will cycle through all cell in a table, replace all tabs
and non-breaking spaces with regular spaces, trim off all leading and
trailing spaces, and center the text. The tabs and non-breaking spaces are
replaced with regular spaces because the Trim function doesn't affect them.
Hope this give you a start.

Ed

Sub Trim2()

Dim myc As Cell
Dim mystr As String
Dim mystr2 As String

' Get text in each cell
For Each myc In Selection.Tables(1).Range.Cells
mystr = myc.Range.Text
' Lose cell marker from string
mystr2 = Left(mystr, Len(mystr) - 2)

' Replace tabs with spaces
mystr2 = Replace(mystr2, Chr(9), Chr(32))
' Replace non-breaking spaces with spaces
mystr2 = Replace(mystr2, Chr(160), Chr(32))
' Remove leading, training spaces
mystr2 = trim(mystr2)
' Insert this in cell
myc.Range.Text = mystr2
' Center text
myc.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter

Next myc

End Sub

 

[Staroslav]

That was very helpful. Thank you, Ed!
Man I wish I knew more! Do you know of any self-study tools on vba in MS
office?

 

[Ed]

One thing: the insertion point needs to be inside the table before the
macro will work. All you need to do is click somewhere inside the table.

Here's what I did to learn:
(1) Use "Help" a lot. You can type in something in the VBE and press F1;
it will pop up a Help topic on that word, and most come with samples.
(2) Google the newsgroups. Most questions have been asked and answered
before. Read other people's questions, see if you can figure out the
answer, read back to see the answer they got.
(3) Record a macro, then dissect the code to see what it did and why.
(4) Use the MVP site: word.mvps.org
(5) Look through the available VBA books to find one that fits where you're
at. I've got "Word 2000 Developer's Handbook", and it works well for me. I
also printed out newsgroup posts that contained things I wanted to know and
Help topics I used more than once or twice - I made my OWN book!
(6) Try lots of stuff! Just not on important documents!
(7) Give yourself time to learn. There's a lot there. I've been at it
about 1 1/2 years, and I'm just now beginning to realize how much I don't
know!

Have fun!
Ed