Thanks so much for joining us here if this looks familiar this is part of the training last week if you did not catch last week's training don't worry about that we are going to cover something brand new in this training you can always go back I'll include the link down below just for a quick review in last week's training we show you how to create these dynamic quizzes with just a simple few clicks creating unlimited questions pictures and both multiple choice checkboxes and text type of.
Questions and allowing user to choose the answer once we do get the information into it we can create an automatic quiz just by entering a name their ID and the clicking start quiz is going to let us know each and every question simply by moving forward we're going to have the ability to move through quizzes students will have that so we've got a lot to cover let's get started we are going to show you today how we can take information from a student list how we can send an email quiz template to each student and also.
Get the results from those quizzes back into Excel automatically with just a few clicks it's gonna be a great training because we're gonna be using zapier as a client we're gonna be using Google sheets we're gonna be using some brand new methods to automatically get new data from Google sheets back into Excel something I have never showed you yet and it's gonna be really great we've got a lot of cover we've got a brand new sheet with quiz data this is gonna check for results and bring that quiz date I'm gonna walk you step-by-step how to get.
The quiz data into this table and we've got the results that's rec league comes from the quiz we've got a quiz sheet this shows us what the information was based on the results of the quiz a student take and a student list as well which we built out we've also got all the information from the quiz data including all the quizzes that were taken all the questions that were answered all the ones correct your average score in the average time it's gonna be a really really comprehensive training so we've got to get started right away before I do I want to make.
Sure when create these videos each and every Tuesday up to usually an hour long so they're long and comprehensive if you like this type of training I do appreciate if you would subscribe and don't forget to click that notification icons bell I also really appreciate you sharing these videos with your friends family co-workers people who might appreciate this type of excel training we create real applications for real users each and every week so that would be excel for freelancers don't forget the name we also have an excel for freelancers Facebook group if you.
Your own application I'll be developing an entire accounting application in this program now include invoices inventory purchase orders customers vendors items we have notifications email a full dashboard and chart of accounts of course general ledger and tons and tons of account including reoccurring transactions and of course most importantly we're gonna teach you how to be able to share and sync your Excel workbook with anybody in the world and of course how to license your own application and be able to charge customers we're gonna take you all the.
Way from concept to cash and everything in between so I hope you'll join us alright let's get started with the training we have added some features I've added the ability to quiz students and the ability to send result back into the application email results to the students and so that's like really great training we've got a lot of love to have a students now students name contain ID email quizzes taken in lots of detail I've added some tablets these are gonna really help us when we send emails because those templates.
Contain dynamic content including the student name student ID the number of quizzes total questions and all the information so when we send a quiz we can send a quiz out with that and I'm gonna walk you step-by-step through that so automatically when you click send a quiz it's gonna automatically create emails and of course it's going to be able to send the quiz to any student just clicking the buttons gonna automatically create a special version of this quiz and it's going to attach that quiz automatically into their email and that specific quiz of course is just.
Going to be protected and show you all show students only the quiz so if we were to open this up you will see that this specific application has only the quiz sheet visible and nothing else everything else is hidden so the students would take this quiz they'll run through it and they'll click finish and those results are automatically going to be sent back to you as the teacher or person sending the quiz automatically those results are going to come back we're going to show you how to do that so you can email unlimited number of students this quiz in Excel so.
It's really really powerful we also have the ability once the results come in to email those we can send the results just by clicking the butts gonna create an email and all the results data even with the combined quizzes so even if you have multiple quizzes that result did is going to be sent to of course your students or the people you're standing on the quiz do a lot to show you've got a lot of macros I've been doing a lot of work on this and I want to get to everything and try to get to it in about an hour so we've got a lot to cover make sure you got your coffee or your drink of choice and so let's get started on.
All this what do I want to show you first well what I want to do is I want to basically take this quiz data once
They complete this quiz I want to send the information to Google sheets and the reason we want to do that is because when it comes into Google sheets we can then which is a table and that information can come back into our cell under quiz data so there's two parts one get it into Google sheets to get it back into Excel so I'm going to show you both of those parts and the best thing the way to do that.Would be through of course Google sheets and through your own zapier so we're gonna show both of those so here is my Google sheets now I'm gonna create a real time with you so you can follow me step by step what I'm gonna do is I'm gonna create a new Google sheets and I'll give that a name so here's our here's our name and basically all we want is the quiz data in this sheet the results data so let's just call it quiz data and then what do I want to tell us why I really want the same thing as what's in here I want this I want all this information right here submitted on.
The student name the ID the time the questions I want all this information in side Google sheets so I'm gonna copy that and we're gonna go back into Google sheets and then I'm just gonna paste that right there just paste it right there paste we can paste the paste of paste it all paste the formats and the values okay so now we have what we have in our sheet that's really all we need for this right it's very very simple the rest will be taken care of through zapier okay let's go into zapier if you're not familiar with it we've done it in a few trainings it's a great application it's.
Free up to about you know it's got a limit to the number of zaps but it is free to try out so we'll be using the free version which is fine for you to try out and so what it basically does is it connects applications you've got tons of applications to be able to connect so when you see the apps in here it connects from one to the other from Gmail Google sheets or select tons and tons and tons of applications and triggers so for example our trigger is going to be a web hook from our application excel so we're gonna send what's called a web hook in two zapier.
And everyone's going to connect it to Google sheets and add a row that's what I want to do first so why don't we click make a zap and we'll show you just how we do that so the first thing I want to do is from a web hook right this is called web hook Mike z-bo you can search for it up here if you don't find it you can search for here web hook so what do I want to do that I want to get a web hook we want to catch a hook because basically what we're doing from our Excel application is we're gonna hook into that and I'll show you how we do that in the programming in the VBA.
But we want the zapier to catch the hook so let's continue and I'm gonna walk you through the go but here's the hook this is what the hook is now you're gonna have your own specific hook so make sure you copy your own hook and now I'm going to go and paste it into the code so let me go into the code and we're gonna go over this quickly but basically this hook is going to we've got a brand new module so sent to Google sheets is the module I'm focused on here send to Google sheets and what I want to do in this case is I want to send this hook so.
I'm gonna copy paste you're not gonna have this link you're gonna have your own so make sure that you copy all this up until the question mark right here see that that's what I want to paste in paste that in and make sure of course it ends with the backslash air so that's the hook all I'm gonna do is that now I'm going to run my quiz and it's gonna automatically send this hook just so we know last week we went over we had run quiz max so at the end of this the last macro that's run when they finish is called finish quiz finish quiz that's so.
Does the last button when they finish it and I'm the last button I've added a brand new line of code that's gonna run this macro that you just saw called send results this is the macro that's gonna send the results to Google sheets so that's important so when I when I click finish it's gonna run that macro I'm gonna walk you through right now just what it looks like when we go into quiz sheet here we can reset it and now let's just put in a name and we'll start the quiz and we got a brand new quiz here and I don't know the answers to these.
But we'll just go ahead and this one I know Soup Nazi okay so we'll just go ahead and go through it answering whatever questions we can and like this one alright so we finished the quiz in and ran that macro now what it did is a brand that hook also it ran the hook into so once at the end of this finish quiz which just ran it also ran send results and the result that macro is this one right here so it ran this web.
Book now it ran this web hook right here we'll go on to that but basically what
It did is that ran this it included all the information student named the ID total time so all that information went directly to zapier so now what I want to do in this it just ran this look so now what I want to do is I want to continue we don't have to pick off the child key and I want to do test and review so let's review it let's find out let's see what it found okay it.Pulled it in two seconds ago let's see what it found it found quiz 90 the submitted date everything looks good the total number of questions you can't see it but you'll see it more in detail so basically it found all the information that I sent here it found so that's good it's got all the information that we need we're done editing okay we don't need any more samples so what do I want to do now we've got the information from this web what do I want to do well what I want to do is I want to create a brand new row in Google sheets I want to create I want to put this data here.
Right in here the first row and then if it comes in again I want to put in the next row so that's what I want zabi ER to do so let's let's do this let's tell it what to do I'm gonna I'm gonna choose Google sheets here and what do I want to do now I'm already connected to Google sheets if not it'll ask you to connect all right now that we've got it on an account now we can continue on and what do we want we want a spreadsheet row this is required so we want to make sure that we look for a specific sheet and quiz data is the sheet that we just created so that's the one we want we're looking for and what worksheet well this.
Is just sheet one we only have one sheet so make sure you select sheet one okay so now we have our account we have we don't need Drive option quiz data sheet one and now we start filling in the fields what do I want on submitted on so let's click here and click the ones submit them we need to map those fields now so query strings submitted on is here the name would be our student name so let's find that and scroll up here so we're gonna need to find the student name here and so each one we're just gonna click connect student ID mapping.
These fields total time here just look for that total time here so we're mapping these fields total questions it's very simple so no matter how many you have you can just map them here of course you might have different ones than I do so total questions here would be here and then the number correct click on here and then the number correct and then incorrect I think we also have that too so let's look for that and then the last one would be the quiz name so we want to put that in here all right so now we.
Have everything let's just review real quick we have query submitted on these to match up the name looks good the ID total time that's correct total questions number correct incorrect and Creed names so now everything is is mapped properly so we're gonna click continue and now we have test and continue we can do that okay it looks good and the last step most importantly turn on the step turn on the Zap you can also do it up here turn on this app great so now we can go to my zaps and we.
Can just see all this absent and so let's take a look back in the quiz did and you see we have a brand new line all that data went in here perfect okay great now make sure your zap is on right here's the zap that we just created we can name it it's also a good idea to name your zap so let's just put quiz data I put send quiz data cuz we're standing in a send quiz data because if you have multiple apps you want a date top because if you have multiple apps you want to make sure that it's all in okay.
So now that we've named it now let's test it out in real time because we know it okay so let's do one more quiz and make sure that information makes it into so we're gonna close this and then reset the quiz let's choose the different name James and automatically the ID will appear here start the quiz and then just go through it I'm gonna answer most of these wrong that's not important that we get this right it's speed so each one I'm gonna answer okay so we finished the quiz six three answers right three answers we have 50 and eight seconds was our.
Information now let's go to Google sheets back into the Google sheets and see if that data made it into there properly and it has here's our Google sheets you see James ID 1208 this should probably this is Bobby it's not 12 I we need to format this it's not that important though it's more important that we format an excel but this really should be formatted as a time right but it's okay it's not so important but you can it is important in Excel so will it's formatted correctly in Excel but keep in mind that this is 15 seconds and this is eight seconds it's just a format issue.
So not a problem six questions correct and everything and we have our quiz name here so everything made it into Google sheets great now I know I haven't in Google sheets but I need to get a back into Excel what does that mean I need to get that data into Excel so I want to bring that data here into a new row right here I'm lever e time does a brand new did I want to send it back to excel how do we do that well I'm gonna walk you through that right now again we're going to use a Pio to do that and we're gonna also use Dropbox you can use any.
Shared folder but Dropbox isn't great what I want to do is basically use zapier anytime there is a new row in here I want to send that information to Dropbox into a text file then Excel is gonna read that text file and bring it back into itself it's very very fast it's not very difficult to program I'm gonna walk you through that right now so let's go ahead and go it back into zapier here and create a brand new zap so we can go back here that one's already done we're gonna make a new zap but this one we're gonna have a different trigger what is the trigger the trigger is gonna be a new row in.
Google sheets right that's what we want so let's if we don't find Google sheets here we'll just Google okay here it is right here Google sheets so what's the trigger triggers a new row a new or updated spreadsheet row when we have a new row let's just go do new row new spreadsheet row that's the only trigger I want when there's a new row I want something to happen let's continue on I'm using dis account here and we can continue and so what is the spreadsheet well of course the spreadsheet is the quiz data so we need to locate it quiz data and the which worksheet of course.
Is worksheet one so when there's a new row we want something to happen there's a new row in quiz data we want something to happen what do I want to happen well I want to have that file let's just test and continue make sure that works okay okay great that works okay so what do I want to happen I want to send it to my Dropbox so let's sort the Dropbox here it is that's what I want to happen and what do I want to what do I want Dropbox to do I wanted to create or create a text file create a text file that's what I wanted to do because Excel can read that text file so let's continue on and.
What folder I've already in a folder in my Dropbox here's my Dropbox and I've created a folder called quiz results so my Dropbox is here and I've created a brand new folder called quiz results that's where I want this text file to be stored make sure you have a specific folder set up in your Dropbox or whatever shared folder you're using so let's go ahead and search it I've already connected my Dropbox if you haven't you'll it'll ask you to connect so let's continue on and now it's gonna look for a folder it's gonna be our first folder which is right here quiz.
Results here and what do I want the file name to be I'm gonna I want the file name to be very unique so the best way to do that probably is to use submit it on because this includes the time and the second so I'm just gonna use this submit it on as our file name because I want it very unique and unless somebody submits it within the same second it's not gonna be now what do I want it contained in this well I really want all of the fields what do I want every single field let's take a look at this and I'm going to also open this at the.
Same time so bring this down so I want all of these fields submitted on time and I wanted in that order submitted on name ID total somewhat and I want them separated I'm gonna enter so let's all we need to do is add search search and the first thing is submitted on ok then I'm gonna hit enter ok that's important next thing what I want name so let's look for name on the list names here hit enter again next one is ID click here click here and then enter and then once.
More next one is total time so let's look for total time you see how this goes it's very simple and next one is total questions so let's look for total questions here just click here and the total questions next one would be number of correct answers because this is going to help us put it all in our Excel data so number of questions total questions here number correct is here and then of course we have the inch so hit enter and then number incorrect let's take a look incorrect is three and.
Then the last one would be the quiz name so we're just gonna put that on right here quiz name right here okay now we have everything all the information in gray do we want to override existing files no it's fine and we'll continue I said we've done everything in here test and continue ok great it says a text test text file was sent to Dropbox one minute ago let's take a look into my Dropbox file here here it is here's our text file let's open it up and take a look at it we have the date the date number the seconds which is fine everything we have all the.
Information everything we need in a text file excellent okay so now zapier is working let's just make sure to turn it on that's critical we need to make sure we turn it on and we don't need to retest we can just turn it on ok now that it's on we are ready to go we can go back into my zaps and we didn't name that I guess we could but let's uh let's name your zap let's name it here and this would be quiz data to Dropbox ok so now we've got a name we understand how we get information into Google sheets and take it from Google sheets and put.
It into Dropbox into a text file we have these two tabs that are on great we are done with zapier now we're good to go so now we can go back we've got our text file now all we need to do is we need to get a macro and that macro needs to look inside this folder for any files and any files that it does find it needs to open it up it needs to parse this it needs to take all this information create a brand a new row and put it right down here and it needs to do that for every file and then what I want to do once it's done we're done with this text file I want.
Excel to delete that file we don't need it anymore and we want to make sure that folder stays clear so how do we do that that's gonna be in a macro and of course let me take you through that Mac and show you just how we did that so inside the VBA editor and we have a brand-new one called get results macros so get results so the first thing I want to do is we need to we need to specify a specific folder for this we need to tell Excel where to direct to look in what specific folder so I've added a folder an additional.
Feature here under create quiz and you see the results folder so we're gonna set that specific results folder in to h3 so now when we click browse we can set a specific folder it's in my Dropbox and so we're gonna have quiz results right here so what we need to do is select that folder it's gonna set that folder right here that's very important we need to tell Excel what folder to look for quiz did so the first macro that we've running is this one right here assigned to this button and the name of that macro is called get results folder you just saw that so let's just take a quick look at.
That although we've been over something like that all we're doing here is basically determining a file dialog we're gonna open up a folder picker remember we only want the folder not a file so we're gonna set the results folder equal to a specific folder and then of course with that dialog we're gonna give it a title and then what we want to do is basically take whatever folder the user has selected and put it in h3 that's all we want to do so that's really important next up I want to take whatever's in that folder through add results and I want to put it in side.
Excel so how do we do that well there's just a few lines of code it's not too long so let's focus on that we need we're gonna use the file system objects we need to define fs0 as an object which is going to be late binding and I'll show you that and of course file folders objects so these are just objects that's gonna enable you as a user not to necessarily need a library for that all right also we need to know the folder path that's of course at age 3 so we need to determine that as a string the.
File name as a string we need to know that the name the file path and the results text the result as strings because all the results are going to go in here we're gonna use an array so it's gonna be really exciting we are going to use file number as long the item number we need to go through the files the item number as long in the results row as long and all results this is a string that's going to be converted into an array and then we're gonna parse the array and I'll show you how we do that so XI 1 we need to make sure that H 3 is not empty remember that's our folder.
Location if that's empty we should just exit the sub if there's no folder to looking then there's nothing to do next up we want to define the file system object scripting object so we can do that with this lot of code set the FS 0 equal to create objects scripting file system object that's going to tell Excel all about our folders and files and help us look through those now we know the folder path we just set that in h3 that's gonna be our folder path and then what I want to do is I want to make sure that it is accurate right we want to.
Make sure that it's not an issue if there's a problem with it or even though it exists there could be maybe doesn't exist so the next line of code will help us we can set the folder to get FS o to get folder path this will get the file folder but if there's an error if there's a folder is nothing then there's a problem with the path right that's why we have on a resume next cuz this will create an error if that folder path is not accurate so for example if I send you my file right and my and my file has.
This folder path your folder path is gonna be very different so when you try to run this macro with this folder path it's gonna create an error so we want to alert the user hey this folder path is not accurate on your machine please browse for a correct folder path so this will help us do a mess but please select a correct results location and then exit self I guess you know what we can do is we could prompt the user to do that so get results folder we can run this macro right now so the user knows to browse.
For it and we can exit the sub so we don't need any more of that alright so next up now assuming that we do have the correct folder we can we want to count the files if there's nothing in this folder we don't need to continue right we only need to continue those actual files in this folder so this line of code is gonna help us if the file folder files count we're counting the number of files in this specific folder if it's equal to zero then exit the sub right we.