Make a Word Document from Excel

[Alex St-Pierre]

Hi,
I have a special report that is generated from Excel. All tables are done
inside Excel and export thereafter. For now, I make a mailmerge with a Word
Template and after that, the table are exported. Instead of using a
MailMerge, I would like to program everything from Excel to add flexibility
into the texts. I’m wondering if someone have an example of an excel file
that format a word document (format style, add text,make the table of content
at the end, etc.) or a good reference?
Thanks a lot!

 

[Shauna Kelly]

Hi Alex

This will get you going:
Control Word from Excel
http://www.word.mvps.org/FAQs/InterDev/ControlWordFromXL.htm

Once you have a reference to the Word application, you can manipulate Word
as well from Excel as you can from within Word itself.

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[Alex St-Pierre]

Thanks Shauna,
I already have a program that create table into Word and I would like to
modify it to format the document, create the style for paragrah text, section
title, ..., add text, format the table of contents. For now, I'm using the
programmation bellow to add 2 lines.. Maybe there's a better way to do that!
There's a lot of information on your website to help me understand Word.
Thanks!
Alex
With appWord.Selection.ParagraphFormat
.LeftIndent = CentimetersToPoints(0)
.RightIndent = CentimetersToPoints(0)
.SpaceBefore = 2
.SpaceBeforeAuto = False
.SpaceAfter = 2
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceSingle
.Alignment = wdAlignParagraphLeft
.WidowControl = True
.KeepWithNext = True
.KeepTogether = False
.PageBreakBefore = False
.NoLineNumber = False
.Hyphenation = True
.FirstLineIndent = CentimetersToPoints(0)
.OutlineLevel = wdOutlineLevelBodyText
.CharacterUnitLeftIndent = 0
.CharacterUnitRightIndent = 0
.CharacterUnitFirstLineIndent = 0
.LineUnitBefore = 0
.LineUnitAfter = 0
End With
appWord.Selection.Font.Name = "Times New Roman"
appWord.Selection.Font.Size = 10
appWord.Selection.Font.Italic = wdNone
appWord.Selection.Font.Italic = wdToggle
appWord.Selection.Font.Bold = wdNone
appWord.Selection.TypeText Text:=oXlRng.Cells(1, 1)
appWord.Selection.TypeText Text:=Chr(13)
With appWord.Selection.ParagraphFormat
.LeftIndent = CentimetersToPoints(0)
.RightIndent = CentimetersToPoints(0)
.SpaceBefore = 0
.SpaceBeforeAuto = False
.SpaceAfter = 5
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceSingle
.Alignment = wdAlignParagraphLeft
.WidowControl = True
.KeepWithNext = True
.KeepTogether = False
.PageBreakBefore = False
.NoLineNumber = False
.Hyphenation = True
.FirstLineIndent = CentimetersToPoints(0)
.OutlineLevel = wdOutlineLevel1
.CharacterUnitLeftIndent = 0
.CharacterUnitRightIndent = 0
.CharacterUnitFirstLineIndent = 0
.LineUnitBefore = 0
.LineUnitAfter = 0
End With
appWord.Selection.Font.Name = "Arial"
appWord.Selection.Font.Italic = wdNone
appWord.Selection.Font.Bold = wdNone
appWord.Selection.Font.Bold = wdToggle
appWord.Selection.TypeText Text:=oXlRng.Cells(2, 1).Text
appWord.Selection.TypeText Text:=Chr(13)

 

[Shauna Kelly]

Hi Alex

Three immediate things may help here:

1. Jay Freedman has an excellent article about 'cleaning up' VBA code at
How to modify a recorded macro
http://www.word.mvps.org/FAQs/MacrosVBA/ModifyRecordedMacro.htm

2. Avoid the Selection object. It's better, and almost always faster, to set
a Range object and manipulate it.

3. Use styles. If you modify the built-in styles to suit your needs, you can
then use a construction like:

