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.

Prepare Word File and Marksheet Template

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.

Insert Bookmarks
Bookmark Fields Highlighted with red

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

Steps to Insert Bookmarks
Steps to Insert Bookmarks

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.

Prepare Excel File and Table
Prepare Excel File and Table

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.

Go To Visual Basic Window
Go To Visual Basic Window

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.

Insert a Blank Module
Insert a Blank 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.

Add Microsoft Word 16.0 Object Library
Add Microsoft Word 16.0 Object Library

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

Start Coding
Start Coding

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.

Assign Macro
Assign Macro

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

Download Excel and Word files used in this tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Captcha verification failed!
CAPTCHA user score failed. Please contact us!

Recommended Reads

Latest Articles