How To Create Automated Student Mark Sheet!!

In this article, we will discover ways to automate Microsoft Word from Excel with the assist of Visual Basic Language. To analyze the Word Automation, we will do not forget a totally practical example i.E. Creating Student’s Mark Sheet in MS Word from Excel Table.

If you find that you are continuously copying and pasting information into Microsoft Word in a particular format then you may use the code and logic, which we are going to use here.

Before walking via the macro, it is critical to go over some steps.

Please create a Word file for Students mark sheet as mentioned in the 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 the 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.

Bookmark Fields Highlighted with to create a bookmark in a Word document, region your cursor where you want the bookmark then visit Insert Tab and pick Bookmark to be had beneath the Links group. This will open the Bookmark dialog box wherein you could provide the name of the bookmark and click on the Add button.

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

Steps to Insert Bookmarks

In the same way, just create the 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 the required data as mentioned in the 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 a mark sheet once coding will be done.

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

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

To insert a module, click on Insert Menu and then select 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 reference window, select ‘Microsoft Word 16.0 Object Library’ from the available references and then click on Okay.

Add Microsoft Word 16.0 Object Library

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

Start Coding

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

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 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137

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 a 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 the button available on top of the table.

Assign Macro

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

Download Excel and Word files used in this tutorial.