Dim oRange As Word.Range

Set oRange = Selection.Paragraphs(1).Range
With oRange
.Style = wdStyleBodyText
.InsertAfter "My text"
End With


Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[Alex St-Pierre]

Thanks!
I have a program that manage excel tables and put the data into word. I
adjust the format into word depending of the excel format. To do that, I
merge the cells into Excel as it will be in Word. So the number of cells
inside a row is always the same. The program works very slow when I create
the table into word. I tried to hide the word application but the table
formatted is not the same?? it's very strange.
Thank you!
Alex
appWord.Selection.PasteExcelTable LinkedToExcel:=False,
WordFormatting:=False, RTF:=False
Set tbl = docWord2.Tables(iTabNb)
docWord2.Bookmarks(1).Delete
End If

tbl.Select 'Set standard table format


appWord.Activate 'voir si toujours erreur...
With appWord.Selection.ParagraphFormat
.LeftIndent = CentimetersToPoints(0)
.RightIndent = CentimetersToPoints(0)
.SpaceBefore = 2
.SpaceBeforeAuto = False
.SpaceAfter = 2
.SpaceAfterAuto = False
.LineSpacingRule = wdLineSpaceSingle
End With

'3- Adjust column width

With tbl.Rows
.LeftIndent = 0
End With

' With ActiveDocument.PageSetup
' UsableWidth = .PageWidth - .LeftMargin - .RightMargin
' End With

UsableWidth = 432 '6 inches
TableWidth = 0
iCol = oXlRng.Columns.Count 'Expect no merge cell on the first line
UsableWidth = UsableWidth - iCol * 5.6 'each column have a 5.6 width
margin
For i = 1 To iCol
TableWidth = TableWidth + oXlRng.Columns(i).Width
Next i

' MsgBox ("iTab:" & iTab & " TableWidth:" & TableWidth & "
UsableWidth:" & UsableWidth)
'Verify potential problems

If TableWidth > UsableWidth Then MsgBox ("Table Width > Word Space for
table #" & iTab)

'Enlarge table to fit 100% page
For i = 1 To iCol
ColWidth(i) = 5.6 + oXlRng.Columns(i).Width '* (UsableWidth /
TableWidth)
Next i

For i = 1 To oXlRng.Rows.Count
c = 1
j = 1
tbl.Cell(i, 1).Select
appWord.Selection.SelectRow
iCell(i) = appWord.Selection.Cells.Count
'iCell(i) = tbl.Rows(i).Cells.Count 'POUR EXCEL
Do Until c > iCell(i)
CellWidth(i, j) = 0
k = oXlRng.Cells(i, j).MergeArea.Count 'nombre de fusion
For n = 1 To k
CellWidth(i, j) = CellWidth(i, j) + ColWidth(j + n - 1)
Next n
r(i, c) = j
c = c + 1
j = j + k
Loop
Next i

For i = 1 To tbl.Rows.Count
For j = 1 To iCell(i)
Set pCell = tbl.Cell(i, j)
pCell.Width = CellWidth(i, r(i, j))
Next j
' tbl.Rows(i).HeightRule = wdRowHeightAtLeast
' tbl.Rows(i).Height = CentimetersToPoints(0.56 * oXlRng.Rows(i).Height /
12.75)
Next i

For i = 1 To tbl.Rows.Count

Next i

'5- Add borders
With tbl
.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
.Borders(wdBorderRight).LineStyle = wdLineStyleNone
.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
.Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
.Borders(wdBorderVertical).LineStyle = wdLineStyleNone

For i = 1 To tbl.Rows.Count
For j = 1 To iCell(i)

