Automated Student Mark Sheet

In this article, we will learn to automate Microsoft Word from Excel with the help of Visual Basic Language. To learn the Word Automation, we will consider a very practical example i.e. Creating Student’s Mark Sheet in MS Word from Excel Table.

If you find that you are constantly copying and pasting data into Microsoft Word in a specific format then you can use the code and logic, which we are going to use here.

Before walking through the macro, it is important to go over a few steps.

Please create a Word file for Students mark sheet as mentioned in below image or download the template used in this tutorial.

Once you create or download the word file, you need to create bookmarks tagging the location where you want your Excel data to be copied.

In current word file, we need total 17 bookmarks to transfer Student Name, Registration Number, Program Name, Examination Date, Grade, Statistics Marks, Statistics Result, Excel Marks, Excel Result, VBA Marks, VBA Result, SQL Marks, SQL Result, Power BI Mark, Power BI Result, Grand Total Marks and Percentage.

To create a bookmark in a Word document, place your cursor where you want the bookmark then go to Insert Tab and select Bookmark available under the Links group. This will open the Bookmark dialog box where you can provide the name of bookmark and click on Add button.

Let us follow the same step as mentioned above and create bookmark for ‘Name’.

In the same way, just create rest of the 16 Bookmarks required for this automation.

Please use the below mentioned Bookmarks name. We will use these names in VBA code while doing automation.

1. Name
2. Registration_Number
3. Program_Name
4. Examination_Date
5. Grade
6. Statistics_Marks
7. Statistics_Result
8. Excel_Marks
9. Excel_Result
10. VBA_Marks
11. VBA_Result
12. SQL_Marks
13. SQL_Result
14. PowerBI_Marks
15. PowerBI_Result
16. GrandTotal
17. Percentage

Once, you create all the Bookmarks save the word file in the name ‘Marksheet Template.docx‘ at the same location where you are going to keep MS Excel file. Now, close the file after saving it.

Now, open the Excel Application and create a blank worksheet. Save the file with the name ‘Automating Word from Excel.xlsm’ in the same folder where you have kept Word file.

Create the table with required data as mentioned in below image. Please keep the cell reference as it is otherwise your code will not work (if you are following the same example).

Now, insert a rounded rectangle and give the caption as ‘Prepare Marksheet’. We will assign macro on this button to create mark sheet once coding will be done.

Let us jump to Visual Basic Application Window to start coding. To open VBA Window, go to Developer Tab and Click on Visual Basic button available under Code group.

In Visual Basic window, insert a blank module. Module would be required to write the code.

To insert a module, click on Insert Menu and then select Module.

As we are going to automate MS Word from Excel hence, we need to provide the reference of MS Word Object library. To add the reference, click on Tools menu and then select References…

In references window, select ‘Microsoft Word 16.0 Object Library’ from the available references and then click on Okay.

Once you done with adding references then double click on Module1 to open the code window.

In Code window, just copy and paste the below code.

Option Explicit
Sub SendToWord()
'Declare Variables for This Automation

Dim wd As Word.Application ' Word Applicaton
Dim wdDOC As Word.Document 'Word Document
Dim iRow As Long 'Variable to hold the starting row and loop through all records in the table
Dim PercentageScore As Variant 'variable to hold the percentage value

Dim sh As Worksheet ' worksheet variable to refer the sheet where scores are available

'Start Word and add a new document
Set wd = New Word.Application

'Set worksheet where table is avaialble
Set sh = ThisWorkbook.Sheets("Student Scores")

'Intialize iRow with 6 as data are starting from row number 6 in table
iRow = 6

