Exporting Multilevel Paragraphs

[SeveretTF]

Hi everyone,

I currently have a spreadsheet with three columns containing a paragraph number, paragraph header text, and paragraph body text. I was wondering if there was a way to export this data into a multilevel list into Word that preserves the numbering defined in the Excel sheet. I've come across terms like Mail Merge and VBA, but I'm not sure if they would allow me to create something like this.

Thanks!

 

[6StringJazzer]

It would help if you showed what your Excel data looks like, and then how you want it to look in Word afterwards. I am not sure what kind of "multilevel list" you have in mind here.

Can you just copy the Excel and paste as a table in Word? Then you can convert the "Table to Text" using "paragraph" as the separator.

 

[SeveretTF]

The spreadsheet has columns set up in this manner:

Para #	Header	Body
1	Heading Level 1	Text for point 1
1.1	Heading Level 2	Text for point 1.1
1.1.1	Heading Level 3	Text for point 1.1.1
1.1.2	Heading Level 3	Text for point 1.1.2

and the goal is to have an output into Word that resembles this:

I didn't know about the table to text feature, but I'll play around with it. Hopefully this clears things up.

 

[6StringJazzer]

The table to text feature will not give you that result; it either would put the heading number, heading text, and paragraph text on the same line, or it would put the number, heading text, and paragraph text on three lines. In addition it will not change the numbers into heading levels.

For what you want to do, VBA is required. VBA is the programming language that Microsoft Office applications use to create macros. I have an inkling of how this might be done but it would require more research and experimentation than I am able to do right now.

 

[SeveretTF]

The table to text feature will not give you that result; it either would put the heading number, heading text, and paragraph text on the same line, or it would put the number, heading text, and paragraph text on three lines. In addition it will not change the numbers into heading levels.

For what you want to do, VBA is required. VBA is the programming language that Microsoft Office applications use to create macros. I have an inkling of how this might be done but it would require more research and experimentation than I am able to do right now.

Okay, it is reassuring to know what direction to look now. Thank you!

 

[Macropod]

For example:

Sub Demo()
' Note: A reference to the Microsoft Word # Object Library is required,
' set via Tools|References in the Excel VBE.
Dim WdApp As New Word.Application, WdDoc As Word.Document, XlSht As Excel.Worksheet
Dim lRow As Long, r As Long, h As Long, StrHd As String, StrTxt As String
Set XlSht = ThisWorkbook.Worksheets("Sheet1")
lRow = XlSht.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
With WdApp
  Set WdDoc = .Documents.Add
  Call ApplyMultiLevelHeadingNumbers(WdDoc)
  For r = 2 To lRow
    h = UBound(Split(XlSht.Cells(r, 1).Text, ".")) + 1
    StrHd = XlSht.Cells(r, 2).Text
    StrTxt = XlSht.Cells(r, 3).Text
    With WdDoc
      .Paragraphs.Last.Style = "Heading " & h
      .Paragraphs.Last.Range.Text = StrHd
      .Range.InsertAfter vbCr
      .Paragraphs.Last.Style = wdStyleNormal
      If StrTxt <> "" Then
        .Paragraphs.Last.Range.Text = StrTxt
        .Range.InsertAfter vbCr
      End If
    End With
  Next
  With WdDoc
    Do While .Characters.Last.Previous = vbCr
      .Characters.Last.Previous.Delete
    Loop
  End With
  .Visible = True
End With
WdDoc.Activate
Set WdDoc = Nothing: Set WdApp = Nothing: Set XlSht = Nothing
End Sub

Sub ApplyMultiLevelHeadingNumbers(WdDoc As Word.Document)
Dim LT As ListTemplate, i As Long
Set LT = WdDoc.ListTemplates.Add(OutlineNumbered:=True)
For i = 1 To 9
  With LT.ListLevels(i)
    .NumberFormat = Choose(i, "%1", "%1.%2", "%1.%2.%3", "%1.%2.%3.%4", "%1.%2.%3.%4.%5", "%1.%2.%3.%4.%5.%6", "%1.%2.%3.%4.%5.%6.%7", "%1.%2.%3.%4.%5.%6.%7.%8", "%1.%2.%3.%4.%5.%6.%7.%8.%9")
    .TrailingCharacter = wdTrailingTab
    .NumberStyle = wdListNumberStyleArabic
    .NumberPosition = CentimetersToPoints(0)
    .Alignment = wdListLevelAlignLeft
    .TextPosition = InchesToPoints(0.5 + i * 0.5)
    .ResetOnHigher = True
    .StartAt = 1
    .LinkedStyle = "Heading " & i
  End With
  With WdDoc.Styles("Heading " & i)
    .ParagraphFormat.LeftIndent = InchesToPoints(i * 0.5 - 0.5)
    .ParagraphFormat.FirstLineIndent = InchesToPoints(-0.5)
    .Font.Name = "Gill Sans MT"
    .Font.Italic = False
    .Font.Bold = False
    .Font.ColorIndex = wdAuto
    .Font.Size = 17 - i
  End With
