Fully Automated Student Marks Sheet (Transfer Data from Excel to Word) - A Step By Step Tutorial

[Music] hello everyone welcome back you're watching the data labs my name is Lee today we are going to learn how to automate Microsoft Word from Excel so to learn ms word automation we will be considering a very practical example of automating students market you can use this up if the same technique and logic to automate other tasks like creating sales invoice welcome letters for employees or freely shipped etc so before walking through the macro it is important to go over a few steps and perform some actions in ms word so let me go to the folder where i have created ms word template and excel file so here you can see that ah this is the folder where I have kept the excel file and MS Award templates let me open the MS Word file so here you can see that this is a basic student market I am NOT spending my time on the beautification of this particular mark sheet I have been created a basic mark sheet and where you can see that we have two six and one is employee details basically the basic details related with the employee and other details are like in this particular table we have subjects maximum marks and marks obtained positives so let's go through one by one what we are going to automate here so with the help of Excel macro basically we are going to copy and paste the data from Excel table to this particular table like name of students registration number name of program and examination date and grade okay and in this particular table we are going to update marks often against each and every subject like statistics and probability Microsoft Excel Visual Basic for applications ms SQL alway so we update although all these marks basically from Excel and to this particular world file and we will also update the result or remarks like powers past or maybe fail so we will update these these particular column as well and apart from this column we we are going to update the grand total this is and this particular field and the percentage okay so a creating and automating M is what is very easy we need to perform some basic steps before going to excel and start writing the macro so as I told you that to copy the data from Excel to MS Word we need to perform some basic steps and all those basic assets are creating the bookmarks so here we are going to replace I know the name this particular registration number bi certification and all the details okay we will be transferring 17 data point from Excel to MS Word and hence we will have to create 17 bookmarks for each and every field let's start by creating the bookmarks so to create the bookmark it's a very easy steps you just need to select you know the word where you want to copy and paste the data in ms word and just then go to insert okay and then in link group you can see that there is a bookmark so you just need to click on bookmarks let's start getting a bookmark all the cementing bookmarks so let's start with name first so that's our I am just going to select this entire world okay because we will be replacing this world and go to insert and then bookmark in bookmark window you just need to type the bookmark name so it should be named make sure the spelling should be same as because we will be repeating these bookmarks in Visual Basic application code and now we need to this is for registration number then again insert and bookmark and just to give the name as registration number okay so in the same way we need to create all those on next 15 bookmarks so let's quickly create this you so we have done with all this bookmark like we have created 17 bookmarks let me show you let's click on bookmarks and here you can see that all these 17 books mark our bookmarks have been created so that's close this bookmark window and press ctrl s so now we have done with damaged word file this particular template is already ready so let's close this MS word application and let's open the automating word from Excel in this particular file the way I have already created the table let me open that so in this particular excel file you can see that if we have one estreno worksheet strained scores and this is the table this is the basic table which I have created for scores like in student name registration number program name examination date and this is the like the subject statistics and probability and the marks obtained by a student and what is the result whether it's extremely sparse or filled then same goal for ms excel and then marks obtained and result visual basic application SQL and power being and this is the grand total marks okay the summation of each and every marks obtained in this particular subject and and this is the create basically okay so this is the simple table and you can create this type of table and copy and paste as many record as you want maybe hundred thousand or maybe ten thousand it depends on the size of one data and then you can automatically transfer all these details in Word file in a specific format so let's start with inserting a button over here so just go to insert and in illustration section you just click on shape and fill and let's consider this one the rounded rectangle okay and just draw a rounded rectangle over here go to shape style and change the color maybe we can pick this one okay right click on that and it text in generate mark sheets okay select it go to Home Center maybe increase the font size so now we have done with this particular button we will utilize this button as a user interface like usually we'll click on this particular button and entire works market will be generated on a single tree so let's start writing the course so to do that we just need to go to Microsoft Visual Basic application so let's click on the Developer tab here and in Developer tab you will find code group in the code group you just need to click on Visual Basic so now we are in Visual Basic application window you can see that this is a project Explorer this is the properties window and this is the currently blank so this is the code window basically right so first of all we need module to write the code to ensure the Modi will just go to insert menu and click on module okay now you can see that we are encoding window this is the code window where we need to write so that so before starting and writing the code we just need to provide the reference of Microsoft Word as a reference basically because we are going to automate the MS word from Excel so if we need to add reference of ms wall put this particular file so let's go to tools and click on app friends and in reference you just need to find the Microsoft Word so let's type Microsoft that's I know ok here you can see that this is a Microsoft Word 16 point or object library we need to select this particular reference so just check this and click on ok so now we have done with the adding reference let's take it go to reference and now we can see that this particular reference the object library has been selected you can close press ctrl s to save this file okay so let's start writing the code so first of all we need to provide the procedure name over here so let's start it described and the procedure name would be send to word ok so we need to write all those goals between sub and ends of a statement ok so let's create some room over here and so first of all we will declare some variable to perform our excellence so let's put a comment over here D so declare variables for this automation let's start with the variable declaration so first of all we will declare the word application right so deem WD and word dot application ok and this particular let's put the comment this is what application ok now we need a variable for MS word document so let's and in WD d you do see and what god document basically okay and this is for one document right so dream I row as long comment variable whole starting row and loop through all record in the table okay as we are considering percentage in our market so we need to do variables for percentiles as well so that we can assign the calculation in that variable and then we will move or just copy that variable to a specific book MA okay so named percentile ESCO as variant variable to hold percentage value okay let's declare one more variable that is SH as worksheet so we will refer this particular box it with the help of the variable SH let put command worksheet variable to refer the week where we hand over the table okay so now we have done with a year variable declaration let's start I know writing with set keyword so first of all we need to set this one application WD okay start word and a new document let's start with set keywords that WD is equal to new word word dot application let's I said the worksheet worksheet where table is available right so set assets is equal to this book dot shoots and the sheet name is so this is the worksheet name basically the student s course so you can copy this name from here as well and just list over here okay so now we have done with the state keywords or ms word application and in the worksheet so as I already let us move to the Excel window first and so here you can see that our data is starting from row number six so let's move to the X visual basic application and we have already declared a row variable to store the initial rule number so if you think that you don't nobody is starting from two then you can assign like a shine to to a row here every I am going to consider iOS 6 okay ah so we have initialized this variable I roll with six let's put comment so now let's start looping through each and every record so we will utilize the while loop here dual wine and SS daughter arrange and here in pyro daughter value is not equal to blank okay and then loop so what it will do it will look through each and every Records and till I like e io is not blank it means we are referring column a okay so let's increase the value of I here I do here is equal to I 0 plus 1 okay so let's do logic is basically once we'll go through each and every record we will open the MS word template which we have already created and transfer the data to a respective bookmark and then clue save that file with the student name and close that file again we will Lu to through next record basically and we will again open the word file transfer the data save the data receive the word file with the new student name and then close the file I can loop through the next record open the file so in that way we will loop through each and every record and open a template save the template with student's name and transfer all those bookmarks replace all those bookmarks with the value which are available in basically our table okay so let's open here put the comment opening the word template okay so set W do W D do C and this particular variable here you can see that is equal to W D is a word application we have already said dot documents and then we need to open that the documents and the document is here we need to pass this this workbook dot path and then I'm pushing marks it M let dot d useless X we can also copy this and liam from the folder let's go to the folder just press f to copy an entire including extension go to visual basic and here you can replace this particular okay so now we have open the documents so let's make the word application visibility false so that if there will not be a aware that whether M is what application is open or not right so that's WD dot visible in is equal to false okay let's scroll this okay now we need to start writing code to insert the values to a specific bookmark so so let's create two room over here then so first of all we will start with the name so let's pull the community name here and the code is very easy so let's w d dot selection not go to basically we are going to be a just crushing ctrl G you can perform manual activity as well go through and what so here we need to double D go to bookmark comma name and what would be the bookmark name so that would be name so make sure you are referring the same name which we have created otherwise it will prompt an error okay now I'm WD God's election not type text and your text would be we need to provide you the range where name is available so as we are looping through each and every record so we just need to refer the column name and then variable idol message dot range a ampersand I wrote normal value so this is the cool basically this is the code for name just copy this entire code and replicate this for registration number change the name this tration number and here we need to keep the name like here the name should be registration under school I'm applying the same name which I have used in creating bookmarks and as the registration number is available in column B so we need to change the column here to be right let's copy this code registration number based over here so the hot bookmark is program name okay and here are winners we just need to replace the registration number with the correct movement program under school name the value is available in column C then copy this program name i replicate this for examination date so regeneration rate right and the bookmark name would be examination under school date okay so here we can transfer the excel basically the cell value data to a specific book mount at is examination did because x is to date as a number okay so we need to here apply the format function like we will have to apply the formatting update over here so the value is available in column d and here bring in to DD mmm YY okay let's copy this program name again and replicate it for grid so let's replace we are in grade and the bookmark name would be g ra d diamond okay and the grade is available in column e because we have already referred D column this is examination date let's scroll this open is engraved and test it over here now we need to pass each and every a score as well as the result of that is cool right so first is step statistics marks statistics marks is AB level in so I think we are making some mistake over here the grade is not available in column E let's move to the table and here you can see there the radius of level in column P so we need to refer the column P and our moxie is starting from column E right so let's here let's replace the column P to concrete and now statistics marks is available in column E and here statistics underscore marks okay I just copy this statistics mark then paste over here and this will be before result then replace the marks with result right and this would be for e and this is for F let's copy both marks and result here the second would be for Excel so to place here Excel and here again Excel okay replace this with the correct bookmark that is Excel mark right and it's available in F here Excel and this is level in G so I think okay so statistics and analytics available in column F then we just need to refer the G or Excel marks and s for Excel results let's move to the excel file and validate so yeah G H V these are level in I and J so let's smooth copy the both marks and result based over here this is for review and again this is for yeah and this would be for giving and the column would be H I okay and this is living column would be I'm Z right so we have done with to be beer let's and do the same for SQL just copy and paste over here and that would be replace SQL just copy this SQL based over here writing just test that the column would be key for marks and I'll for result now we need to do the same for power way so copy and paste over here and replace this power bi just copy the power being replace a still with power being and the same here the power being again and here probably right and the column would be empty for marks and and what power very good now we have left with two bookmarks one is grandpa Bell and the second last would be percentage so let's do the same let's copy only yeah this is for in grandpa grandpa grandpa oh this is okay now we are left with one bookmark that is percentage so before moving the percentage marks from table to work Word file we need to calculate the percentage because we have only we know the total marks overall marks basically and the marks obtained by a student like that's grand grand total so as we have already declared a variable so let's do the calculation calculation for percentage and let's move to up and here you can see that this is the percentage score we have already declared twist to the percentage so let's copy and paste over here and this is equal to our grand total value is available in a column right so this is oh this undivided by 500 okay so 500 is the overall mark and now we need to apply the formatting percentage vomiting the in format and this zero point zero position okay now our cultural calculation is done let's copy this the grand total this one paste over here and change the grand total to percentage okay let's copy this because this bookmark with percentage and we need to replace the entire section just with the variable okay so all those bookmarks like data transfer is done let's write the code to remove the existing bookmark if any so let's start with on next because if any of the bookmarks will not be available it from terror while we will delete so on error resume next will help us including any of the existing here while deleting in the bookmark so let's start with the below deal they were C dot bookmarks and we need to provide the bookmark name here so first is name dot delete it's a very simple code so let's replicate the same code sixteen times okay let's change the bookmark name so the second would be registration under school number and that heart is program underscore name grand total and that is percent isn't it I think if we have extra bookmark here now we need to save this file the current file which we have already already open and transfer the data I'm talking about the MS Word file so create syndrome okay so here we need to write the code double D D using dot save and the file name would be this work book dot path and present in our data is available and the name is label basically let me go so I'm talking about this one the name so here just copy this name because we are going to refer the name as a file name ampersand dot do sin x now we need to close this file close the word file to DB was C dot pro okay now release the memory of WD do you see set you WBOC is equal to nothing now we have done with saving the file with the new name the student name then closing the file and releasing the memory whatever the memory has been assigned my operating system to this particular variable that is w DD you seem ok let's do this so what it will do it will prove this file again and come to this particular line of code you increase the IRA value and go through the next record so go up to upside here like do well again it will open because initially it was a 6 so once you will increase the value it will become 7 so it will go to the next record and open the word document again and make it visibility false and then start transferring the data to a specific bookmark from rule number seven in that same way it will create each and every worksheet or with student's name close the file and end loop basically so once it will complete the loop like each creation of each and you have three students mark sheet then we need to close the word basically I am talking about this one WD variable the world variable ok WD dot quit close this application and let's set WD is equal to nothing release the moving allocated to the broody variable right so now we need to prompt a message to use it is taking that market has been created so a mess in the box shoots have been created successfully okay so we are done with the pudding let's scroll up and see just go to deburr income so I think we have made a mistake and the type where basically if the spring should be type or text so let's say let's copy this and press ctrl-g control ever and here replace we just need to replace this type text with typ in Thai takes to be the green right this is the character spelling so let's replace one by one so that we can see should start from here replace replace please okay everything is done now it has been rectified and this is only a typo error press ctrl I click on save you go to debug compile so now you can see that there is no error or compile error basically so let's move to excel window and it's assigned the macro on this particular button right click on that and assign macro here this workbook and in this workbook we have one macro that is sent toward which we have already created in a separate module just click on this and click on OK this controls s so now we can see that we do not have any market of level here we have only the automation file that is Excel file and one is the market template right so once you will click on this particular button it will generate four different marketing because we have only 4 records so let's click on generate mark sheet and now you can see that it's generating each and every market basically transferring the data here you can see that message these markets have been created successfully let's click only save go to the folder now you can see that we have apart from this two files we have market for Alex as mr. Kamiyama let's open each and every and see whether the data is getting transferred correctly or not so this is 4lx so this particular data has been transferred from I know Excel here and this one this so it is correctly transferring to this let's open them adds more modifiers and this will be hi certification it's working perfectly so friends it's all about automating ms word from excel file do i have considered the market automation as an example but you can use the same logic and code to transfer on data from Excel to word and you can also know simulate I know the mail merge technique this is available in Word or with the help of Visual Basic because he whenever you being used we shall basically we have a lot of control we can apply conditional statements while transferring the data right which the record we need to you know ignore which record we need to remove or whether we need to change some calculation or not right so in that way we can transfer the data from Excel to word hope you enjoyed this video thank you for watching please like share subscribe and comment have a great day mama

Channel: TheDataLabs

Views: 84,047

Keywords: Automate Microsoft Word from Excel using VBA, Excel-to-Word Document Automation, How to Integrate Excel Data Into a Word Document, Controlling Word from Excel using VBA, How to write on word from Excel using VBA, Writing Text to Word Documement from Excel VBA

Id: 8cb0pV7JJPc

Length: 42min 8sec (2528 seconds)

Published: Tue Apr 28 2020