Do While sh.Range("A" & iRow).Value <> ""

    'Opening the word template where bookmarks have been added
    Set wdDOC = wd.Documents.Open(ThisWorkbook.Path & "\Marksheet Template.docx")
    wd.Visible = False

    'Name
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="Name"
    wd.Selection.TypeText Text:=sh.Range("A" & iRow).Value

    'Registration_Number
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="Registration_Number"
    wd.Selection.TypeText Text:=sh.Range("B" & iRow).Value

    'Program_Name
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="Program_Name"
    wd.Selection.TypeText Text:=sh.Range("C" & iRow).Value

    'Examination_Date
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="Examination_Date"
    wd.Selection.TypeText Text:=Format(sh.Range("D" & iRow).Value, "dd-mmm-yy")

    'Grade
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="Grade"
    wd.Selection.TypeText Text:=sh.Range("P" & iRow).Value

    'Statistics_Marks
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="Statistics_Marks"
    wd.Selection.TypeText Text:=sh.Range("E" & iRow).Value

    'Statistics_Result
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="Statistics_Result"
    wd.Selection.TypeText Text:=sh.Range("F" & iRow).Value

    'Excel_Marks
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="Excel_Marks"
    wd.Selection.TypeText Text:=sh.Range("G" & iRow).Value

    'Excel_Result
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="Excel_Result"
    wd.Selection.TypeText Text:=sh.Range("H" & iRow).Value

    'VBA_Marks
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="VBA_Marks"
    wd.Selection.TypeText Text:=sh.Range("I" & iRow).Value

    'VBA_Result
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="VBA_Result"
    wd.Selection.TypeText Text:=sh.Range("J" & iRow).Value

    'SQL_Marks
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="SQL_Marks"
    wd.Selection.TypeText Text:=sh.Range("K" & iRow).Value

    'SQL_Result
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="SQL_Result"
    wd.Selection.TypeText Text:=sh.Range("L" & iRow).Value

    'PowerBI_Marks
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="PowerBI_Marks"
    wd.Selection.TypeText Text:=sh.Range("M" & iRow).Value

    'PowerBI_Result
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="PowerBI_Result"
    wd.Selection.TypeText Text:=sh.Range("N" & iRow).Value

    'GrandTotal
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="GrandTotal"
    wd.Selection.TypeText Text:=sh.Range("O" & iRow).Value

    'Calculating Percentage
    PercentageScore = Format(sh.Range("O" & iRow).Value / 500, "0.0%")

    'Percentage
    wd.Selection.GoTo What:=wdGoToBookmark, Name:="Percentage"
    wd.Selection.TypeText Text:=PercentageScore

    'Clear the Bookmarks from this file

    On Error Resume Next

    wdDOC.Bookmarks("Name").Delete
    wdDOC.Bookmarks("Registration_Number").Delete
    wdDOC.Bookmarks("Program_Name").Delete
    wdDOC.Bookmarks("Examination_Date").Delete
    wdDOC.Bookmarks("Grade").Delete
    wdDOC.Bookmarks("Statistics_Marks").Delete
    wdDOC.Bookmarks("Statistics_Result").Delete
    wdDOC.Bookmarks("Excel_Marks").Delete
    wdDOC.Bookmarks("Excel_Result").Delete
    wdDOC.Bookmarks("VBA_Marks").Delete
    wdDOC.Bookmarks("VBA_Result").Delete
    wdDOC.Bookmarks("SQL_Marks").Delete
    wdDOC.Bookmarks("SQL_Result").Delete
    wdDOC.Bookmarks("PowerBI_Marks").Delete
    wdDOC.Bookmarks("PowerBI_Result").Delete
    wdDOC.Bookmarks("GrandTotal").Delete
    wdDOC.Bookmarks("Percentage").Delete

    'Save the document with Student's name
    wdDOC.SaveAs2 (ThisWorkbook.Path & "\" & sh.Range("A" & iRow).Value & ".docx")

    'Close the document
    wdDOC.Close

    Set wdDOC = Nothing

    iRow = iRow + 1

Loop

wd.Quit
Set wd = Nothing

MsgBox "Mark-sheets have been prepared for all the students."

End Sub

Now move to Excel Application and assign the macro on button available on top of the table.

You can test this application with the data available in table.

Download Excel and Word files used in this tutorial.