Next
End Sub

 

[SeveretTF]

For example:

Sorry to trouble with my ignorance, but it seems I'm having trouble running the code you provided. This is my first time using VBA, but here is what I did:

1. In the Excel sheet, I enabled the developer tab and opened Visual Basic.
2. In the VBE, I went to Insert > Module and pasted the code.
3. I selected the Microsoft Word Object Library in Tools > References.
4. I click Run

It gives this error:

Going into debug highlights this line:

I'm speculating that Word didn't have a paragraph style under this name, but I see options for Headings 1–2 when I open a blank Word doc. I know I'm swimming quite blindly, so apologies if these are elementary mistakes, and thank you to everyone so far for their input.

 

[Macropod]

Evidently, then, you're using a system with a non-English Office installation. German uses heading Styles with names like 'Uberschrift 1', 'Uberschrift 2', etc., whilst French uses 'Titre 1', 'Titre 2', etc. And so on. To tailor the code to suit whatever language you're using, you could replace the 'Heading ' strings with whatever names are applicable to your language (note the need to keep the space between the word and number).

Alternatively, if the code is to be used on systems configured for a range of different languages, you could use:

Sub Demo()
' Note: A reference to the Microsoft Word # Object Library is required,
' set via Tools|References in the Excel VBE.
Dim WdApp As New Word.Application, WdDoc As Word.Document, XlSht As Excel.Worksheet
Dim lRow As Long, r As Long, h As Long, StrHd As String, StrTxt As String
Set XlSht = ThisWorkbook.Worksheets("Sheet1")
lRow = XlSht.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
With WdApp
  Set WdDoc = .Documents.Add
  Call ApplyMultiLevelHeadingNumbers(WdDoc)
  For r = 2 To lRow
    h = -2 - UBound(Split(XlSht.Cells(r, 1).Text, "."))
    StrHd = XlSht.Cells(r, 2).Text
    StrTxt = XlSht.Cells(r, 3).Text
    With WdDoc
      .Paragraphs.Last.Style = h
      .Paragraphs.Last.Range.Text = StrHd
      .Range.InsertAfter vbCr
      .Paragraphs.Last.Style = wdStyleNormal
      If StrTxt <> "" Then
        .Paragraphs.Last.Range.Text = StrTxt
        .Range.InsertAfter vbCr
      End If
    End With
  Next
  With WdDoc
    Do While .Characters.Last.Previous = vbCr
      .Characters.Last.Previous.Delete
    Loop
  End With
  .Visible = True
End With
WdDoc.Activate
Set WdDoc = Nothing: Set WdApp = Nothing: Set XlSht = Nothing
End Sub

Sub ApplyMultiLevelHeadingNumbers(WdDoc As Word.Document)
Dim LT As ListTemplate, i As Long
Set LT = WdDoc.ListTemplates.Add(OutlineNumbered:=True)
For i = 1 To 9
  With LT.ListLevels(i)
    .NumberFormat = Choose(i, "%1", "%1.%2", "%1.%2.%3", "%1.%2.%3.%4", "%1.%2.%3.%4.%5", "%1.%2.%3.%4.%5.%6", "%1.%2.%3.%4.%5.%6.%7", "%1.%2.%3.%4.%5.%6.%7.%8", "%1.%2.%3.%4.%5.%6.%7.%8.%9")
    .TrailingCharacter = wdTrailingTab
    .NumberStyle = wdListNumberStyleArabic
    .NumberPosition = CentimetersToPoints(0)
    .Alignment = wdListLevelAlignLeft
    .TextPosition = InchesToPoints(0.5 + i * 0.5)
    .ResetOnHigher = True
    .StartAt = 1
    .LinkedStyle = WdDoc.Styles(-1 - i)
  End With
  With WdDoc.Styles(-1 - i)]
    .ParagraphFormat.LeftIndent = InchesToPoints(i * 0.5 - 0.5)
    .ParagraphFormat.FirstLineIndent = InchesToPoints(-0.5)
    .Font.Name = "Gill Sans MT"
    .Font.Italic = False
    .Font.Bold = False
    .Font.ColorIndex = wdAuto
    .Font.Size = 17 - i
  End With
Next
End Sub

Note the changes to the lines referencing the heading Styles. This allows the code to use Word's built-in heading Style enumeration, which works regardless of the installation language.