William Rosener: Hello welcome back and this video you'll learn how to use Microsoft excel to create html pages.
William Rosener: If you're maintaining a website of any size of all you're going to probably want to use some content management system to help you create.
William Rosener: manage and organize your various documents let's start by taking a look at the spreadsheet and the output that has created here.
William Rosener: So i've got some a produce apples broccolis carrots i've got the two hoops i've got either a fruit or vegetable.
William Rosener: i'm going to guys, have you guys had at least one more number of laws and produce maybe you could add whole grains or you can add dairy.
William Rosener: such as milk yogurt and cheese, or you can create a pretty good for me it's being intentional and like it's like it's like culture in exits over there.
William Rosener: So i'ma let you guys, at least at one more food group you're going to add some URLs for the user can get some more nutritional information on that.
William Rosener: And then some images here so i've got some images here on assignment 10 and these URLs which is but as long as she's in the interface here, everything should be working on.
William Rosener: Okay let's go ahead and take a look at the outputs I can see i'm going to be working on desktop etc, and when I press on convert i'm gonna have it's going to create three files, so I hit convert.
William Rosener: And it's going to create one with all of the.
William Rosener: produce one is for fruits and a third document just for one for all the fruits all the produce one just for fruits and one just for festivals okay take a look here, here it is so the first look on all foods will open that.
William Rosener: And come on.
William Rosener: There we go so take a look at this one was all.
William Rosener: alone so we're gonna take a look here, we got an APP on the produce i've got the group name fruit in parentheses, and then got a link to nutritional facts.
William Rosener: Okay here's broccoli festival in nutritional.
William Rosener: Okay, and what else can be created into this case that tomorrow, may have one just raw fruits.
William Rosener: And here we go so apples oranges and peaches.
William Rosener: And one just for vegetables.
William Rosener: And there we go so again, I can click on the nutrition facts and it's going to take me to the URL there and get more information.
William Rosener: Okay well let's get started there so in the same area as you found a link to this video, you should find the interface, which should save you a little bit about a time there.
William Rosener: So, again i'm going to walk through the two groups root vegetables normal that you have at least one more food Group, along with some URLs and images here.
William Rosener: So first step is i'm going to click on convert and i'm going to go ahead and start off by the first thing I like to do is i've been option explicit so by in misspell variable i'm gonna get a warning the system.
William Rosener: And i'm going to go ahead and I create us and variables here or the produce food group.
William Rosener: The URL and the image URL.
William Rosener: And i'm also going to create some more strings here so as we saw We wanted the font size of the produce maybe like apples to be larger in even boldface so i'm going to place an ad in pro destroying food group.
William Rosener: We create one here that's going to go ahead and simply place to implement information in parentheses.
William Rosener: URL string and then we're going to get the image URL string as well, then i'm gonna create one here called complete string which is going to take all four of these and combine them into one, so I can simply make one right write it out to the various phone.
William Rosener: i'm going to create.
William Rosener: The file name.
William Rosener: i'm also going to create one here called my path.
William Rosener: And wrote down or.
William Rosener: If you want to put up your hand.
William Rosener: Okay, so kind about declared some variables here momentarily row counter is going to simply allow us to start in route to and we're just going to keep going until we reach an empty cell.
William Rosener: OK so maybe the next step is let's make sure we get the right directory so we're working in the right area.
William Rosener: And what I like to do is.
William Rosener: Just simply come here so i'm going to be working on the desktop the director called a temp i'm just going to simply right click here and copy that's a right click copy address.
William Rosener: Then i'm going to simply paste that make sure you don't have any spaces and paste them.
William Rosener: OK now i'm going to go ahead and do a change.
William Rosener: The drive to my path.
William Rosener: And also change during my bath.
William Rosener: And you should only need to do one of these at found that if i'm working on an l Dr work, not a jump drive etc have found that if I have both of this, it says always work here and now let's go ahead and we'll just do a little check here.
William Rosener: To make sure everything's working that we're actually right into that data so i'm going to get the current directory and then i'm gonna have a message box display let's go ahead and check that just make sure we got that much working.
William Rosener: Someone hits a convert Sure enough, it wants to ride some rewriting it and see user roesner desktop in my folder called a temp.
William Rosener: Okay, so we're off to the races, now we can start maybe we'll start with that credits and phone.
William Rosener: Number call one all foods.
William Rosener: That html.
William Rosener: Okay, what this is going to do is it's going to open all foods that html for outputs it's actually going to affect delete everything in there and then start writing at the top and i'm gonna open up as file number one.
William Rosener: Okay let's go ahead and create one for fruits.
William Rosener: and open this one up is about to go with vegetables.
William Rosener: And let's get that one has three thing and like I mentioned before i'm gonna have you guys do one more food group why don't you get this one working first before you start tackling years and maybe again like you do grains or you would do a dairy or meat or something along those lines.
William Rosener: Okay, so we've kind of got the first part up and running there but cleared some variables are making sure we're getting in the right area now let's go ahead and work together just go ahead and make sure rope counters equal to two so we'll be starting in.
William Rosener: The second row i'll start our while loop here.
William Rosener: In one we're pretty soon.
William Rosener: it'll be a little bit higher there Okay, before I forget maybe all my loop.
William Rosener: And, before I forget this one so i'm just going to go ahead and increment my real counter right away.
William Rosener: So why don't we take a look at the first round, and the second row, then the third row etc until we get to an empty piece of data there.
William Rosener: Okay uh maybe at this point, we could even come on down here we've got a lot of work to do, rob lot of work to do right in there let's come on down here, they will close.
William Rosener: Are various files that we've opened.
William Rosener: In like that little message box down here.
William Rosener: Just tell the user hey information has been written everything worked correctly.
William Rosener: And why don't we check this out.
William Rosener: check this out so we're not actually writing any data to these files, but we are going to be creating these three files and closing them so so check that out.
William Rosener: would come along right over here, first of all, it delete these three.
William Rosener: i'm going to press on convert.
William Rosener: Information written now let's come back to my directory, and I can see i've written the three files or i've created them, particularly during the size they're all zero we haven't written any data to these files, but that's coming.
William Rosener: Okay, so probably the next step up here is, I need to grab the data grab this, let me grab the four pieces of data.
William Rosener: To produce the next one was food group.
William Rosener: And that will be in.
William Rosener: column to.
William Rosener: grab the URL.
William Rosener: And the image URL.
William Rosener: or.
William Rosener: OK.
William Rosener: now going to start with working on is created some strings because we wanted produce to be a larger font and involved or to find them or any strength to work on the URLs because it's in double quotes etc them.
William Rosener: Some of the baby come on down here and i'm going to create a complete string.
William Rosener: This one is going to be equal to simply can take those three and add them all up, which is going to be my which I haven't even created yet, but my produce.
William Rosener: string.
William Rosener: concatenate it with my food group.
William Rosener: string.
William Rosener: With an image your.
William Rosener: And while we're right here, maybe i'll do a little print we still got lots of work here to do but i'll print.
William Rosener: Another one, so I think I mentioned here this phone number, I can give it an ID number one I could call this.
William Rosener: 108 as long as I managed this would be 108 and i'm going to close on awake and i'm going to keep this little simpler i'm just going to call it a one.
William Rosener: pound number one or number two number three Okay, so what we're doing is we're still got some work here to do we're going to create this.
William Rosener: First of all, got to create some strings here for the various produce food groups, etc, and concatenate them all, then we're going to write them now let's take a look here at the produce.
William Rosener: And let's take a look, maybe here here's what I wanted, I wanted to say bold.
William Rosener: style equals.
William Rosener: On size, they be 26 pixels.
William Rosener: They wanted to actually have like apple.
William Rosener: And then I want to close my office.
William Rosener: Okay that's what I wanted to say so right now produce only contains it's going to grab it and contain apple so I need to.
William Rosener: put this within a boldface and then increase the size, the size someone called this up produce string and that's going to be equal to.
William Rosener: It V.
William Rosener: style equals.
William Rosener: And you're going to probably quickly realize Okay, I need to start my string with a double quotes I need to end with the typical what's going to happen with these democrats in the middle.
William Rosener: Okay.
William Rosener: that's what I want to happen, and then it actually need to close the quote here.
William Rosener: concatenate the produce.
William Rosener: concatenate my closing.
William Rosener: beautifully said Okay, but what you're going to find is that hey we can't have double quotes inside double quotes here, so what most people do is they simply substitute another character.
William Rosener: Or you could actually insert the sq value which i'm going to do here momentarily on.
William Rosener: Do a string substitute so i'm gonna do a string substitute converting these.
William Rosener: These.
William Rosener: Terrorists in the double quotes here.
William Rosener: Okay.
William Rosener: So what's going to happen is i'm going to replay i'm going to take complete i'm eventually create this.
William Rosener: long string and i'm going to replace all the activities with asked about your 34, which is a double quote so that's going to convert that back into double quotes there Okay, so I think we've got the produce string taking care of.
William Rosener: Food group string all we wanted to do here, with some start with places parentheses before and after it.
William Rosener: Just like that so we're going to place the string parentheses concatenate the food group this case that would be like fruit and then another parentheses.
William Rosener: Okay now take a look at the URL.
William Rosener: To go start off with what we want it to look like.
William Rosener: it's a little short URL.
William Rosener: So we want this to say.
William Rosener: Like to go to some of you are our realm and then it would say nutrition.
William Rosener: facts.
William Rosener: And then we're going to close the anchor.
William Rosener: Okay, so that's what we wanted to do some a per acre hypertext reference equal to replace the URL with in double quotes i'm gonna happen saying nutrition facts that i'm a closed.
William Rosener: sure your class closer close the anchor there.
William Rosener: So i'm going places in a string called URL string is equal to.
William Rosener: So we'll start off with them the aid hypertext reference equals.
William Rosener: So I want to get the double quote there.
William Rosener: So i'm going to put in a till you get my double quote so now I have got this much of it right here i've got the a high protection reference equals the double quote i'm now going to grab the URL.
William Rosener: Okay, which was in my spreadsheet I now need to grab all of this information.
William Rosener: And so i'm going to go ahead and start off with one of those utilities.
William Rosener: And that's going to.
William Rosener: Make sure I get the double quote i've got to tell you that i'm going to get.
William Rosener: attrition.
William Rosener: facts.
William Rosener: and close it so now the trick here is we've got one of these it's going to make sure we keep that double quote so i'm going to have my start my strength and close my strength concatenate a URL and nutritional facts okay same thing here and we'll start off with the image or i'll.
William Rosener: tell you i'll just again like I did a second ago.
William Rosener: Maybe at this point we'll also put a paragraph break.
William Rosener: And I should probably add one up here as well.
William Rosener: Just kind of it's gonna be a paragraph break take that information and slide it on down there okay so here's what we want for the image URL so i've got image space source equal i've got the URL of some image notice the double quotes here the nomad a paragraph break.
William Rosener: I want to place this.
William Rosener: Year image URL string.
William Rosener: source equals.
William Rosener: I actually want double quote.
William Rosener: right there.
William Rosener: So now, at this point i've got image source equal and i've got the double quote I now need to place the image URL.
William Rosener: Now we need to grab the remaining near which is going to be.
William Rosener: I want to start off with closing at double quote.
William Rosener: And then, a p.
William Rosener: Okay So hopefully we're getting close here with credit a produce drag a food group string a URL string an image URL string.
William Rosener: we're going to combat comm combine all these into one complete string we're going to do a string substitution replacing all those activities.
William Rosener: With double quotes and then we're going to print it out and most likely we're gonna have to make some changes here but let's check this see what's going on here.
William Rosener: It convert.
William Rosener: Information written everything's looking good here so far now for the big check here, so these two should be zero we haven't done anything with fruits and vegetables only thing we've done.
William Rosener: Is the foods and i'm glad to see it's not zero let's take a look here open with the see what it looks like in Google chrome here.
William Rosener: And there we go so probably about the only thing I could really see right off is I probably should have added a space.
William Rosener: After apple and fruit so maybe i'm going to come right back to my code.
William Rosener: i'm going to come on down here after the word apple right appear.
William Rosener: About put a space.
William Rosener: Nutritional facts so i've got a space there.
William Rosener: let's, let me just.
William Rosener: it's I convert.
William Rosener: Information written come back here and hit refresh and share it if I move that over just a little bit here.
William Rosener: Okay, let me check on nutritional facts and sure enough that's working no perfect okay so we've now got it working I didn't expect it to work on the first try here, but sometimes things happen like good so let's take a look here on.
William Rosener: we're going to close that now, what we need to do is we've got created a document for all the food groups now, this is going to be very easy in order to create one for vegetables and fruit.
William Rosener: loops let's come back here and design mode and we need to do is so we've got we're writing it all out, we always write it out to found number one.
William Rosener: Now i'm gonna do is i'm going to take a look at take a look at my food group.
William Rosener: In particular, to a fruit.
William Rosener: we're also going to write this complete.
William Rosener: Stranger.
William Rosener: To.
William Rosener: found number two loops.
William Rosener: Complete string here so food group is equal to a fruit.
William Rosener: Then we're going to write it out to some notice, I called this one file was too.
William Rosener: Okay, if my fruit, the food group is a fruit i'm also going to write it out to two and maybe just to save a little type in here.
William Rosener: And if it's festival.
William Rosener: Sure, you mentioned case or plural, so I call this fruit singular vegetable saying learn so make sure i've got vegetable here if that's the case i'm going to write it out to three so three was my vegetables, at my food group is the best of all the most going to write it out to three.
William Rosener: Okay cross your fingers let's check this out convert information written come back here great on least seeing they're not zero so we can Double Click on.
William Rosener: All foods again and that's looking perfect.
William Rosener: Just like it should let's take a look at the fruits.
William Rosener: Perfect again I can click on these that's going to take me to that website.
William Rosener: And finally festivals.
William Rosener: In perfect perfect perfect okay.
William Rosener: Take a look here let's just kind of wrap this video up here, so what happened is we've got some produce some food groups some URLs some images and have you guys add some more.
William Rosener: Food group and at least one more food group and add a few more produce go ahead and find a URL where they can get more information on again maybe.
William Rosener: Do something with a dairy you can talk about cheese or yogurt and then get an image and I don't care where you find your image and you could go one website is PICs of a calm.
William Rosener: And it's just.
William Rosener: fix ebay.com as shown right there.
William Rosener: Okay.
William Rosener: And you're going to add some additional information there so maybe just looking at the the code here if you've got into trouble for so always make sure you get the very, very top option explicit turned on.
William Rosener: The show where else you could go wrong just kind of keep looking at your kid individually, you should find them if you got a mistake, hopefully find them okay i'll catch the next video thanks.