Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / November 2006

Tip: Looking for answers? Try searching our database.

Edit table cells in VBA

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
stevewy@hotmail.com - 10 Nov 2006 13:55 GMT
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 - 10 Nov 2006 14:57 GMT
> 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
[quoted text clipped - 9 lines]
> ... 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

Signature

Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

stevewy@hotmail.com - 10 Nov 2006 15:13 GMT
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

> > 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
[quoted text clipped - 63 lines]
> Email cannot be acknowledged; please post all follow-ups to the newsgroup so
> all may benefit.
Graham Mayor - 10 Nov 2006 15:54 GMT
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.

Signature

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> 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
[quoted text clipped - 82 lines]
>> Email cannot be acknowledged; please post all follow-ups to the
>> newsgroup so all may benefit.
stevewy@hotmail.com - 10 Nov 2006 16:41 GMT
> 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....
Graham Mayor - 10 Nov 2006 18:03 GMT
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.
Signature

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

>> Wouldn't it be simpler to set the merge up properly instead of
>> trying to deal with a cockup?
[quoted text clipped - 13 lines]
> paragraph, where the typist couldn't be bothered/didn't know to
> hang-indent the paragraph properly....
Tony Jollans - 10 Nov 2006 16:37 GMT
> 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.

Signature

Enjoy,
Tony

>> 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
[quoted text clipped - 56 lines]
>    MsgBox Err.Number & vbCr & Err.Description, , "Error"
> End Sub
stevewy@hotmail.com - 10 Nov 2006 17:01 GMT
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 - 10 Nov 2006 17:45 GMT
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

Signature

Enjoy,
Tony

> 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
[quoted text clipped - 6 lines]
>
> Steve
stevewy@hotmail.com - 13 Nov 2006 09:55 GMT
Thank you for your help and advice.

Steve

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.