WEBVTT 1 00:00:01.410 --> 00:00:12.269 William Rosener: hello, this is Dr burroughs there are welcome back this instructional video he learned how to create web pages from data contained in Microsoft excel file using bpa visual basic for the application. 2 00:00:12.840 --> 00:00:22.620 William Rosener: Microsoft excel spreadsheets are convenient place to store data but frequently he may want some of this data on a website organized and displayed on numerous pages. 3 00:00:23.880 --> 00:00:32.010 William Rosener: And in fact you want all your data stored in one file in this case being a Microsoft excel file, this is the master file, where you make all of your changes. 4 00:00:32.400 --> 00:00:41.760 William Rosener: Within execute a script in this case we're going to have a little command button it's going to potentially create hundreds of files, in our case, we have it create through files from this master document. 5 00:00:42.390 --> 00:00:47.370 William Rosener: Before actually taking a look at this let's go ahead and take a look at the issue website here. 6 00:00:48.600 --> 00:00:58.410 William Rosener: So here is the catalog and if I was not sure if it's in Microsoft excel or some type of database, but there's going to inevitably will be one file all this information to store. 7 00:00:58.950 --> 00:01:15.240 William Rosener: But now to make it convenient to access for users, we want to go to break it up, so when you press on the accounting it's going to take me to one page, you can see a different URL and if I take it was on maybe I come down here and choose the biology minor it's going to take me to a page. 8 00:01:16.410 --> 00:01:19.560 William Rosener: If I press maybe on chemistry. 9 00:01:21.090 --> 00:01:24.750 William Rosener: it's going to take me to another page and that's in fact what we'll be doing here today. 10 00:01:26.040 --> 00:01:35.670 William Rosener: Take a look at this before I press a creative DEMO pages we've got several companies and there's two categories, either in service or retail. 11 00:01:36.420 --> 00:01:40.740 William Rosener: i've got the URL and i've got an image of the particular company. 12 00:01:41.610 --> 00:01:54.810 William Rosener: Before pressing the button, let me go ahead and show you what we're going to be doing so i'm going to be stored in mind on the desktop just subdirectory called a tap i've got two files here, this is the finish line than here in a minute i'll be working on the student. 13 00:01:55.860 --> 00:01:59.730 William Rosener: When I press on the create html page it's going to create three pages. 14 00:02:01.080 --> 00:02:10.170 William Rosener: So if I come right back here it's going to create all the companies, then it's going to break it down and then one to some retail and one just on service. 15 00:02:10.830 --> 00:02:24.780 William Rosener: So one page is going to contain all six companies one it's going to contain just those in the service and another page will contain just those in retail and so Now let me go ahead and press on all companies. 16 00:02:26.730 --> 00:02:39.330 William Rosener: So it's going to first of all, place the company name and then i've got it in parentheses, whether it's service or retail and then a link I could press to go right to that particular website same thing with lows. 17 00:02:42.390 --> 00:02:45.810 William Rosener: Okay mailchimp an issue or maybe target. 18 00:02:47.580 --> 00:02:48.030 William Rosener: Okay. 19 00:02:50.280 --> 00:02:55.350 William Rosener: And that was the first one here that one was on six. 20 00:02:56.610 --> 00:03:08.640 William Rosener: To keep this thing, so one moment have 6am but i've also got one that's going to have just the retail companies okay there's just three tone and finally one just on the service companies. 21 00:03:11.670 --> 00:03:20.460 William Rosener: someone like an issue with age wars be creating three and one with all the companies one just for retail and one just for service them. 22 00:03:21.090 --> 00:03:28.980 William Rosener: And that was all by pressing one button here Okay, so in the same area as you find a link to this video, you should also find. 23 00:03:29.340 --> 00:03:44.940 William Rosener: kind of a template to get you going with all the data and the very first step i'm going to do is create a command, but if you're a developer tab is not visible or as on file good options press on customize ribbon and make sure your developer tab is visible. 24 00:03:46.290 --> 00:04:00.450 William Rosener: And i'm going to press on the on developer tab under insert on the right here and press on the command button and critic command on this one on the simply right click on this i'm going to go to properties. 25 00:04:01.890 --> 00:04:04.440 William Rosener: I could go see him D. 26 00:04:06.090 --> 00:04:08.280 William Rosener: Create html. 27 00:04:09.300 --> 00:04:10.770 William Rosener: pages and then maybe i'll. 28 00:04:11.790 --> 00:04:12.900 William Rosener: Change the caption. 29 00:04:17.370 --> 00:04:24.810 William Rosener: Okay, so I have here in a second when I Double Click on the code or C C and D create additional pages underscore click. 30 00:04:25.230 --> 00:04:35.100 William Rosener: But here's what appears on the screen and if you wanted to it looks pretty small I could press on font not that i'm too hard about this and get a little bit larger font size them. 31 00:04:36.900 --> 00:04:44.100 William Rosener: Okay i'm going to close them Okay, at this point i'm going to go ahead and Double Click on html pages and shut up it's taken me right there. 32 00:04:44.850 --> 00:04:59.430 William Rosener: In the save some time i'm gonna be doing quite a bit of copied and pasted here someone simply up, first of all come right up here and just paste and while this is going to be doing is declaring some variables i'll tell you and i'm gonna come up above this done and type option. 33 00:05:01.830 --> 00:05:02.430 William Rosener: Explicit. 34 00:05:03.450 --> 00:05:06.150 William Rosener: This way i've got a spelling mistake it's going to tell him. 35 00:05:07.350 --> 00:05:12.240 William Rosener: Okay next one i'm going to grab i'll tell you what i'm gonna say my path. 36 00:05:16.530 --> 00:05:18.810 William Rosener: equals lumps going to put this or second. 37 00:05:20.070 --> 00:05:23.520 William Rosener: And then i'm going to change the drive in the directory to that. 38 00:05:28.350 --> 00:05:33.000 William Rosener: Okay i'm going to take i'm going to come right here i'm going to i'm going to delete these. 39 00:05:34.830 --> 00:05:43.260 William Rosener: Because we're going to be recruiting them here in a second i'm going to position so i'm on the PC desktop my a tab on the right click and copy address. 40 00:05:43.770 --> 00:06:00.600 William Rosener: it's like I can't mess them again we come right back here and then within the double quotes i'm going to paste so here's what's going on on the specify my path desktop I mean yours is obviously going to be different here than a temp change the drive and the directory to that path. 41 00:06:02.160 --> 00:06:07.080 William Rosener: Okay, then i'm going to go ahead and and i'm just not sure whether to give you this code i'm still kind of thinking here. 42 00:06:08.280 --> 00:06:13.230 William Rosener: Maybe as an image we actually have to type it out, or maybe i'll just give you the code here. 43 00:06:14.280 --> 00:06:25.200 William Rosener: So we're going to have three files one is going to be all companies in this case, when I got six and it's not that impressive and i'm going to open that for file one. 44 00:06:25.950 --> 00:06:33.030 William Rosener: They weren't have just our service companies that's going to be filed to and then our retail and that's going to be three. 45 00:06:36.000 --> 00:06:45.870 William Rosener: Okay, tell you what maybe I could get you a little more code here and i'm gonna come along and paste this i'm going to set my room counter to to and why to. 46 00:06:47.460 --> 00:06:55.860 William Rosener: Okay i'm going to start in row two and i'm going to keep going as long as there's data in this case I could stop at seven but i'm gonna keep going as long as there's data. 47 00:06:57.540 --> 00:07:11.190 William Rosener: So, while not as long as this thing so row to come on some rope rope counters to somebody taking a look at cells row two on one, and while it's not empty. 48 00:07:12.660 --> 00:07:18.210 William Rosener: If I wanted to I could come way down here to the end I tell you what ladies ground these lines of code here. 49 00:07:20.400 --> 00:07:21.060 William Rosener: and paste them. 50 00:07:25.140 --> 00:07:40.710 William Rosener: So we've got a lot of work to do here in the middle what's going to happen is i'm going to open the files i'm going to open one, two and three and i'm going to close one, two and three and i'm just going to keep going through all of the data until I get to an empty cell. 51 00:07:43.740 --> 00:07:45.720 William Rosener: Okay i'm going to start up here taking a look. 52 00:07:46.950 --> 00:07:58.200 William Rosener: At most needed them unfortunately deleted those what I could do is come back here to the finished one and create the html pages quick and take a look here. 53 00:07:59.640 --> 00:08:02.580 William Rosener: So the first step, I want is the company name. 54 00:08:03.990 --> 00:08:05.880 William Rosener: And let me just grab some code here. 55 00:08:11.190 --> 00:08:12.360 William Rosener: And i'm going to students. 56 00:08:13.890 --> 00:08:17.070 William Rosener: Some write down in here and i'm going to paste that. 57 00:08:19.080 --> 00:08:26.280 William Rosener: we're going to find is it's sometimes easier and what i've done here is i've got a I want I need a double quotes here. 58 00:08:27.960 --> 00:08:29.490 William Rosener: And I need a double quote. 59 00:08:30.690 --> 00:08:31.200 William Rosener: here. 60 00:08:32.460 --> 00:08:41.730 William Rosener: What you find it as mommy to start mixing double quotes because actually need I want this to say begin here and enter here but i'm gonna put these back. 61 00:08:43.230 --> 00:08:57.270 William Rosener: And then, what i'm going to do is a string replacement So the first thing is i'm going to get i'm going to increase the font size to 26 and i'm going to i'm going to start with boldface close with both face i'm going to put the company name there. 62 00:08:59.400 --> 00:09:01.410 William Rosener: Okay next thing I want to service. 63 00:09:05.580 --> 00:09:07.890 William Rosener: that's going to fall under the category. 64 00:09:09.420 --> 00:09:11.670 William Rosener: So I take a look here here was the category. 65 00:09:15.840 --> 00:09:16.890 William Rosener: The pace that. 66 00:09:19.710 --> 00:09:27.240 William Rosener: And that's going to simply take that category report a parentheses before it prints see after it and it's going to grab the category. 67 00:09:29.520 --> 00:09:34.980 William Rosener: next thing is going to be if I come back here it's going to grab the URL and would that URL is a bone. 68 00:09:45.540 --> 00:09:55.530 William Rosener: So just kind of take a look here, the first one is going to say okay and grow someone so visually counters that to do two comma three. 69 00:09:56.730 --> 00:10:00.300 William Rosener: So row two three here's the URL. 70 00:10:03.060 --> 00:10:13.320 William Rosener: Okay, then what I want to do is i'm gonna say a for anchor hypertext reference equal and again I need this in the double quotes but at the same time. 71 00:10:14.280 --> 00:10:27.180 William Rosener: And with a bba I need to start my quote with double quotes so it gets a little confusing so much easier to simply a common approach is to use some other character, then we'll do a search and replace. 72 00:10:28.320 --> 00:10:37.860 William Rosener: So i'm gonna start my anchor hypertext reference equal equals, to the URL in that address i'm going to close the anchor that will put a paragraph break. 73 00:10:40.920 --> 00:10:43.230 William Rosener: Okay, then i'm going to grab the image. 74 00:10:51.090 --> 00:10:59.730 William Rosener: Okay, if I was to come right back here that is going to be stored in row initially to and row column for. 75 00:11:02.370 --> 00:11:14.640 William Rosener: Okay, and that's going to simply i'm going to put image space and i'm going to deliver it with the margins on the put the sources equal to whatever URL is in there, the nomad another paragraph, break. 76 00:11:18.210 --> 00:11:18.600 William Rosener: Okay. 77 00:11:21.810 --> 00:11:22.950 William Rosener: Then i'm going to come along. 78 00:11:27.750 --> 00:11:32.730 William Rosener: and say my complete strength, or what is my complaint strength it's the company history. 79 00:11:34.320 --> 00:11:48.570 William Rosener: One with the category string the URL and the image string so takes all four pieces and pieces come together now what I do is i'm going to replace all of these. 80 00:11:50.130 --> 00:11:51.840 William Rosener: And curly brackets. 81 00:11:53.310 --> 00:11:58.710 William Rosener: With 34 which is asked to value every Tuesday with a double quote. 82 00:12:01.050 --> 00:12:02.790 William Rosener: Okay now i'm ready to write it out. 83 00:12:04.830 --> 00:12:08.490 William Rosener: In the way I describe all these line that I haven't read this last line. 84 00:12:13.920 --> 00:12:21.660 William Rosener: Okay, so i'm always going to print number one what was number one that was all companies, so I always print a number one. 85 00:12:22.860 --> 00:12:30.630 William Rosener: Now what i'm going to do if category is equal to service, so if this was service service or service. 86 00:12:32.880 --> 00:12:37.050 William Rosener: And notice service, I said to file to. 87 00:12:38.250 --> 00:12:52.050 William Rosener: Set the category was service, I am going to print this so i've got this complete string i'm going to print it to two as well if the categories three i'm going to go ahead and print it side always print it to. 88 00:12:53.430 --> 00:13:01.740 William Rosener: A master file or an effect all companies, but then I also go ahead and print it possibly to service or retail. 89 00:13:02.130 --> 00:13:10.320 William Rosener: And then I also need to make sure I, so I don't have an infinite loop I need to set row counter equals row counter plus one, so the first time through row counters to. 90 00:13:11.040 --> 00:13:21.210 William Rosener: Then the next time next time through it's going to have a value of 3456 and it's going to keep on going as long as this is not empty. 91 00:13:22.740 --> 00:13:28.590 William Rosener: Again, and finally, what might be kind of beneficial, is it way down here at the bottom. 92 00:13:29.760 --> 00:13:38.040 William Rosener: How could we have done everything we're going to have a little dialog box that pops up information written and let me just so you guys know that. 93 00:13:39.300 --> 00:13:39.780 William Rosener: This is. 94 00:13:40.830 --> 00:13:41.790 William Rosener: The student version. 95 00:13:43.710 --> 00:13:46.320 William Rosener: Okay Let me close that. 96 00:13:48.180 --> 00:13:53.160 William Rosener: And let me come right back here, I am going to delete some files. 97 00:13:57.630 --> 00:14:11.610 William Rosener: And then we come over here to the student and the press on the design mode and the press create html pages information runs on I knew I wasn't using my finished one I use the one that is created and hit okay. 98 00:14:12.840 --> 00:14:25.080 William Rosener: i'm not going to turn right around, and I can take a look here are the files so here is all companies so i've got the images i've got the hyperlink right here, I can go right to the website. 99 00:14:27.480 --> 00:14:37.230 William Rosener: And i'll close that one but i've also got two other files here one just for retail and one for service if I press on that show not only get those with retail. 100 00:14:40.200 --> 00:14:50.100 William Rosener: But hopefully know this video you'll learn how to create html pages, I know this example was very simple we're only really creating a criminal master file. 101 00:14:51.330 --> 00:15:04.350 William Rosener: All companies and they were creating two other ones for service in retail, but the big thing here is, I had all my information in one location, but then i'm using bba to create various pages. 102 00:15:05.070 --> 00:15:18.420 William Rosener: Based on us and the last thing you want to be doing is, if you want to make some some changes running around trying to modify hundreds of documents, whereas, in this case, if I wanted to add another company, I simply come right up here. 103 00:15:19.500 --> 00:15:31.860 William Rosener: The company's name, what type of category, they are their URL and then i've got these images and I hit the current page and then I simply need to do is upload all this in this case i'm simply upload. 104 00:15:33.330 --> 00:15:46.650 William Rosener: History files to my website and i've been business well, hopefully in this video again you learned how to create web pages or html pages you seen from a Microsoft excel phone using GPA and i'll get to the next, video thanks.