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

[TheNewVbaDev]

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 :

 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
Range("A2").Select
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
doc.Close
i = i + 1

Next NomCell
wd.Quit

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 !