VBA in Word Table Cells -- Horiz. Align./Text/Paragraphs 1

[afelissimo]

Please help! How do you set horizontal alignment, insert text and insert paragraph returns inside a specific table cell. I can get all of this to work outside of a table, but I can not get this to work in the table.

TIA!!!

--from a beginner VBA programmer

 

[PHV]

Did you try to play with the macro recorder ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

What is "this"?

Basically though, if you are talking about a specific cell, then set a Cell object for that cell. All normal properties of a cell are then available to that cell object.

Dim aCell As Cell
Set aCell = ActiveDocument.Tables(3).Cell(2,2)

' insert text
aCell.Range.Text = "yadda"

' insert paragraph mark
'  ummmm, WHERE exactly???
'  at the beginning
acell.Range.Text = vbCrLf & aCell.Range.Text
'  at the end
acell.Range.Text = aCell.Range.Text & vbCrLf

As for "horizontal alignment", you will have to state what you mean by that.

Gerry

Reason is a whore, the greatest enemy that faith has."
Martin Luther

 

[afelissimo]

Thanks PHV. I started the whole thing by recording it, and I've been debugging it ever since. I had no trouble whatsoever creating a new blank document, setting the margins, creating the table, setting the vertical alignment for the rows and creating, coloring and placing two drawing shapes in the document; however, when it hits the paragraph alignment center code it just stops or ignores it. Then it ignores all the insert text and "TypeParagraph" code. Alternatively, sometimes when I tweak the code, it inserts all the text after the table and formats it too. I just can't get it to do it in the table cell.

Fumei -- Thanks! As you can see in my code below, I tried using extend to tell VBA where to format, but it didn't really work.

When I get home tonight, I'll try tweaking the code and see if your fix is the magic answer. I promise to post my results for everyone.
Here's the code:

Sub Envelope()
'
' Envelope Macro
'
'
Documents.Add DocumentType:=wdNewBlankDocument
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Font.Name = "Times New Roman"
Selection.Font.Size = 12
Selection.MoveLeft Unit:=wdCharacter, Count:=1
With ActiveDocument.PageSetup

.TopMargin = InchesToPoints(0.3)
.BottomMargin = InchesToPoints(0.3)
.LeftMargin = InchesToPoints(0.3)
.RightMargin = InchesToPoints(0.3)

End With
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=4, NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
wdAutoFitFixed

Selection.MoveDown Unit:=wdLine, Count:=3, Extend:=wdExtend
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
Selection.Move Unit:=wdRow, Count:=1

ActiveDocument.Tables(1).Rows(1).Height = InchesToPoints(7.13)
ActiveDocument.Tables(1).Rows(2).Height = InchesToPoints(1.25)
ActiveDocument.Tables(1).Rows(3).Height = InchesToPoints(0.18)
ActiveDocument.Tables(1).Rows(4).Height = InchesToPoints(1.61)

With ActiveDocument.Tables(1).Rows(2).Cells(1)
.VerticalAlignment = wdCellAlignVerticalCenter
Selection.Extend
Selection.Extend
With Selection.ParagraphFormat
.Alignment = wdAlignParagraphCenter
End With

Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
With Selection.Font
.Name = "Garamond"
.Size = 13
.SmallCaps = True
End With
.Range.Text = "company name"
With Selection
.Collapse Direction:=wdCollapseEnd
.TypeParagraph
End With

With Selection.Font
.Name = "Garamond"
.Size = 10
.SmallCaps = False
End With
.Range.Text = "ATTORNEYS AT LAW"
With Selection
.Collapse Direction:=wdCollapseEnd
.TypeParagraph
End With
.Range.Text = "first line address"
With Selection
.Collapse Direction:=wdCollapseEnd
.TypeParagraph
End With
.Range.Text = "NEW YORK, NEW YORK 10038"
End With

