After you have created a new directory and saved the original file as a new file in that directory that you chose, see the previous screencast on this. The next step is to import into your main file the roster. So, what I'm going to show you in this screencast is how you can import the roster. Now I've got the roster file. This is the roster file that you're going to be starting with, and it's got the last name in alphabetic order, comma first name it's got the student ID number, and the section number. So, you're going to want to import this roster file into your main file. You're a great manager file. I would highly recommend doing this in a modular approach, meaning you should have sub routines for each step of the way. It's a lot easier to do that to troubleshoot one module at a time and be one step at a time, and then at the end you can call all of your subroutines. I've got a really good screencast in Part one of Excel VBA for creative problem solving that relates to modular programming. So, what I'm going to show you in this screencast is how we can take the data here in the roster file that the user is going to select. This is still part of the initialization scheme, so this isn't going to be done all the time this is just done the first time that the user runs this file to sort of set up their grade manager spreadsheet. So, this is what we're going to make in this screencast. It's quite simple. We're just going to have a message box, it says navigate to the initial class roster. This file must have student names in column A, Last Name, First Name, Student ID numbers in column B, and section number in column C. So, I'm just kind of stipulating the format of that file, and then the user can navigate to where the roster's found, and I've got that just in a rostered dot XLS X file. We click okay, so this is the result. It opened up the roster file, it copied all of that and then it opened up the roster tab here on my main project. This is just the import roster file, and then pasted the the roster into the roster sheet of this file, and then a closed the roster file. So, it open it, copied, switched over to here, pasted, closed the file. So, let me show you how you can do this. The first step is to define a few variables, and if you recall, if you took part two of the course where we worked a lot with workbooks. I am Dimmy TWB, as a workbook, AWB is a workbook because when you're working with different workbooks, opening and closing, you want to eliminate any discrepancies, and you want to be able to refer to the proper, the correct ones. I'm Dimming roster file name as a string, because we're going to use that here in a minute. It's going to be important that we set this workbook to this variable TWB. Now, I'm just going to have a message box that says to the user navigate to the initial class roster, so just tells them the format, the user can then select using this get open filename filter, they can select the file where the roster is, a lot of this is covered in part two of the course. We're then going to open that roster file name workbook, so whatever they chose in this previous step, we're going to open that workbook. We're going to set that active workbook to this variable AWB which is going to represent the active workbook and then we're going to do, we're assuming that the roster is on the first sheet of the roster workbooks. That's an assumption we're making. We might want to put a warning in here that says the roster has to be on the first sheet of the roster file but I'm just going to assume that. So, we're going to select the first worksheet which has the roster on it, of our roster file name. We're going to copy all of the cells, and I recorded a macro for a lot of this to determine how to select all the cells on that sheet. We're going to copy that, we're then going to activate this workbook. So, the workbook that this code is in is always this workbook which I set above here. We're going to activate the roster sheet then we will go ahead and select the cells of that roster sheet, and then we're going to paste. And finally, we're going to close the active workbook. The work that we took the roster data from, the roster file, and we're going to close it without making changes. We'll make a few more modifications to this. I'm going to go ahead and put in this application. display alerts equals false. Another thing I'm going to do, is I'm just going to put some air handling in here because whenever you have a box that pops up and the user can press cancel, you want to make it such, that's going to trigger an error. And so you want to catch that error. So, I'm just going to say on error go to here, and at the end I'm just going to do here. So, it basically just bypasses everything. So, that's the code for doing what I showed at the beginning of this screencast. It'll ask the user to navigate to the initial class roster, it'll then open that up, and it'll copy and paste. Another thing you can do if you don't want to see what's happening behind the scenes, you can always add in up at the top here, this statement application dot screen updating equals false. So, that will enable or prevent the user from seeing anything that's happening so it'll basically freeze the screen, it'll do everything, and then it'll just kind of show you the end result. So, that's kind of a more professional way to present things. All right, we will continue along with the grade manager project in the next screencast.
