In this screencast, we are going to learn how to sync the rosters to the main gradebook. So once the TA's have input grades, maybe every week they grade and then they submit this to a common folder. This folder might be a Dropbox folder, for example. Then the teacher can sync or update all of the rosters to the main gradebooks. That's what we're talking about in this screencast. So I've got five files here. These are similar to the files that I've been working with in previous screencasts. And what I've done is I've just put A, B and C. So these A, B and C are supposed to mimic the grade items, so we might have homework one, homework two, homework three, and so on. And then we've got our students that are in each section. And I've just put in some random numbers here that are supposed to represent the grades on those assessments. And what we're going to be creating in this screencast is this subroutine. When I go ahead and click on this, Sync files to this sheet, it's going to open up each one of these files one at a time. It's going to look through for the matching person in that file. By the way, I've alphabetized these on this main spreadsheet here. And then whoever finds a match, it's going to pluck the data from these files, and overwrite or put it here. Now, it's important to note that if you already have a grade item in the gradebook and the TA changes that grade item, then we're just going to overwrite what was in the grade file to begin with. So let's go head and let me show you what this subroutine is going to do. We can click this, it's opening up right now and extracting the data. And at the end of this, it doesn't take much time, but we've extracted then the grades. For example, these three grades from the A, B, C columns of those individual files and we've consolidated into the main gradebook. So that's what we're going to be making in this screencast. So I've just drawn a quick flowchart for this. Now, this is not for the entire subroutine. But this is for iterating through the workbooks. So we're going to do some stuff here. Then what we're going to do is we're going to open up a workbook. And I've entered into this do loop, so iterating through files until there aren't anymore files in our directory. So in this example we have five files. So we're going to do this loop five times. We're going to open up the workbook. In each workbook, we're going to count the number of students. We're then going to enter into a for loop where we're going to iterate through all of the students in each file. So if I just look at one of these files, let me just open up File three. File three has two students, so the number of students is going to be 2. Then while we're iterating through each of the students, we're going to create a vector G. G is going to be a vector that's going to be composed of the number of items, and that's another variable. So the number of grade items here would be three. So the G vector would contain for Sally for i = 1, the G vector would just contain two, six, and one. They're the grades for that student, Sally in this case. After we've created the vector G for that particular student in that particular workbook, we're going to transfer back over to the main gradebook file. That is this, we're going to transfer over to here. And we're going to find the index, which I'm going to call idx, which is the row number of the student i in the main gradebook. So if I was in File three here and i was equal to one, we're looking at Sally. I would store Sally's name, and then we go back to the main spreadsheet and we'd find Sally in this column here. And now it'd be 1, 2, 3, 4, 5, 6, 7, 8, 9, so idx would be 9 for Sally. Once we found that, we're going to then export G, which we've stored. Remember, G was the line of the grades for Sally or for that particular student. We're going to export that to the corresponding row, that's the index row that Sally is found in. In that way, we're transferring the vector G to the main spreadsheet, the main gradebook. And then we're going to keep going until we're all out of files, five files in this case. As always, when I'm transferring between different workbooks, I like to define variables aWB and tWB As Workbook. And I'm also going to Dim a path here As String. The path is the folder path that contains those files. So in this case, the path to my files here is just that the file path here to get to the folder where these files are located. I'm going to show you something a little bit different here. But first, we need to set tWB equal to ThisWorkbook. Now, what I'm doing in this example is I have a hidden sheet here. And I recommend doing this for your projects, because you don't want to keep asking the user for a file path every time. You can just store that file path because it's not really ever changing. So what I've done, and I'm going to go ahead and unhide this, I have a sheet under here called Path. So there's a sheet called Path, and this is the file path. So I've just copied and pasted that file path into cell A1, and that's going to be a string. And then I can go ahead and hide it, and it's always going to be there. So the first thing I'm doing here is I'm just going to say path is equal to that cell A1 of my Path Sheet in ThisWorkbook. So that's how you can sort of have a static path. And then every time you run the subroutine, you can pick up that path from that cell. Next, I'm just going to make sure that sheet 1 of this workbook is activated, just in case it isn't activated. I'm going to count the number of items in this workbook, on sheet 1. So I'm going to be counting the number of items in column A. And that's going to be the number of names. I need to Dim that variable, and I also need to Dim this nItems. Both will be integers, so the number of names is just, I'm going to use the Count A of Column A. nItems is going to be the, I'm going to do Count A but up the Rows of Row 1. nItems corresponds to the number of grade items. And you see in Row 1, we have 1, 2, 3. Another thing I've done is I've dimmed this G vector as a variant. And this is actually going to be an array. It's going to be a one by something array. And after we've counted the number of grade items, then I'm going to ReDim G as a variant. I'm using a variant just in case the user puts in grades like A, B, C, and D along with numbers. I want to make it so it's adaptable to numbers and letters. So now we're entering into the Do Loop. In here, I have FileName. And I actually need to Dim FileName. FileName is equal to the Dir. So it's going to take the first Excel file, xls file, in the path. The path is what we defined earlier. And that's in the hidden sheet, the path sheet. So it's going to take the first file and we're just going to go one at a time. We're going to open up that FileName, and then we're going to Set aWB to the ActiveWorkbook. So whatever we open up, we immediately define as the ActiveWorkbook. We're going to make sure that sheet 1 is activated. We're assuming the grades are on sheet 1 of every file. We need to count the number of students in each of those files. So for example, if I open up File_3 here, I would do a count A on column A which is going to be 2 students. So that's what nStudents is. We are then going to just select Range A2 of that sheet. So we're going to start in this cell A2. Now, we're going to enter into a fixed iteration 1 to the number of students. And on the flow chart, we're now entering into this for loop here. Inside the for loop, we're going to create this vector G. We're going to determine the index, then we're going to go back to the main spreadsheet, determine the index, and then we're going to export G to the spreadsheet. While iterating through each of the students, we're going to activate the active workbook, just to make sure it's active. I'm going to say name is going to be the ActiveCell.Offset, so remember we already activated range A2. But if I want to select, for example, the name of the first person here. So i is equal to 1. Sally is i equal 1, then I can just do the offset. The name then we're going to active Sally offset by i minus 1 rows. So here i is equal to 1, so we're offsetting 0 rows and 0 columns. When i equals 2 for Stephanie, we're going to offset by 1 row and 0 columns. So that's how we'll pick up the name of that student. We can then set this G vector = range. Now, because I'm just saying G is going to turn the vector, it's just going to turn this into an array. I'm going to set G as range B and i + 1. So if i = 1, we're going to do B2 to D2. B2 to D2 would be this vector, so it's going to take in that as the vector G. And now if i = 2, we're just going to increment that by one row for the next time we go through this.
