1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
| Macro1 Macro
'
Option Explicit
'Filepath to the data and template
Const FilePath As String = "C:\Users\aguy\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\aguy\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 |
Partager