Cell padding when copying from Excel

[paddysheeran]

Hi All,

I have a procedure that works fine when copying data from Excel to word.

[vba]Public wdApp As New Word.Application
Public wdDoc As Word.Document
Public t As Word.Range
Public wsSource As Worksheet
Set t = wdDoc.Bookmarks("Example 1").Range

Set wsSource = ActiveWorkbook.Sheets("Example 1")
wsSource.Activate

Range("A1").CurrentRegion.Copy
t.Paste

[/vba]

After the table has been entered into Word I want to adjust the cell padding. I've tried the following but this has no affect on the final table:

[vba]With t
With .Cells(1)
.TopPadding = 3
.BottomPadding = 3
.LeftPadding = 2
.RightPadding = 2
.FitText = False
End With
End With[/vba]


Does anyone have a quick fix for this? thanks.

[fumei]

With t

t is declared as a range, not a table. True, the Excel table (region) is inserted into Word, but what is there is not a Word table.

[paddysheeran]

the following work fine though with the t range:

[vba]With t
.Font.Size = "8"

.Tables(1).Rows(1).HeadingFormat = True
.Tables(1).Rows.AllowBreakAcrossPages = False
End With[/vba]

and

[vba] With t.ParagraphFormat
.LeftIndent = 0
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
.CharacterUnitLeftIndent = 0

End With[/vba]

and

[vba]With t 'Table Width Examples
.Tables(1).Columns(1).SetWidth ColumnWidth:=38, RulerStyle:= _
wdAdjustNone
.Tables(1).Columns(2).SetWidth ColumnWidth:=40.65, RulerStyle:= _
wdAdjustNone
.Tables(1).Columns(3).SetWidth ColumnWidth:=41.55, RulerStyle:= _
wdAdjustNone
.Tables(1).Columns(4).SetWidth ColumnWidth:=41.55, RulerStyle:= _
wdAdjustNone
.Tables(1).Columns(5).SetWidth ColumnWidth:=47.75, RulerStyle:= _
wdAdjustNone
.Tables(1).Columns(6).SetWidth ColumnWidth:=88.95, RulerStyle:= _
wdAdjustNone
End With[/vba]

[macropod]

For the padding, change:
With .Cells(1)
to:
With .Tables(1)

[paddysheeran]

I've tried that but there is no cell padding in the final tables

When I select the table in the final word document > Table properties > Cell > Options. The margins are all blank

[macropod]

Aside from having to delete the '.FitText = False' line, using 'With .Tables(1)' works fine for me. This change will work anywhere that your:
With t 'Table Width Examples
.Tables(1).Columns(1).SetWidth
code works.

[paddysheeran]

this only affects the overall table cell margins

When I record the code in word for amending the cell padding the code output is:

[VBA]With Selection.Cells(1)
.TopPadding = 2
.BottomPadding = 2
.LeftPadding = 2
.RightPadding = 2
.FitText = False
End With[/VBA]

[macropod]

Selection.Cells(1) applies to one cell only - the first selected one. If you don't want all cells formatted that way, you need to say what it is you do want. Merely saying "I've tried the following but this has no affect on the final table" is both wrong (With t ... With .Cells(1) affects the first cell) and fails to indicate what you want to achieve.

[paddysheeran]

Sorry I require all cells within the imported table to have a padding

[macropod]

Sorry I require all cells within the imported table to have a padding

And that is exactly what the change I suggested does.

[paddysheeran]

Sorry you change doesn't work in my script - thanks for trying anyway

[macropod]

It does when I run the snippet you posted. Of course, without seeing the snippet in its context, there's no way of knowing what it applies to ...

[fumei]

[VBA]
Dim objTable As Table
Set objTable = t.Tables(1)
With objTable
.TopPadding = 2
.BottomPadding = 2
.LeftPadding = 2
.RightPadding = 2
End With [/VBA]