Hi all,
I managed to write/copy and paste some VBA code a few years ago to create a multiple word documents based on template word document files and a long list of students, their teacher and their group name is excel. We have 1,200 records.
It works and does what is needs to do but it takes about 3 hours to make all the reports. I wondered if I had done anything that is just not good VBA practice. Is there a way to quicken it up?
I appreicate the time anyone spends looking into this.
John
*********
Sub Report_Generator()
Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim TeacherName As String
Dim GroupName As String
Dim StudentName As String
Dim Template As String
Dim FileYear As String
Dim Answer As Integer
Dim StartTime As Double
Dim MinutesElapsed As String
Answer = MsgBox("Are you sure you are ready to do this? Have you done all the necessary checks? Remember this will take a LONG time.", vbYesNo + vbQuestion, "Empty Sheet")
If Answer = vbNo Then
Exit Sub
Else
'makes partent reports
End If
FileYear = InputBox("What academic year is it? e.g. 17-18")
StartTime = Timer
Sheets("Data").Select
FinalRow = Range("A9999").End(xlUp).Row
For I = 2 To FinalRow
Sheets("Data").Select
Range("A" & I).Copy Destination:=Sheets("Template").Range("A1")
Range("B" & I & ":E" & I).Copy
Sheets("Template").Select
Range("A2").PasteSpecial Transpose:=True
Range("A1:A3").Copy
StudentName = Sheets("Template").Range("A1").Text
GroupName = Sheets("Template").Range("A2").Text
TeacherName = Sheets("Template").Range("A3").Text
Template = Sheets("Template").Range("A4").Text
If Dir((ActiveWorkbook.Path) & "/" & TeacherName, vbDirectory) = "" Then
MkDir (ActiveWorkbook.Path) & "/" & TeacherName
Else
End If
If Dir((ActiveWorkbook.Path) & "/" & TeacherName & "/" & GroupName, vbDirectory) = "" Then
MkDir (ActiveWorkbook.Path) & "/" & TeacherName & "/" & GroupName
Else
End If
'Movers and flyers
If Template = "EVALUATION REPORT Movers & Flyers" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT Movers & Flyers.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & "" & " " & FileYear
End With
End If
'1 primary
If Template = "EVALUATION REPORT 1st Primary" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT 1st Primary.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'infants
If Template = "EVALUATION REPORT Infants" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT Infants.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'B1 blast
If Template = "EVALUATION REPORT PET1 BLAST" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT PET1 BLAST.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'PET2 and 3
If Template = "EVALUATION REPORT PET23" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT PET23.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'´FCE 1
If Template = "EVALUATION REPORT FCE1" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT FCE1.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'FCE 2
If Template = "EVALUATION REPORT FCE2" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT FCE2.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'FCE3
If Template = "EVALUATION REPORT FCE34" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT FCE34.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'CAE1
If Template = "EVALUATION REPORT CAE1" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT CAE1.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'CAE23
If Template = "EVALUATION REPORT CAE23" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT CAE23.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'CPE
If Template = "EVALUATION REPORT CPE" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT CPE.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'starters
If Template = "EVALUATION REPORT Starters" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT Starters.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'KET
If Template = "EVALUATION REPORT KET" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT KET.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
Call wApp.Quit
Next I
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
'Notify user in seconds
MsgBox "To create all these reports took " & MinutesElapsed, vbInformation
End Sub
I managed to write/copy and paste some VBA code a few years ago to create a multiple word documents based on template word document files and a long list of students, their teacher and their group name is excel. We have 1,200 records.
It works and does what is needs to do but it takes about 3 hours to make all the reports. I wondered if I had done anything that is just not good VBA practice. Is there a way to quicken it up?
I appreicate the time anyone spends looking into this.
John
*********
Sub Report_Generator()
Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim TeacherName As String
Dim GroupName As String
Dim StudentName As String
Dim Template As String
Dim FileYear As String
Dim Answer As Integer
Dim StartTime As Double
Dim MinutesElapsed As String
Answer = MsgBox("Are you sure you are ready to do this? Have you done all the necessary checks? Remember this will take a LONG time.", vbYesNo + vbQuestion, "Empty Sheet")
If Answer = vbNo Then
Exit Sub
Else
'makes partent reports
End If
FileYear = InputBox("What academic year is it? e.g. 17-18")
StartTime = Timer
Sheets("Data").Select
FinalRow = Range("A9999").End(xlUp).Row
For I = 2 To FinalRow
Sheets("Data").Select
Range("A" & I).Copy Destination:=Sheets("Template").Range("A1")
Range("B" & I & ":E" & I).Copy
Sheets("Template").Select
Range("A2").PasteSpecial Transpose:=True
Range("A1:A3").Copy
StudentName = Sheets("Template").Range("A1").Text
GroupName = Sheets("Template").Range("A2").Text
TeacherName = Sheets("Template").Range("A3").Text
Template = Sheets("Template").Range("A4").Text
If Dir((ActiveWorkbook.Path) & "/" & TeacherName, vbDirectory) = "" Then
MkDir (ActiveWorkbook.Path) & "/" & TeacherName
Else
End If
If Dir((ActiveWorkbook.Path) & "/" & TeacherName & "/" & GroupName, vbDirectory) = "" Then
MkDir (ActiveWorkbook.Path) & "/" & TeacherName & "/" & GroupName
Else
End If
'Movers and flyers
If Template = "EVALUATION REPORT Movers & Flyers" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT Movers & Flyers.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & "" & " " & FileYear
End With
End If
'1 primary
If Template = "EVALUATION REPORT 1st Primary" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT 1st Primary.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'infants
If Template = "EVALUATION REPORT Infants" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT Infants.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'B1 blast
If Template = "EVALUATION REPORT PET1 BLAST" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT PET1 BLAST.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'PET2 and 3
If Template = "EVALUATION REPORT PET23" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT PET23.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'´FCE 1
If Template = "EVALUATION REPORT FCE1" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT FCE1.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'FCE 2
If Template = "EVALUATION REPORT FCE2" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT FCE2.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'FCE3
If Template = "EVALUATION REPORT FCE34" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT FCE34.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'CAE1
If Template = "EVALUATION REPORT CAE1" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT CAE1.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'CAE23
If Template = "EVALUATION REPORT CAE23" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT CAE23.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'CPE
If Template = "EVALUATION REPORT CPE" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT CPE.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'starters
If Template = "EVALUATION REPORT Starters" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT Starters.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
'KET
If Template = "EVALUATION REPORT KET" Then
Set wApp = CreateObject("Word.Application")
'wApp.Visible = True
Set wDoc = wApp.Documents.Open(ActiveWorkbook.Path & "" & "templates" & "/" & "EVALUATION REPORT KET.docx")
With wDoc
.Application.Selection.Find.Text = "<<NAME>>"
.Application.Selection.Find.Execute
.Application.Selection = StudentName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<GROUP>>"
.Application.Selection.Find.Execute
.Application.Selection = GroupName
.Application.Selection.EndOf
.Application.Selection.Find.Text = "<<TEACHER>>"
.Application.Selection.Find.Execute
.Application.Selection = TeacherName
.Application.Selection.EndOf
.SaveAs (ActiveWorkbook.Path & "" & TeacherName & "") & GroupName & "" & StudentName & " " & FileYear
End With
End If
Call wApp.Quit
Next I
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
'Notify user in seconds
MsgBox "To create all these reports took " & MinutesElapsed, vbInformation
End Sub