Export data from Excel to Word step by step including footer and header

[mrwad]

Hello everyone,

I have Excel workbook where I have the data. My current code is running through worksheet and is creating good looking pdf document (looks like it was created in Word). The problem is that I would like to built the same document in Word with proper formatting. Converting from pdf to Word is not an option because basically you will get Excel table in Word as an output as file originally was created using Excel.
I can create embedded Word template document in my Excel workbook and insert bookmarks to header and footer (as I understood there is no other proper way to make them work in Excel to Word export). Then with code I can input data to header and footer from Excel. After that I would like the other code go through my excel workbook and copy paste cells with proper formatting. By proper formatting I mean:

In my current Excel worksheet

Column A paragraph numbers (1, 2, 3 or 1.1, 1.2, 1.3 or 1.1.1, 1.1.2, 1.1.3)
Column B actual text
Column D tips for code to recognize what is what (title, main, sub, sub-sub, par)
- title is title and it has font of 14 Arial in Word it can be for example Heading 1 in Word
- main is main paragraph and my current code is placing numbers to Column A. So first main is 1, second main is 2, third main is 3 and etc.
- sub is working as main but numbers go like 1.1, 1.2, 1.3 etc.
- sub-sub is working as sub but numbers go like 1.1.1, 1.1.2, 1.1.3
- par is actual paragraph text

I use this one for numbering in Excel:

    For Each Cl12 In .Range("D14", .Range("D" & Rows.Count).End(xlUp))      Select Case LCase(Cl12.Value)
     Case "title"
            t12 = t12 + 1: s12 = 0: ss12 = 0
            Cl12.Offset(0, -2).Value = UCase(Cl12.Offset(0, -4).Value)
            Cl12.Offset(0, -2).Font.Size = 14
            Cl12.Offset(0, -2).Font.Bold = True
         Case "main"
            m12 = m12 + 1: s12 = 0: ss12 = 0
            Cl12.Offset(, -3).Value = m12
            Cl12.Offset(0, -2).Value = UCase(Cl12.Offset(0, -4).Value)
            Cl12.Offset(0, -2).Font.Bold = True
            Cl12.Offset(0, -3).Font.Bold = True
         Case "sub"
            s12 = s12 + 1: ss12 = 0
            Cl12.Offset(, -3) = m12 & "." & s12
            Cl12.Offset(0, -2).Font.Bold = True
            Cl12.Offset(0, -3).Font.Bold = True
         Case "sub-sub"
            ss12 = ss12 + 1
            Cl12.Offset(, -3) = m12 & "." & s12 & "." & ss12
            Cl12.Offset(0, -2).Font.Bold = True
            Cl12.Offset(0, -3).Font.Bold = True
      End Select
   Next Cl12

Also sometimes as values changing I have empty rows as I have formulas like =if(I="";"";N5)
Code should skip these empty cells and operate with cells that are not "" = empty.

Typical output of my Excel workbook:

     A        B       C        D
1           text              title
2           text              main
3           text              par
4           text              main
5           text              par
6           text              sub
7           text              par
8           text              main
9           text              par
10         
11         text              main
12         text              par
etc.

Output in Word

             text              title   (Heading 1)
1           text              main   (Heading 2)
             text              par   (Normal)
2           text              main   (Heading 2)
             text              par   (Normal)
2.1        text              sub   (Heading 3)
             text              par   (Normal)
3           text              main   (Heading 2)
             text              par   (Normal)
4           text              main   (Heading 2)
             text              par   (Normal)
etc.

This one is used for opening embedded Word document and inserting data from cells to bookmarks:

    Set wSystem = Worksheets("Templates")''The shape holding the object from 'Create from file'
''Object 2 is the name of the shape
Set sh = wSystem.Shapes("Object 1")
''Activate the contents of the object
sh.OLEFormat.Activate
''The OLE Object contained
Set objOLE = sh.OLEFormat.Object
''This is the bit that took time
Set objWord = objOLE.Object
 
 objWord.Bookmarks.Item("ProjectName1").Range.Text = ThisWorkbook.Sheets("MAIN").Range("D15").Value
 objWord.Bookmarks.Item("ProjectName2").Range.Text = ThisWorkbook.Sheets("MAIN").Range("D16").Value
 
  objWord.Bookmarks.Item("ProjectNumberName").Range.Text = ThisWorkbook.Sheets("MAIN").Range("B18").Value & ":"
  objWord.Bookmarks.Item("ProjectNumber").Range.Text = ThisWorkbook.Sheets("MAIN").Range("D18").Value


objWord.Application.Visible = False
 
''Easy enough
    objWord.SaveAs2 ActiveWorkbook.Path & "\" & Sheets("Other Data").Range("AN2").Value & ", " & Sheets("Other Data").Range("AN7").Value & "_" & Sheets("Other Data").Range("AN8").Value & "_" & Sheets("Other Data").Range("AX2").Value & ".pdf", 17


sh.OLEFormat.Delete