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

mrwad

New Member
Joined
Oct 16, 2018
Messages
47
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:
Code:
    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:

Code:
     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

Code:
             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:
Code:
    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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,130,126
Messages
5,640,270
Members
417,132
Latest member
Srw123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top