Create a Macro to Export a PDF file from Excel data and bookmarks


New Member
Jul 28, 2021
Office Version
  1. 365
  1. Windows
Hey everyone,

I'm currently stuck within the development of a macro to transfer data from an Excel Sheet to a word template with bookmark.
For now, the main of the macro is working, the data are well transfered to the Template but I have several issues that I cannot fix since three days, and I swear, I tried a lot since.
Here my issues ( you can see my trying in comment in the code below)
1/ When I want to save the document in PDF, my macro will keep the information paste in the template after closing the docx, therefore, the next pdf file will have the information of two line instead of one. I tried to use an export function but I had the same result. Do you have any idea of how I could change my code to let the template empty after registered to pdf file ?

2/ To name the PDF file, I defined different variable but it seems to not work, indeed, it seems that the name is defined with the information of the N-1 cells instead of the information from the N cell, any idea to solve it ? Also, I cannot figure out how to put the YEAR information in the name of the file ( I have a compatibility error, I think it's because of the data format )

3/ To end, I want to set up a IF & Then to take into account only the line concerning the current year, I didn't find how to make it work efficiently, could you give me some suggestion ?

You can find my VBA code here :
VBA Code:
 Macro1 Macro
Option Explicit

'Filepath to the data and template
Const FilePath As String = "C:\Users\\OneDrive\Desktop\fiches\Templates\" & "Template macro.docx"""
Dim wd As New Word.Application
Dim NomCell As Range

Sub CreateWordDocuments()
'Defini a counter
Dim i As Integer
Dim prenom As String
Dim nom As String
Dim interviewyear As Date
i = 0
'create copy of Word in memory
Dim doc As Word.Document
wd.Visible = True
Dim NomRange As Range

'create a reference to all the people
Set NomRange = Range(ActiveCell, ActiveCell.End(xlDown))
'for each person in list
For Each NomCell In NomRange

'open a document in Word

Set doc = wd.Documents.Open("C:\Users\\OneDrive\Desktop\fiches\Templates\" & "Template macro.docx")

'go to each bookmark and type in details
'If Year(Cells(NomCell, 14)) >= Year(Date) Then

CopyCell "Age", 4
CopyCell "Ancienneté", 6
CopyCell "Projet", 9
CopyCell "Grade", 10
CopyCell "Rôle", 11
CopyCell "Date_de_départ_prévue", 12
CopyCell "Carrière_manager", 13
CopyCell "Motif_départ", 16
CopyCell "Motif_départ_2", 17
CopyCell "Points_positifs_expérience", 18
CopyCell "Point_négatifs_expérience", 19
CopyCell "Situation_future_entreprise_ou_autre", 20
CopyCell "Commentaire_RRH", 21
CopyCell "Prénom", 2
CopyCell "Site", 7
CopyCell "Service_line", 8
CopyCell "Nom", 1
CopyCell "Date_entretien_de_départ", 15
CopyCell "RRH_entretien", 16

prenom = Cells(NomCell, 2).Value
nom = Cells(NomCell, 3).Value
'interviewyear = Cells(NomCell, 15).Date

'save and close this document
doc.SaveAs2 FilePath & nom & " " & prenom & interviewyear & ".pdf", wdExportFormatPDF
i = i + 1

Next NomCell

MsgBox "The work is done, " & i & "  files had been created in " & FilePath & "!"

'End If
End Sub

Sub CopyCell(BookMarkNom As String, ColumnOffset As Integer)

'copy each cell to relevant Word bookmark
wd.Selection.Goto What:=wdGoToBookmark, Name:=BookMarkNom
wd.Selection.TypeText NomCell.Offset(0, ColumnOffset).Value

End Sub

I hope I will find help here !
Thanks to you and have a great summer day !

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Forum statistics

Latest member

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
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 "".
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