With oXlRng.Cells(i, r(i, j))
With .Borders(xlEdgeTop)
If .LineStyle = xlContinuous Then
tbl.Cell(i, j).Borders(wdBorderTop).LineStyle =
wdLineStyleSingle
If .Weight = xlThin Then
tbl.Cell(i, j).Borders(wdBorderTop).LineWidth =
wdLineWidth025pt
ElseIf .Weight = xlMedium Then 'Or .Weight = xlLarge Then
tbl.Cell(i, j).Borders(wdBorderTop).LineWidth =
wdLineWidth150pt
End If
Else
tbl.Cell(i, j).Borders(wdBorderTop).LineStyle =
wdLineStyleNone
End If
End With
With .Borders(xlEdgeBottom)
If .LineStyle = xlContinuous Then
tbl.Cell(i, j).Borders(wdBorderBottom).LineStyle =
wdLineStyleSingle
If .Weight = xlThin Then
tbl.Cell(i, j).Borders(wdBorderBottom).LineWidth =
wdLineWidth025pt
ElseIf .Weight = xlMedium Then
tbl.Cell(i, j).Borders(wdBorderBottom).LineWidth =
wdLineWidth150pt
End If
Else
tbl.Cell(i, j).Borders(wdBorderBottom).LineStyle =
wdLineStyleNone
End If
End With
With .Borders(xlEdgeLeft)
If .LineStyle = xlContinuous Then
tbl.Cell(i, j).Borders(wdBorderLeft).LineStyle =
wdLineStyleSingle
If .Weight = xlThin Then
tbl.Cell(i, j).Borders(wdBorderLeft).LineWidth =
wdLineWidth025pt
ElseIf .Weight = xlMedium Then
tbl.Cell(i, j).Borders(wdBorderLeft).LineWidth =
wdLineWidth150pt
End If
Else
tbl.Cell(i, j).Borders(wdBorderLeft).LineStyle =
wdLineStyleNone
End If
End With
With .Borders(xlEdgeRight)
If .LineStyle = xlContinuous Then
tbl.Cell(i, j).Borders(wdBorderRight).LineStyle =
wdLineStyleSingle
If .Weight = xlThin Then
tbl.Cell(i, j).Borders(wdBorderRight).LineWidth =
wdLineWidth025pt
ElseIf .Weight = xlMedium Then
tbl.Cell(i, j).Borders(wdBorderRight).LineWidth =
wdLineWidth150pt
End If
Else
tbl.Cell(i, j).Borders(wdBorderRight).LineStyle =
wdLineStyleNone
End If
End With
End With

sData = oXlRng.Cells(i, r(i, j))
If IsNumeric(sData) And sData <> "" Then
If sData <> 0 Then 'on veut pas formater les "-"
sFormat = oXlRng.Cells(i, r(i, j)).NumberFormat
If Left(sFormat, 15) = "#,##0_);(#,##0)" Or Left(sFormat, 15) =
"# ##0_-;(# ##0)" Or Left(sFormat, 15) = "#,##0_-;(#,##0)" Then
sFormat = "#,##0;(#,##0)"
End If
If sFormat <> "General" Then sData = Format(sData, sFormat)
If Right(sData, 1) = ")" Then
.Cell(i, j).Select
With appWord.Selection.ParagraphFormat
.RightIndent = CentimetersToPoints(-0.12)
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
End With
Else
.Cell(i, j).Select
With appWord.Selection.ParagraphFormat
.RightIndent = CentimetersToPoints(0)
.SpaceBeforeAuto = False
.SpaceAfterAuto = False
End With
End If ')"
.Cell(i, j).Range.Text = sData 'Transformation seulement
pour les nombres
ElseIf sData <> "" Then 'cause prob de mettre cette ligne avec
ligne suivante
' If Right(sData, 1) = "%" And IsNumeric(Left(sData, Len(sData) -
1)) And Len(sData) > 1 Then
' sData = Left(sData, Len(sData) - 1) & " %"
' .Cell(i, j).Range.Text = sData 'Transformation seulement pour
les nombres
' End If
End If
End If 'IsNumeric

Next j
Next i
End With