With ActiveDocument.Tables(1).Rows(4).Cells(1)
.VerticalAlignment = wdCellAlignVerticalCenter
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.ParagraphFormat.LeftIndent = InchesToPoints(0.25)
Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:= _
wdInLine, DisplayAsIcon:=False
End With

ActiveDocument.Shapes.AddConnector(msoConnectorStraight, 1.05, 101.65, _
609.9, 0#).Select
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.ConnectorFormat.Type = msoConnectorStraight
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Rotation = 0#
Selection.ShapeRange.Left = 0.7
Selection.ShapeRange.Top = 101.5
Selection.ShapeRange.RelativeHorizontalPosition = _
wdRelativeHorizontalPositionColumn
Selection.ShapeRange.RelativeVerticalPosition = _
wdRelativeVerticalPositionPage
Selection.ShapeRange.RelativeHorizontalSize = wdRelativeHorizontalSizePage
Selection.ShapeRange.RelativeVerticalSize = wdRelativeVerticalSizePage
Selection.ShapeRange.Left = InchesToPoints(-0.29)
Selection.ShapeRange.LeftRelative = wdShapePositionRelativeNone
Selection.ShapeRange.Top = InchesToPoints(2.95)
Selection.ShapeRange.TopRelative = wdShapePositionRelativeNone
Selection.ShapeRange.WidthRelative = wdShapeSizeRelativeNone
Selection.ShapeRange.HeightRelative = wdShapeSizeRelativeNone
Selection.ShapeRange.LockAnchor = False
Selection.ShapeRange.LayoutInCell = True
Selection.ShapeRange.WrapFormat.AllowOverlap = True
Selection.ShapeRange.WrapFormat.Side = wdWrapBoth
Selection.ShapeRange.WrapFormat.DistanceTop = InchesToPoints(0)
Selection.ShapeRange.WrapFormat.DistanceBottom = InchesToPoints(0)
Selection.ShapeRange.WrapFormat.DistanceLeft = InchesToPoints(0.13)
Selection.ShapeRange.WrapFormat.DistanceRight = InchesToPoints(0.13)
Selection.ShapeRange.WrapFormat.Type = 3
Selection.ShapeRange.ZOrder 4
Selection.ShapeRange.Line.ForeColor.ObjectThemeColor = _
wdThemeColorBackground1
Selection.ShapeRange.Line.ForeColor.TintAndShade = -0.25
Selection.ShapeRange.Line.Visible = msoTrue
ActiveWindow.ActivePane.VerticalPercentScrolled = 24
ActiveDocument.Shapes.AddConnector(msoConnectorStraight, 0.7, 392.8, _
609.9, 0#).Select
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.ConnectorFormat.Type = msoConnectorStraight
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Rotation = 0#
Selection.ShapeRange.Left = 0.7
Selection.ShapeRange.Top = 393.1
Selection.ShapeRange.RelativeHorizontalPosition = _
wdRelativeHorizontalPositionColumn
Selection.ShapeRange.RelativeVerticalPosition = _
wdRelativeVerticalPositionPage
Selection.ShapeRange.RelativeHorizontalSize = wdRelativeHorizontalSizePage
Selection.ShapeRange.RelativeVerticalSize = wdRelativeVerticalSizePage
Selection.ShapeRange.Left = InchesToPoints(-0.29)
Selection.ShapeRange.LeftRelative = wdShapePositionRelativeNone
Selection.ShapeRange.Top = InchesToPoints(7.13)
Selection.ShapeRange.TopRelative = wdShapePositionRelativeNone
Selection.ShapeRange.WidthRelative = wdShapeSizeRelativeNone
Selection.ShapeRange.HeightRelative = wdShapeSizeRelativeNone
Selection.ShapeRange.LockAnchor = False
Selection.ShapeRange.LayoutInCell = True
Selection.ShapeRange.WrapFormat.AllowOverlap = True
Selection.ShapeRange.WrapFormat.Side = wdWrapBoth
Selection.ShapeRange.WrapFormat.DistanceTop = InchesToPoints(0)
Selection.ShapeRange.WrapFormat.DistanceBottom = InchesToPoints(0)
Selection.ShapeRange.WrapFormat.DistanceLeft = InchesToPoints(0.13)
Selection.ShapeRange.WrapFormat.DistanceRight = InchesToPoints(0.13)
Selection.ShapeRange.WrapFormat.Type = 3
Selection.ShapeRange.ZOrder 4
Selection.ShapeRange.Line.ForeColor.ObjectThemeColor = _
wdThemeColorBackground1
Selection.ShapeRange.Line.ForeColor.TintAndShade = -0.25
Selection.ShapeRange.Line.Visible = msoTrue
End Sub
Sub TestSelectCell()
'
' TestSelectCell Macro
'
'
Selection.Extend
Selection.Extend
With Selection.ParagraphFormat
.LeftIndent = InchesToPoints(0)
.RightIndent = InchesToPoints(0)
.SpaceBefore = 0
.SpaceBeforeAuto = False
.SpaceAfter = 0
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceSingle
.Alignment = wdAlignParagraphCenter
.WidowControl = True
.KeepWithNext = False
.KeepTogether = False
.PageBreakBefore = False
.NoLineNumber = False
.Hyphenation = True
.FirstLineIndent = InchesToPoints(0)
.OutlineLevel = wdOutlineLevelBodyText
.CharacterUnitLeftIndent = 0
.CharacterUnitRightIndent = 0
.CharacterUnitFirstLineIndent = 0
.LineUnitBefore = 0
.LineUnitAfter = 0
.MirrorIndents = False
.TextboxTightWrap = wdTightNone
End With

End Sub

 

[fumei]

Aaaaack! All those uses of Selection. Avoid Selection...although to be fair, using the macro recording always uses Selection. Hopefully you will learn to adjust macro recordings to use Range, rather than Selection.

OK. Please describe - exactly - what it is you want to do. I could probably work through all that Selection instructions, but just looking at it gives me hives.

Oh, and please use the TGML code tags when posting code. To see how to do this (and other formatting) click the "Process TGML" link beside Step 2 Options.

First off, what are you trying to achieve by using Extend? I am not following this.

Do you want to perform actions on the cell the Selection (cursor) is in, or do you want to perform actions - as stated - on a specific cell?

when it hits the paragraph alignment center code it just stops or ignores it. Then it ignores all the insert text and "TypeParagraph" code.

Most likely because the selection is not in the correct place.

I am also wondering about why you are trying to do as executed actions (a macro). It sure seems to me that this looks like a template. In other words, create the document as you want it, save it as a .dot (template) file, and then you can clone it as a new document any time you want.

Gerry

Och ammmmm, I think I need a shave.
- hirsute Scot, trying to decide

 

[afelissimo]

Hi Fumei,

Thank you for being so responsive! It kind of is a template, but I do want it to be a macro. I guess I could create a template...

At work, we use a double-windowed envelope for mailings. The secretaries print out a sheet with our return address and the mailing address. The firm is currently using WordPerfect, but we're switching to Word 2007. I'm trying to write a macro that could go on everyone's QAT. The secretary would highlight and copy the address where the letter is going and then activate the macro. The macro is supposed to open a new document, put in the fold lines (the line shapes in the macro), enter our return address in column 1, row 2, and then paste as unformatted text the contents of the clipboard in column 1, row 4. Everything needs to be formatted precisely so that the addresses can be seen through the envelope windows. I am very new to editing recorded code, so I'm stumbling a bit here. I have been trying to find someplace that will teach me the basics online, but honestly I'm a little pressed for time. I'm just posting the code for the table here, and I'll try to post it correctly this time. Time to sleep soon, but I'll be tackling this again in the morning.

Thanks again!!!!!!


ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=4, NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
wdAutoFitFixed

Selection.MoveDown Unit:=wdLine, Count:=3, Extend:=wdExtend
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
Selection.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
Selection.Move Unit:=wdRow, Count:=1

ActiveDocument.Tables(1).Rows(1).Height = InchesToPoints(7.13)
ActiveDocument.Tables(1).Rows(2).Height = InchesToPoints(1.25)
ActiveDocument.Tables(1).Rows(3).Height = InchesToPoints(0.18)
ActiveDocument.Tables(1).Rows(4).Height = InchesToPoints(1.61)

With ActiveDocument.Tables(1).Rows(2).Cells(1)
.VerticalAlignment = wdCellAlignVerticalCenter
Selection.Extend
Selection.Extend
With Selection.ParagraphFormat
.Alignment = wdAlignParagraphCenter
End With

Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
With Selection.Font
.Name = "Garamond"
.Size = 13
.SmallCaps = True
End With
.Range.Text = "company name"
With Selection
.Collapse Direction:=wdCollapseEnd
.TypeParagraph
End With

With Selection.Font
.Name = "Garamond"
.Size = 10
.SmallCaps = False
End With
.Range.Text = "ATTORNEYS AT LAW"
With Selection
.Collapse Direction:=wdCollapseEnd
.TypeParagraph
End With
.Range.Text = "first line address"
With Selection
.Collapse Direction:=wdCollapseEnd
.TypeParagraph
End With
.Range.Text = "NEW YORK, NEW YORK 10038"
End With

With ActiveDocument.Tables(1).Rows(4).Cells(1)
.VerticalAlignment = wdCellAlignVerticalCenter
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.ParagraphFormat.LeftIndent = InchesToPoints(0.25)
Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:= _
wdInLine, DisplayAsIcon:=False
End With

 

[afelissimo]

Hi Fumei,

Thank you for being so responsive! It kind of is a template, but I do want it to be a macro. I guess I could create a template...

At work, we use a double-windowed envelope for mailings. The secretaries print out a sheet with our return address and the mailing address. The firm is currently using WordPerfect, but we're switching to Word 2007. I'm trying to write a macro that could go on everyone's QAT. The secretary would highlight and copy the address where the letter is going and then activate the macro. The macro is supposed to open a new document, put in the fold lines (the line shapes in the macro), enter our return address in column 1, row 2, and then paste as unformatted text the contents of the clipboard in column 1, row 4. Everything needs to be formatted precisely so that the addresses can be seen through the envelope windows. I am very new to editing recorded code, so I'm stumbling a bit here. I'm just posting the code for the table here, and I made my comments green to help. Time to sleep soon, but I'll be tackling this again in the morning.

Thanks again!!!!!!

'I thought this line would put my cursor/insertion point in the correct cell

With ActiveDocument.Tables(1).Rows(2).Cells(1)
'The vertical alignment works just fine in the correct table cell
.VerticalAlignment = wdCellAlignVerticalCenter
'I tried using extend to "select" the cell, because the commands after extend are not going into the cell
Selection.Extend
Selection.Extend
'This gets applied to the first paragraph after the table, but is meant for the cell mentioned above. I think there's an easy fix, if I can just get the code right for selecting the cell
With Selection.ParagraphFormat
.Alignment = wdAlignParagraphCenter
End With

Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
With Selection.Font
.Name = "Garamond"
.Size = 13
.SmallCaps = True
End With
.Range.Text = "company name"
With Selection
.Collapse Direction:=wdCollapseEnd
.TypeParagraph
End With

With Selection.Font
.Name = "Garamond"
.Size = 10
.SmallCaps = False
End With
.Range.Text = "ATTORNEYS AT LAW"
With Selection
.Collapse Direction:=wdCollapseEnd
.TypeParagraph
End With
.Range.Text = "first line address"
With Selection
.Collapse Direction:=wdCollapseEnd
.TypeParagraph
End With
.Range.Text = "NEW YORK, NEW YORK 10038"
End With

With ActiveDocument.Tables(1).Rows(4).Cells(1)
.VerticalAlignment = wdCellAlignVerticalCenter
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.ParagraphFormat.LeftIndent = InchesToPoints(0.25)
Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:= _
wdInLine, DisplayAsIcon:=False
End With

 

[fumei]

The macro is supposed to open a new document, put in the fold lines (the line shapes in the macro), enter our return address in column 1, row 2, and then paste as unformatted text the contents of the clipboard in column 1, row 4. Everything needs to be formatted precisely so that the addresses can be seen through the envelope windows.

This is precisely, exactly, the reason for using a template. This is what templates are for.

Doing it via code each time is NOT the way to go. Here is the entire code in a template that will do exactly as you have described.

Assumptions:

The template has the fold lines, and table already in it. This is, again, the whole point of templates.

The template is named yadda.dot.

[b][COLOR=red]' in Normal.dot or other global template[/b][/color red]
Sub Address_Stuff()
' keyboard shortcut = Alt-A
   Documents.Add Template:="Yadda.dot"
End Sub

[b][COLOR=red]' in the template Thisdocument code module[/b][/color red]
' again this is the ENTIRE code to do your task
Option Explicit

Const OurAddress As String = "1234 Whatever St." & _
   vbCrLf & "SomeCity, Blah"
   
Private Sub Document_New()
Dim r As Range

ActiveDocument.Tables(1).Cell(2, 1).Range.Text = OurAddress
Set r = ActiveDocument.Tables(1).Cell(4, 1).Range
r.Paste
End Sub

I set up the calling procedure (Address_Stuff) to have a keyboard shortcut. So I can select something in ANY document, click the Copy button, and then hit Alt-A.

What happens.

1. a new document is created, cloned from the template "yadda.dot". Remember this already has your folds and your table.

Documents.Add Template:="Yadda.dot"

2. when a template is cloned to new document, the Document_New event is fired. This first inserts the CONSTANT OurAddress (already defined) into Cell(1,2).

ActiveDocument.Tables(1).Cell(2, 1).Range.Text = OurAddress

3. it then pastes whatever you copied into the clipboard into Cell(1,4)

Set r = ActiveDocument.Tables(1).Cell(4, 1).Range
r.Paste

Done. As it is a new document, your template is unchanged, ready to be used again.

In the above I used a procedure in Normal.dot to start things. But you don;t have to do this. You could have a icon on a toolbar, a menu item (version issues in mind); or simply use File > New and select the template.

Either way, the template is cloned and Document_New fires aand does its thing.

Gerry

Och ammmmm, I think I need a shave.
- hirsute Scot, trying to decide

 

[fumei]

Got my Cell index backwards in my explanation.

"paste as unformatted text the contents of the clipboard in column 1, row 4."

My instruction is correct.

Set r = ActiveDocument.Tables(1).Cell(4, 1).Range
r.Paste

Gerry

Och ammmmm, I think I need a shave.
- hirsute Scot, trying to decide

 

[afelissimo]

Fumei,

Your original advice about setting the cell and then using it before the command actually helped me fix a lot of the problem code:

Dim aCell As Cell
Set aCell = ActiveDocument.Tables(3).Cell(2,2)

' insert text
aCell.Range.Text = "yadda"

This little snippet of code was brilliant, and I realized that I needed to use this idea over and over again for all of my commands.

It's working beautifully now, but still needs a few minor tweaks.

Regarding your most recent post. I can talk to my IT department about using a group template instead of my macro.

Whichever way we decide to go, I am glad that I struggled through this project, because I learned a lot about cleaning up recorded code and how to work inside of tables.

When I have more time, I will post my final code with comments and markup.

Thank you again for your help!!!!

 

[fumei]

This little snippet of code was brilliant, and I realized that I needed to use this idea over and over again for all of my commands."

I am covered with blushes. Seriously though, if you want to REALLY use VBA you need to use objects. It is arguably the most powerful aspect of VBA.

Gerry

Och ammmmm, I think I need a shave.
- hirsute Scot, trying to decide