I will appreciate your help.In this article, we will learn to automate Microsoft Word from Excel with the help of Visual Basic Language. I want to calculate in a single cell the percentage number of students that pass the test and in a separate cell the percentage number of students that fail the test. I am working on excel with 22 number of students, each with mark score on cell rows.
Marksheet In Excel Download Lagu How
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.Download Lagu How To Create Automatic Mark Sheet Or Grade Sheet In Ms Excel 3.8 MB, Download MP3 & Video How To Create Automatic Mark Sheet Or Grade Sheet.Automated Student Mark Sheet. Build better customer relationships by tracking relevant details, and monitor sales activity to ensure you hit your sales and revenue goals every quarter. Track sales leads, assess communication efforts, and view your sales pipeline in its entirety with this CRM template. Baistiki, Mirpur-13, Dhaka, BangladeshExcel Sales and Marketing Templates. You don’t have to go anywhere to do our course. Creating Student’s Mark Sheet in MS Word from Excel Table.Tanvir Academy is an e-learning (online-based training) platform.
Now, close the file after saving it.Now, open the Excel Application and create a blank worksheet. We will use these names in VBA code while doing automation.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. Steps to Insert BookmarksIn the same way, just create rest of the 16 Bookmarks required for this automation.Please use the below mentioned Bookmarks name. 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’.
Start CodingIn Code window, just copy and paste the below code.Dim wd As Word.Application ' Word ApplicatonDim wdDOC As Word.Document 'Word DocumentDim iRow As Long 'Variable to hold the starting row and loop through all records in the tableDim PercentageScore As Variant 'variable to hold the percentage valueDim sh As Worksheet ' worksheet variable to refer the sheet where scores are availableSet sh = ThisWorkbook.Sheets("Student Scores")'Intialize iRow with 6 as data are starting from row number 6 in tableDo While sh.Range("A" & iRow).Value <> ""'Opening the word template where bookmarks have been addedSet wdDOC = wd.Documents.Open(ThisWorkbook.Path & "\Marksheet Template.docx")Wd.Selection.GoTo What:=wdGoToBookmark, Name:="Name"Wd.Selection.TypeText Text:=sh.Range("A" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="Registration_Number"Wd.Selection.TypeText Text:=sh.Range("B" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="Program_Name"Wd.Selection.TypeText Text:=sh.Range("C" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="Examination_Date"Wd.Selection.TypeText Text:=Format(sh.Range("D" & iRow).Value, "dd-mmm-yy")Wd.Selection.GoTo What:=wdGoToBookmark, Name:="Grade"Wd.Selection.TypeText Text:=sh.Range("P" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="Statistics_Marks"Wd.Selection.TypeText Text:=sh.Range("E" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="Statistics_Result"Wd.Selection.TypeText Text:=sh.Range("F" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="Excel_Marks"Wd.Selection.TypeText Text:=sh.Range("G" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="Excel_Result"Wd.Selection.TypeText Text:=sh.Range("H" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="VBA_Marks"Wd.Selection.TypeText Text:=sh.Range("I" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="VBA_Result"Wd.Selection.TypeText Text:=sh.Range("J" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="SQL_Marks"Wd.Selection.TypeText Text:=sh.Range("K" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="SQL_Result"Wd.Selection.TypeText Text:=sh.Range("L" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="PowerBI_Marks"Wd.Selection.TypeText Text:=sh.Range("M" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="PowerBI_Result"Wd.Selection.TypeText Text:=sh.Range("N" & iRow).ValueWd.Selection.GoTo What:=wdGoToBookmark, Name:="GrandTotal"Wd.Selection.TypeText Text:=sh.Range("O" & iRow).ValuePercentageScore = Format(sh.Range("O" & iRow).Value / 500, "0.0%")Wd.Selection.GoTo What:=wdGoToBookmark, Name:="Percentage"Wd.Selection.TypeText Text:=PercentageScoreWdDOC.Bookmarks("Registration_Number").DeleteWdDOC.Bookmarks("Examination_Date").DeleteWdDOC.Bookmarks("Statistics_Marks").DeleteWdDOC.Bookmarks("Statistics_Result").DeleteWdDOC.SaveAs2 (ThisWorkbook.Path & "\" & sh.Range("A" & iRow).Value & ".docx")MsgBox "Mark-sheets have been prepared for all the students. Add Microsoft Word 16.0 Object LibraryOnce you done with adding references then double click on Module1 to open the code window. 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.
0 kommentar(er)
