Edit table cells in VBA

[stevewy]

Is there a way I could go through each table cell in a document,
removing the last character from each cell? I know that each table
cell ends with a table end cell marker, so perhaps what I what is to
remove the last-but-one character in each cell.

I guess it isn't as easy as saying:

For Each Cell in ActiveDocument.Tables(1)
NewContent=Left(Content, Len(Content)-2)
Next

.... so where am I going wrong? Barking up the wrong tree completely,
or just slightly out?

 

[Jay Freedman]

Is there a way I could go through each table cell in a document,
removing the last character from each cell? I know that each table
cell ends with a table end cell marker, so perhaps what I what is to
remove the last-but-one character in each cell.

I guess it isn't as easy as saying:

For Each Cell in ActiveDocument.Tables(1)
NewContent=Left(Content, Len(Content)-2)
Next

... so where am I going wrong? Barking up the wrong tree completely,
or just slightly out?

No, it certainly isn't that easy.

For one thing, there's no way in VBA to say "for each cell in this table."
You could loop through the table's Rows collection and, for each row, loop
through its cells (or loop through each cell in each column). The problem
with that approach is that VBA barfs if you try to access the Rows
collection of a table that has vertically merged cells, or the Columns
collection of a table that has horizontally merged cells. The only
reasonable way to deal with it is to start with the cell in row 1/column 1
and keep doing "process this cell and go to the next cell" until you run out
of cells.

You also have to check for "empty" cells, ones that contain only the cell
marker, because trying to use the Left() method with a negative length would
throw an error.

Try it this way:

Sub ZapLastCharInCell()
Dim myRg As Range
Dim myTbl As Table
Dim myCel As Cell

On Error GoTo ErrHdl
For Each myTbl In ActiveDocument.Tables
' every table has at least one cell
Set myCel = myTbl.Cell(1, 1)

Do
Set myRg = myCel.Range
' exclude the cell marker from the range
myRg.MoveEnd unit:=wdCharacter, Count:=-1
' test that there's something there before shortening
If Len(myRg.Text) > 0 Then
myRg.Text = Left$(myRg.Text, Len(myRg.Text) - 1)
End If
' try to go to the next cell
' (left to right, then down to next row)
Set myCel = myCel.Next
Loop Until myCel Is Nothing
Next
Exit Sub

ErrHdl:
MsgBox Err.Number & vbCr & Err.Description, , "Error"
End Sub


--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[stevewy]

Thank you for your prompt reply, and the macro which will certainly get
some use. The reason I want to do this rather odd thing, is because
when someone in our office merges to labels from a data source, the
text is never correctly vertically-centered on the labels because the
last carriage-return symbol for the last field always louses it up. It
needs to be deleted from each label so the centering will work
properly. And going through a few hundred labels manually removing a
single carriage-return from each label would be majorly time-consuming!

It is a pity Word's find & replace function cannot identify an
end-of-cell-marker (can it?), or I could do a search for ^13(and the
EOCM symbol) and replace it with just the EOCM. Unfortunately it
doesn't seem possible to do that in Word.

Once again, many thanks.

Steve Wylie

 

[Graham Mayor]

Wouldn't it be simpler to set the merge up properly instead of trying to
deal with a cockup?
See http://www.gmayor.com/mail_merge_labels_with_word_xp.htm If the last
paragraph mark offends, then delete it from the source document, propagate
again then remerge.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Tony Jollans]

For one thing, there's no way in VBA to say "for each cell in this table."

There is, actually. Try

For Each cel In ActiveDocument.Tables(1).Range.Cells
Set rng = cel.Range
rng.MoveEnd wdCharacter, -1
rng.Characters.Last.Delete
Next

But I agree with Graham that it would be better to sort out the merge so
that it wasn't necessary.

 

[stevewy]

Wouldn't it be simpler to set the merge up properly instead of trying to
deal with a cockup?

Ideally yes, but often a document will come to me ready-merged, and if
I notice any problems with it, I may ask the author for the original
source file, and they say "I didn't save it... I thought the merged
labels would be the important thing to save...", so I am left with the
merged file. "Can I have the data file then, and I'll set up a quick
label format and remerge." "Will my amendments show up in the new
labels then?" "What amendments?" "The ones I did to my labels after
they were merged. That's why I saved them for you...."

But yes, I agree with you totally. Much better to do it right in the
first place than have to patch up a bad merge. However, I also have a
macro to remove multiple tab-tab-tabs at the start of each line of a
paragraph, where the typist couldn't be bothered/didn't know to
hang-indent the paragraph properly....

 

[stevewy]

Thanks Tony - that's quite a natty bit of code. I imagine it's the
rng.MoveEnd wdCharacter, -1 that does the clever bit. I'm gonna hit
the help files and look at that command again.

One query though..... how come I don't get an error when Word
encounters the word "cel"? Surely this is an undefined variable? I
thought you would have needed a DIM cel AS CELL at the start, or
something?

Steve

 

[Tony Jollans]

Hi Steve,

If you don't have "Option Explicit" at the top of your module (you *should*
but if you don't) then implicit declaration of variables on first reference
is allowed.

What I posted was just a snippet of code rather than a complete procedure
and you should declare ..

Dim cel as Word.Cell ' and
Dim rng As Word.Range

 

[Graham Mayor]

It would probably still be simpler to extract the data from the labels and
re-merge - http://www.gmayor.com/convert_labels_into_mail_merge.htm
and you would have the benefit of a correctly laid out source document for
when the user wants to do it again.
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[stevewy]

Thank you for your help and advice.

Steve