Excel VBA: Automate Excel, Dynamic Templates, Send Emails, PDF Form Filling, Selenium | Yuri Douglas, CPA | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Excel VBA: Automate Excel, Dynamic Templates, Send Emails, PDF Form Filling, Selenium

teacher avatar Yuri Douglas, CPA, 9,000+ Students | CPA | Online Educator

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

45 Lessons (4h 9m)
    • 1. Introduction Video

      3:04
    • 2. Welcome to the Course and Course Structure

      1:20
    • 3. Adding The Developer Tab

      0:45
    • 4. Modules And Subroutines

      3:56
    • 5. Declare Variables, MsgBox, Debug.Print

      7:38
    • 6. Operators

      7:06
    • 7. If Statements

      4:48
    • 8. Interacting With Cells

      6:02
    • 9. Loops (For, For Each, Do While)

      7:32
    • 10. Built-In VBA Functions

      4:58
    • 11. First Programming Exercise - Highlighting Cells

      5:01
    • 12. Worksheet Codenames

      3:54
    • 13. InputBox

      2:32
    • 14. With Statement

      2:40
    • 15. Object Variables - Early And Late Binding

      5:16
    • 16. Functions

      9:31
    • 17. Index + Match

      5:21
    • 18. Re group Rows Using Numbering & Filtering

      1:55
    • 19. Select and Delete Blank Value Rows

      2:47
    • 20. Wildcard Character in Formulas

      2:22
    • 21. SUMPRODUCT

      4:34
    • 22. Dynamic Template With Index + Match

      2:53
    • 23. Dynamic Template With Automated Filtering

      8:03
    • 24. Loop Through The Dynamic Template

      4:38
    • 25. Automate Saving the Dynamic Template To PDF While Looping

      5:15
    • 26. Send Outlook Emails With Attachments

      10:30
    • 27. Send Gmail Emails With Attachments

      6:51
    • 28. Selenium Download

      2:43
    • 29. Learn HTML

      6:23
    • 30. Web Inspector

      2:16
    • 31. Automate Internet Explorer

      13:45
    • 32. Automate Chrome (using Selenium)

      10:12
    • 33. How To Find A Website's API

      4:56
    • 34. Learn JSON

      6:13
    • 35. API Tester

      4:43
    • 36. Download JSON VBA Libraries

      2:13
    • 37. JSON Data From An API (No Browser)

      12:05
    • 38. XML Data From An API (No Browser)

      9:23
    • 39. HTML Data From An API (No Browser)

      5:44
    • 40. HTTP Cookies From A Website

      7:34
    • 41. VBA With Multiple Excel Files

      6:04
    • 42. Loop Through Files In A Folder (Two Techniques)

      6:25
    • 43. Find Word(s) In A PDF File

      6:27
    • 44. Merge PDFs Via VBA

      5:16
    • 45. Automate Fillable PDFs

      4:58
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

97

Students

--

Projects

About This Class

Take your Excel skills to the next level by learning VBA coding. Become more productive and efficient!

  • Write 10+ VBA programs to automate your daily tasks
  • Learn VBA fundamentals & syntax (loops, if statements, modules and more)
  • Create dynamic Excel templates
  • Automate saving numerous Excel templates as PDF or Word files
  • Send emails with attachments from Outlook and Gmail
  • Automate Chrome & Internet Explorer (using Selenium) for web tasks & web scraping
  • Populate and update multiple Excel files using a single VBA script
  • PDF form filling
  • Pull data from web APIs into Excel
  • Web scrape using HTTP requests
  • Parse text in a PDF
  • Dynamically split and merge PDFs
  • Loop through files in a folder
  • Mass rename and mass copy files
  • Learn about HTML, JSON and XML

These are real VBA coding examples that I use daily as a CPA accountant and with hundreds of clients. This course is geared towards Excel users who want to increase their coding skills by learning real world business examples. If you have no coding experience, this course teaches the basics of VBA programming (loops, if statements, modules and more). Building onto this, you will learn how to create useful VBA programs to automate your daily tasks.

Every line of code in the course includes comments, so you're not left guessing what each line of code does. Also, a video is included for every coding related section.

You'll learn how Excel VBA can be used for a lot of tasks beyond just Excel. By the end of the course, you will have all of the scripts and knowledge to implement VBA programs from scratch.

Don’t spend another minute performing Excel tasks manually. Enroll now and learn how you can get Microsoft Excel to work for you.

Meet Your Teacher

Teacher Profile Image

Yuri Douglas, CPA

9,000+ Students | CPA | Online Educator

Teacher

Hello, it's nice to meet you! I am a San Francisco-based CPA accountant, freelance programmer, and online educator.

My love for Excel and VBA began while working as a CPA accountant in the private equity space building programs to save time. Using the knowledge I've gained over the years through blogs and trial-and-error, I've been freelance programming for hundreds of clients on Excel, VBA, Python and Google Scripts projects.

My courses aim to condense my years of experience into bite size chunks. My courses will help to make you more efficient, marketable and increase your earning potential! The combination of business and coding is very powerful. Yet, very few individuals have both skills.

Feel free connect with me on LinkedIn and ask me any questi... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction Video: My name is Yuri and I'm the instructor of this course. I am a CPA account and freelance programmer. I created this course to help you master Excel VBA using real-world examples. In this course, you'll master BBA fundamentals, such as modules, subroutines, loops, if statements, and more. Then you'll create over 10 VBA programs that you can use in your daily life. Let me show you what I mean. So here we have a meetup payroll template. And we have this super value here. And as I move the value around, you'll see that we moved to this Steve, our person, all the values here updated and rows hit UNH ID automatically. I didn't do anything. The code just added automatically for us. And so you'll see as I update this, the other values the code keeps updating for us. So this is something you'll learn. But we'll build on top of this. We can have the code automatically update this value for us. And then as the value's updated, this script will automatically save this template here as a PDF into a folder that we specified. So for instance here, my folder structure is currently empty. And then as the PDFs are created, we can have this script, great emails and attach those PDFs for us. So it might look something like this. So the emails are being created on a separate screen, so I'll just pull them over real quick and we'll see that the emails are being created, the PDFs are being attached. And I can show you, show you my folder structure here. And you can see the PDFs are being created. So this is one thing we'll learn how to write together. Another example that's something you'll learn how to write is how to automate a web browser. So I have a made-up blog here. And the goal is to loop through these posts and pull in the information into the cell. Then also to click around and maybe add some text here just so you can see how it's done. So we're going to add in the host name here and the post hyperlink reference here. So it might look something like this. The browser got open on to a different screen. Stop, pull it over. And you can see that it's looping through the posts. It's pointed in the information into Excel, which you'll see once the code is running, you'll see that it added text here. Once this closes, you'll see the information in here. And I use the lasers to slow things down, but the code can be much faster. Last example that I want to show you that you learned in the course is how to write a script that will fill in a fillable PDF form. So we have a 1099 here, and these are the fillable fields here. And so you will learn how to write a script that will take in this information is the payers name. So Tim see made-up address and box1 would just be some numerical information. And this information will get entered into the fillable PDF form and we'll create a new PDF. You'll also learn how to write loops. And so that way you could extend this and add more data and create a bunch of fillable PDF form. So it might look something like this. So after I hit run, a fillable form was filled here. And then I'll open it. You'll see that TNC was in fact added ER, and the numerical information is here. And then we also check this box here. 2. Welcome to the Course and Course Structure: Hello and welcome to the Excel VBA course. My name is Yuri and I'm the instructor of this course. You will learn VBA programming using real-world business examples. The course is structured as follows. First, to be guided through DBA fundamentals. This will teach you the basics of EVA, such as subroutines, modules, if statements, loops, operators and functions make you, after just a handful of videos in, you'll have your first coding exercise. This will take what you've learned so far in the course, create a simple VBA programming, highlight cells and the cell has certainly minute. Also you'll learn to create a custom EVA function that you can use inside your Excel file. Next, The greatly build onto your VBA fundamentals to create real DBA programs. He's VBA programs for business focus. So you can see how VBA can be useful in your everyday life. For example, you'll pray dynamic Excel templates that hide and unhide rows automatically as you move around to different employees. Also, the data in the Excel template automatically refreshes. You will expand onto the Excel template by learning how to programmatically rhomboids and save the template as EDF and the folder that you specified, then create an email with that PDF as an attachment. If this sounds like a lot, please don't worry. You'll be taught in chunks. So let's begin. 3. Adding The Developer Tab: In this video, we're going to add the Developer tab. So if you don't have the Developer tab up here in your Excel file, what you can add it is by clicking any of any of these buttons here, just right-click one of them. And you should have this option to customize the ribbon. You'll get this pop up and just make sure again, your Customize Ribbon over on the main tabs. If you're not on the main tab, just make sure you select them. And then just click and check developer. And then now that you have the Developer tab, you can click on it and then go to Visual Basic. And mine opened up on a different screen, but I'll just pull it over. It should open up something like this. Another option is you can also hit Alt and an f 11 will also open it. 4. Modules And Subroutines: In this lesson, we're going to talk about modules and procedures. So module is an area where the code is written. And so the way we insert a module as you go to Insert Module. And I would point out the other two main ones are user farms in class modules. I won't go too much into detail about these because we won't be using that throughout the course. But as you get better at scripting, I just want to point out user forums, you can create really custom user inputs so people can put in tax, radio buttons, drop-downs, things like that. And so when users enter those kinds of things, you can write scripts based on user's inputs. But again, I won't go too much into detail the way we inserted modules here. And we can see a module is created here. One thing I would point out down here is the name of the module. In most cases, I would suggest that you rename this to be something useful if someone else is looking at your code. It's easier for them instead of looking at Module one to look at some name, maybe it's an Outlook e-mail creator or something like that. So that way they know exactly what they're clicking on. And so we have this blank canvas here. This is where we'll start writing our code. Before we do that, I also just want to point these two out here. So that way you guys, as you can see, I've just so you know what this is, you can double-click this workbook and you can actually write code in here, which we will use later on in the course. So if you want to write code or the entire workbook, you do it here. And you can also double-click sheet, she won here. And you can also write code in here that interacts with that worksheet. And I would point out here, summary is the worksheet name and sheep one is the code name which you can change down here. And these are important to know, but we will go more about code names later on in the course, but I just wanted to show it, showed that to you now. So that way you're familiar with it once we get to that section. And the reason we would create a module rather than writing code in here is modules are just more generic. You can export them, you can copy the code into other workbooks. And it's just that better practice to write code in modules. So the two main ways of reading scripts in VBA is through subroutines and functions. In this lesson, we'll just talk about subs just to start. And subs in functions or procedures which are a group of statements executed as a whole, which instructs Excel how to perform a specific task. In other words, we're just going to write code and then Visual Basic for Applications or VBA will execute that code for us. So we're giving VBA instructions for something to do and then it will execute that. So the way you create a sub subroutine is just using the keyword stuff. And then after that, you can literally make up any word or text. It just can't have spaces. So if you do want to have a space, use an underscore, so like case. And then when you hit Enter, it will automatically create these parentheses. We'll talk about the reason for the parentheses later on when we talk about functions. But I just want to point that out. And you will see a lot in Visual Basic for Applications still be some sort of n statements. So if we're creating a subroutine sub here, they're also being n sub, which just tells the code, this is the end of the script, stop running. And then one thing I also want to point out is comments. So if you want to write comments, describe what the code is doing. You just put an apostrophe and then you write your text here and you'll see it becomes green change. That's tests. And you can write as many possibilities as you want. If you want to add more detail, you can add it. And patient by adding two apostrophes are three. And so this won't be executed when the code runs. But it's still good to add comments just so people know what your code is doing. And so any of this whitespace in between sub and then SAP is where we'll start writing our code. So that's it for this lesson. 5. Declare Variables, MsgBox, Debug.Print: In the last lesson, we talked about how to create subroutines and also how to answer a modules. In this lesson, we'll talk about how to insert and declare variables and the reason you've created variables. So you can assign values to that variable. Sometimes you need to create operations like multiplications or extract parts of a string from a variable and you can update it. So a variable is a named memory location used to hold the value that can be changed during the script execution. So again, you're just assigning values to that variable. And then you can interact with that variable for different operations. So the way you declare a variable is we'll go into our code here, again, in-between sub and test case here. And that's we'll write our code, can't be outside of it. And so the way you declare a variable is used DEM. You can make up any text here, and then you use the keyword as. So usually I would suggest variables to be named it something that you can understand. So that way if someone else is looking at your code, they can understand what you're referring to. So like let's call this thin name. And then once you hit space, you'll see this big drop down at potential variables you can select from. I won't go through all these, but I just want to show you a couple that you'll use a lot for the current one is string. And string looks exactly like this. String is just a string, a patch. So I'd say person's name. How about we use my name? And I didn't mean to have that apostrophe there. Another one, we can use them. Let's use a number, remember as integer. So you can use integers, a very common one, and we'll say number equals 2. Another common one is boolean. We'll say true ofs. And Boolean is exactly that. It's just a true, false. So we can copy this. And so this can either be true or it can be false. So we're using our comments like we talked about in the last section by using an apostrophe. And the last one I want to show you, which is kinda hard to demonstrate right now, but we use later on is objects. And objects are nice because we can set them to be something like Internet Explorer browser, or we can interact with Outlook, for instance. So objects will have other procedures you can call other methods and then inherently they'll have also value. So like think of an outlook object might be able to create an email. And then you can also extract v2 field, the blind copy field, carbon copy field, subject line. So that's how you declare variables. Now, let's talk a little bit about message boxes. In debug print. You would use these is so you can visualize what, what you're assigning to your variables. So if you have a really long code or script, you just sometimes one is checked, hey, our values being assigned properly, my variables, because you might have some complex operations going. So let's delete this for now. The way you write message box is just like this and SG. And let's test for true, false. And we can see that it returns true because that's what we set it to. Now, if we want to see what value of number is, we'll just update this number. And we'll see number is two. This can be handy to check what are the values assigned to your variables. Also, inherently, a good reason to use message boxes if you have a complex code, as you saw with the script, it's stopping the script every time the message box, message box prompt comes up. So if you need to stop your script for some reason, maybe you need a user input. So if you have like a login prompt on a web browser, maybe you use a message box that tells the user click Okay, once you've logged in and then all the code after, like out here with executed after they user, it's okay. So I'll show you what I mean. So let's use Message Box number and a message of oxygen. So we'll see there's a prompt first that will come up for this message box for the number once we hit Okay, then this code block queue, which will show a message box for true false. So we can see our number is two here. And then true and false came up after we hit. Okay? And we can see the code is not running, nothing's happening until we hit OK orange, we exit out. Another way you can visualize what variables are being assigned with Basie debug app. And the way that you've you debug print is you can hit View. We're going to immediate window. This window down here will pop up. We'll minimize this a little bit so it's easier to see. And then once you hit Run, we'll see that number here To says, as we expected is showing up here. Unfortunately debugged up print doesn't automatically erase values that you've looked at before. So we can just do Control a which is flexible, and that will delete. And then we can double-check for true, false. And we can see that it shows here. And the reason you've used debug dot print versus message box is debugged. Footprint doesn't stop your code from running. So if you want to just check values, but have your code keep running, and then just be able to visualize what are the values being assigned. You could just use the debug doctrinal. And so I'll show you what I mean. We'll use to debug dot print one for number one for true, false. And we'll see that the code doesn't get stopped. What's going on? Up there we go. And so we'll see true. And two, so the code just ran for us. It didn't stop along the way and it shows us our values. And the last thing I want to show you what variables. And one thing we'll use throughout the course is we'll add a new line. And right above ourselves, we're going to enter the keywords Option Explicit. So what Option Explicit does is it tells R code in this module that every variable must be declared. So for instance, if you do not declare this variable, VBA will automatically just assign this as a variance. Variant is sort of a catch-all variable. It can be assigned to any value. Problem is, variance will inherently be assigned to that maximum memory allocation from BBVA. And so your code might start to use a lot of memory. It's also good just to assign the type of variable you expect that variable to be assigned. So that way if this was a number of being assigned to person name, they would give you an error and you would say, oh, person name should be a string of texts, which should be a person's name, not a number. And then I'll tell you that something's wrong with your code in a way that it's written. So I'll show you what I mean. So we have person named here and what if we just made up person name one? So let's make this scheme. For instance. We'll see person name is declared here. Person name one is not declared anywhere. And so we'll see what happened is variable is not defined. And so a good reason to use Option Explicit is it forces us to declare our variables. But also what it does is it helps us ensure that there's no typos in our code. So for instance, if you would declare person's knit person name, and what if you accidentally added an S here person names, then we would get an error. And you would know that you had a typo in your code. So that's it for variable declarations, masses, boxes, debug dot print, and also Option Explicit. 6. Operators: In the last lesson, we talked about how to declare variables and also how to use debug dot, print and message box. In this lesson, we're going to talk about operators. Operators allow us to compare values if they're the same, if they're not equal. We can also use the summations like addition, subtraction because steam applications. And we can also do concatenation, which just means combining two strings together. So operators can look like this, which is equal to carrots, is not equal to. And then we could do the typical greater than, less than concatenation. We will use an ampersand and then we will talk about its audience. Not AND OR. So first things first, let's compare the value of person name. So person's name equals. So let's double-check. Does this actually equal URI? So we don't put anything in front or behind this statement here, all we're doing is just assigning a value to a variable. And if we compare it like this, debug dot print, once you have something before your statement here, you're not anymore declaring a variable or assigning a value to that variable. Here instead comparing this as a logical operation. So if we go back to View input window, which will open up our immediate window here, we can check this value so it's going to be equal to true. So what if we add an extra letter at the end? You're yea. Well, we can see now it's false. And let me show that again just because the text at the bottom was a little bit odd. So now we can see it's equal to false. So that's one way we can compare values and we will eventually use them as part of if statements. So we can do logical comparisons and that if a value is equal to or not equal to another value, we can write additional code to execute. And if the opposite is true, then another piece of cardboard. So actually let's compare one other thing, not equal to two. So this should return false because person's name is European, so this is not equals to. And we can see this is false. So now we can also do numerical operations and comparisons. So let's try this debug that number is greater than, equal to one. So the answer should be true here as two is greater than or equal to one. And then if we compare it as two, it is also equal to two. So this is true. And then if we compare that just purely greater than, this should be false. Exactly. Number two is not greater than two, it's equal to two. So that's another comparison that we can do. So another one that we talked about was also AND, OR, and NOT. So one way we can use not, for instance, not turns a true to a false and a false true. So we can compare parentheses. We'll do our logical operations and number 2. In this operation here, number is in fact equal to true. So this will evaluate to true. And there are not operation will convert that true to a false. And that's why we'd expect here are true became a false. And then if we do the opposite, this will convert to false because number is equal to true, NOT will make it true. And these can be helpful for logical operations. Again, if statements or things like that, or maybe you just need to turn a true to a false or vice versa. We also can do ants. So if we need to compare multiple values and double-check are the operations both equal to some value? And this is the way you would do it. So what I mean here is persons name is going to equal to true here at URI. And we also wanna make sure that number. Sometimes you have logical operations where you need two values to be true or something to execute. And if one or the other is false and it won't execute. So we can see this is true for sname is equal to 0 and number is equal to two. Now what if we say number of equal to P3? This should return false because although person named Yuri number is equal to two, so this is false. This is true. So both of them are not true. So this will be entirely pulse. And that's what we see here. So the opposite or not the opposite, but sort of similar we can use or in, or as you can probably guess is one or the other is true, will return. The entire statement is true. If both are false, then it will return false. If both are true, then it also returns true. Because again, anytime there's a true statement in the or statement, it will return true. So in this case, person name is equal to the number is not equal to three. This will still return true because one of the statements is true, this one. But in this case, if we add you're a at the end, both of these are going to return false will be false here, and number will not equal to three. So we get false. So that's how we use logical operators to compare if things are true or false, equal to or not equal to. We can also do numerical operations. So we can do addition. We can say number equals number plus two and then debug option. So we know number here is equal to two. We're going to take the number value. We're going to first add two to it, and then we're going to assign it back to the numbers. So two plus two is four. So this should return four. And that's what we have here. We could also do multiplication. So same thing here. 2 times 2 is equal to 4. This should also return for, and that's what we have here. And the last thing I want to show you is concatenation. So you might have done this in Excel formulas combining two strings or two cells, the other to get a different string or combining the string. So the way that we would do that, Let's delete this. Let's take person name. And then I'm just going to type this out real quick. So we're going to take person name and let's append a string to it. Let's put your eat and it will add a to the n. So as you can guess, we're going to take person name, which is URI here. And then we're going to concatenate text a here. And it'll debug dot print. This. One thing you can also do is you can append numerical values. So as you'll see here, it's a URI and one, and you don't need to convert it as a string like this. That's probably one of the unique things about EVA. So that's it for operators. So we went through how to do logical operators. We did AND, OR and NOT. We also did equal to, not equal to, greater than, less than. We also did summations, multiplications, and then we also did concatenation of strings. 7. If Statements: Now last lesson we talked about operators. So equal to, not equal to less than, greater than things like that. So in this video, we're going to show you how to use if statements and using those operators that we used in the last lesson, we can see how we can execute codes based on operator conditional value. So if something's equal to something, a piece of code will run. It not equal to something else would run. The way that you write an if statement is just like it sounds, it. And then you have something you want to compare against. And this case we've declared person name as a string and we put it as URI. And we can check if this is equal to 0. And then we end it with Ben. And then once I hit Enter, you'll see there's, if it becomes a different color, this and that then becomes a different color. The way we end our if statements similar to end here for any subroutine is just going to end it. And so anything in between here will execute if this logical value is true. So in this case, let's use debug document. And so we can say debug dot print this year. And so that's what happened. Because person1 name is equal to URI. We have this as URI. Now what happens if this value is not true? Well then we can use an else if, so it looks like this else if, then we can compare a different operation. So maybe you say firstName equal to Steve. And that again we have to end up with, then we can say keep up. This is feed. So again, person name is still set to URI. Uri. Now let's have a URI to Steve. And then we'll see this is Steve. So first what happens is this gets compared. If this is true, then this will execute. If this is false at the next else-if statement. So the next else-if statement is also not true, then just nothing runs because we go to add it. Now what happens if we just want to say, well, if none of those become true, then we just want to run something anyway. The way you would do that as Alice and the difference with Allison's, you don't need to put them at the end. And so we can see here LCP bugged out prints. This is someone helps. So let's put an a again to Steve. So none of these will execute true. So what's going to happen here is this will first compare. This will be false, and this will compare, this will also be false. And so we're saying else there's nothing being compared. So no matter what, if these conditions are false, then this whole run this to someone else. And just so as a reminder, this section, you don't have to put an else-if, if you want purely just emit and analysis, you can totally do that. So I'll leave this here just to show you. Again, this becomes someone else. We have Steve. Same thing will happen. This is someone else. And then if we go to URI and this URI, now I want to show you how to also use numerical comparison comparisons. So we'll declare this number, will change this to be an integer. So that way we can make it a numerical value of this number. Let's make this. So if number is equal to 2, and we'll say this is two. And then we'll say this is not to delete the text down here. So when we ran our code, the text down here shows this is two because the number is in fact two. But if we make it equal to three, then this else statement should run and say this is not too. And so that's what happens, is this is not to fill, delete this here. We can also do comparisons of greater than or equal to. So we'll say numbers greater than or equal to. This is greater than or equal to. We'll save this is less than 2 because it's not equal to 2 and it's also not greater than. And so that's what's going to happen. This is greater than 2 because this executed here. And if we make this equal to one, then this is less than. So that's how you can also do comparisons of greater than, greater than or equal to, less than or equal to. You can also do a nodule as well that we talked about before. And so that's how you can run if statements to help execute different codes based on logical comparisons. 8. Interacting With Cells: In the last lesson, we talked about if statements. In this lesson we'll talk about how do we interact with cells within the workbook. So we're going to create a variable person named as string. And in our Excel file here we have renames IV, carry, and Mary. And we just wanted to say how do we grab the values from those cells? The two ways that you can interact with cells is between using cells or range. So I'll show you what I mean. So first name is equal to 0. And you'll see there's a row index and the column index. So the row goes first and then the column gossiping. So as we see, row one, column one should be Steve. And we can double-check if that's the period. So we'll look at debug dot parent person name. And then it, when we run, we'll see it's Steve. We want to go to row two, will go like this. And then if you want to go to row 3, go here. And then row four should just be an empty value, which is what we see here, because nothing is being returned. You should also return the value of that cell this way, but you don't have to. If you don't put the value at the end, it automatically just return the value of the cell. There's also other things you can do like interior, for instance, interior and a color. We'll change the interior color. And I'll show you how you can look at those. When we use range. Before we go to range, I just want to show you you can also, instead of looking at a numerical column, you can also use the letter. So letter a here. And if we look at one, and we'll still get Steve, and row two should be carry. So that's one way we can interact with cells. And before we move to ranges, one thing that I would always suggest doing is if you just use cells here, it's going to look at the cells and the active workbook, meeting book one that we're in. But if you have multiple workbooks open based on whichever one has active your script my interact with. But we should tell our code actually interact with a specific worksheet. So you can do this in a couple of ways. You can do worksheets. And you can use an index which is numerical, or you can use the actual worksheet name, numerical, meaning, you can go like this. So worksheets, one should be our person worksheet. And in this case we only have one worksheet, which is our summary one. So when I run this shell still will return carry. But since we don't have more than one workbook, if I connect this to the should give us an error. Because it says subscription at a range. What that means is there's not two workbooks to identify. You can also interact with worksheets by using the worksheet name. So the worksheet name here is summary, which you can see here. So the same thing should be returned here. Now we have Kerry. Later on in the course, I'll show you how to use code names. And as you can probably guess, we'll use the code name here. And that's what I will suggest we use throughout the course and throughout all your scripts. But for now, let's not go too deep into that. Another way, you can act or interact with Worksheet values or other properties of worksheets that you can use range. And you can probably guess what range means. Range is a range of cells or you can just be a salad salad. So we can use a range A1. So you can use ranges because it's easier to depict what you're trying to, you're trying to interact with. So A1 is easier to the eye than cells. One comma one. If you have a new user to BBA, they might not necessarily know what sells one, what means. So let's see what this does. So again, this is Steve. And the nice thing. You can also use concatenation that we talked about with our operators. And so you could do an operator like this. So this will be eight and we're concatenating one. So this will still be range A1. And you can even create a second variable, then number as an integer. And we can first make number one and we concatenate the value of that number. So this will be a and this will be one. And so this should still return steve of interior, sorry, integer type that. So we still see this is Steve. And we'll see how to use this in loops. So we can start looping these numbers in future lessons so we can go through a bunch of cells all at once and our code, rather than typing this out one by one. Now the other thing I want to show you is you can interact with beyond just values. So if I use period, you can see there's a lot of things we can do here. We can grab the address of the cell like this. And so this is no longer going to be a person name. This will actually be the address. So address, which we'll see as a11 because that's what we're interacting with. There's also a lot of other things. One that we'll use later on as interior, thought this out. And we can use color. And this is how we can change the fill of that cell. And that's what we'll use later on, highlights themselves in the coding exercise. If I wanted to show you in this lesson how you can interact with cells, grab their values, grab formatting. If you want to practice with that. You can also grab the address range or the cellular on. And then these are two ways of interacting with cells. One is you can use the range or you can use cells. And later on we will use cells because it's easier to loop through ranges of cells because it's numerical anti-vaccine meeting your row is numerical and typically your colonists numerical as well. So that's it for interacting with cells. 9. Loops (For, For Each, Do While): In the last video, we talked about how to interact with styles and grab the values from those cells. So I'll show you again this is our, what our worksheet looks like. If cells A1, A2, and A3 is Steve area Mary. So we showed in the last video how to interact with those cells. But what if we wanted to loop through those cells so that way we don't have to numerically hard code. Number here is row 2 or row 3 or so on and so forth. What if we actually wanted to loop through all of the values in these results? So let's talk about how to use loops. So here we're declaring a variable. We're just going to call it number. You can make this anything you want, but we'll say it's number. We're going to start with number 1 because we're going to start with row 1. So I'll show you the different ways to use loops. One way to use looses, we use a for loop. So we'll say for number equals one. And then you can tell it where to go. So as where n, so we'll say it's it. And at the end, you can either use the word next or you can have the variable name at the end. And I suggest using the variable name at the end. So this part is not required. But I would suggest using it just makes it easier for you to visualize when you're talking about in next loop. Sometimes you might have three or four for-loops nested into each other. Meaning you'll have maybe another for loop inside this for loop. And so it's easier for you to figure out which next loop are you actually. So in this case, if you're using a for-loop and its numerical, you're telling it where to start. The technically this line of code is not required, but we can still leave it anyway. And so what we'll do here is we'll use debug dot fans. We can use cells. And so we're going to loop through the rows. So we'll use our variable number here. So again, we're gonna start at one and then as this loop through of us number two and then number three. And we use column index one or a, whichever. We'll add dot value here. And again, as part of the last lesson, we don't need this top value here. You can exclude it if you want, and it'll return the value anyway. But just so we can see what's going on, and it makes a little bit easier to visualize some of grabbing the value. So let's try to run this so we can see exactly what happened. So we started at number one, Steve Perry married. That's exactly what happened here. Another way to loop through cells is you can use a do while loop. So do while is exactly like that. Do-while. And we'll end with loop. And then now we need to tell this code, what are we going to check every time this loops, make sure that this cocci confronting. So that's essentially what's happening. This will loop once and it'll go back up to this section of code. It will check what is the operation you're comparing against. And as long as that is still true, then it'll keep looping. And once that operation becomes false, then it will stop looping and go to or any code that you have your afterwards. So in this case, we'll say two while number is less than. What we could even do, less than or equal to three, then we're saying number equal to one. So it's definitely below three are equal to at least, but below or equal to. And the key with do-while loops, as we need to make sure we turn increments some value or turn that value OR operator here to true or false at some point. So if we don't do anything here, in other words, number is going to keep stains. One is going to loop here. Number will be one and the code will execute. It will loop back here. Number is still stay as one. And so it'll become an infinite loop. And what we need to do is we need to increment or add a value to number. So every time it loops, we're going to add one to the number. So we'll start at one. We'll use our debug operand here. The number will increment by one, so it becomes two, and then so on, so forth until eventually it'll hit F4. And then this do-while will not execute a market because it'll become false. So that's exactly what happens. Another thing we could even do is we could just comment out number. And one thing I like to do is we could just say do-while cells. Number is not equal to blank. So in other words, the value in the cells and not blame. Let's just continue with our code. And once the cell value becomes blank, then we'll stop. So in other words, what we're saying is, let's keep looping through these cells intelligent to a blank value. And so that way we know that we've ended. And so we can do that as well. So this is an example. I'm sorry, I didn't actually declared number. That's what happened here. So the value in row 1, column a is not an empty string. So we're going to execute this code. We're going to increment our number by one, and we're just going to keep looping. And the last type of loop that I want to show you is a for each loop. For each allows you to loop through objects, ranges or cells. So in terms of objects, you could iterate over through say like an HTML elements. Maybe if you have a bunch of paragraphs on a webpage, might want to loop through all of the paragraphs, which I'll show you later on in the course, how to do that. But for our simple scenario here, just to show, and it's part of our BBA basics. Let's look through cells. So we can declare this as themselves. Fell loop maybe. And we'll make this a cell. Or actually it would be a ring. And again, Arrange can be a range of cells or it can just be a satellite cell. So we no longer need this. And we don't want her to eat this or this. And for-each just like it sounds, is for each fell swoop in. And then we use a range of cells. So maybe we want to go through cells A1 to A3. And what's going to happen in this case is every time we loop and we need to end with our next. And then we put a variable name. But what's gonna happen is it's going to loop through each cell in this range, because again, we're, we're declaring this as a range variable. And so we're going to loop through every cell in this range. And so that's what's going to happen. But the ones loud, some small difference here is we're looping through the cells. So every time we loop through this cell loop will become a cell or a range of cells in this case is one cell. And so we can actually just interact with its properties if that makes sense. So think of this as cell A1. On the first loop. We don't need to declare that we're interacting with the salads because this is already itself, has its own properties. It has its hear your fill color. It's got its own formatting. It's got its own value built in. So we can actually just interact with it like this. And now we're interacting with the cell and we want to grab its value. And so that's what we see happening here. This is looping first through cell one, is then going to sell a2, and then cell A3. So those are the three most common looping types. There's four loops, do-while. For each. 10. Built-In VBA Functions: In this lesson, we're going to talk about some built in VBA functions that are really useful for us to use. And so that way we don't need to rewrite functions. We can use them to do simple things that we might need to do and RGBA codes. So let's start. We have our variable declared here at number is integer. And one of the most common ones we will ever uses LEN, which finds the length of a string. And so let's check this. Let's see what is the length of this string. And we'll debug that print. We'll use another. So this should be four because the length of URI is four. If we add a to the m, then this should become five. So this is really useful to check if a string has some link to it. In other words, it's not an empty string. So in this case, an empty string should just be 0, which is what we have here. This is useful to check if a string has a length and if it's an empty string, maybe there's an error occurring or maybe we stop running a loop, for instance. Another one that's really useful is in strength. And we'll see that the arguments it takes is the starting position. It takes the starting position as we see here. And so you don't have to enter a starting position. If you just start with your string, then this will just be ignored. And then string one is the string you're comparing against. And string two is what are you looking for in that string? And I want to talk about the last argument here. So let me show you an example. If we're looking for URI and we're saying, what is the position letter I. Let's figure out what position that's n. And that's position four, so that makes sense. But 1, 2, 3, 4. And then if we go to you, and that should be two, and that's what we have here. And if we look for X for instance, which is not in my name, it'll give us 0. So this is useful because we can check to see if a certain letter or maybe even number isn't a string. And it's thought that maybe we do an IF statements and run a different piece of code. Also, we can check to see if we need to parse text. And so we can find a position of a string and then maybe parse part of the string out and start at a different position in that string. So that way we can maybe find something inside of that string. So think of this more as a really long string, maybe an HTML code or some other source code. And maybe we need to find something in that source code. And that's one way we can parse through HTML code or something later on in the course. So those are two really helpful ones. Len. So length of a string and INFFER is looking inside of that string. Now let's look at ones where we can actually parse. So let's put a person named as String firstName. Well again, you just keep my name is Yuri. And what if we just wanted to grab the first two letters of this test? What you would do that is you can use LDF, heat lamps and then person's name and then the length that you want to extract. So he came to describe to you. We can see if each person should give us just y and u. And so we can use INFFER before and then left using this position. Or how many of you want to return it, the left to parse text. So maybe we wanted to find where the opposition is. And maybe we will just wanted to grab text up to r. And then we could use the result of the INFFER, parse it by using left. And then you can probably guess that there are other ones. So right is just like it sounds. It'll grab the right two values in this case, or however many you want, report, etc. So this should be R and I. And then we can also use nib. And mid will have one extra argument. So this will be the starting position. So we'll start at one. And at how much or how many characters do you want to return? If you don't return anything here, it'll grab the entire text onwards from that starting position. So in this case, if we're starting at the first position and we're grabbing the first letter should be wide. For grabbing the second position, this should be u. And if we want to grab two gliders, and it's UR, and then like I said, if you exclude this altogether, we're starting at the second position, which is you wish to return. You are just the rest of the string. So that's what's happening here. Those are the five really useful built-in VBA functions that we can use. You don't need to write a self, so remember it's left, right and mid LEN OR length. And then IN STR, so in string. 11. First Programming Exercise - Highlighting Cells: So now that we've gone through some of the basics of VBA, let's actually try to create a script together, have the code actually do something. So in this case, what we're going to do is highlight cells throughout a row. And so our goal here is we're going to want to loop through all the values in column a. And if they're equal to Steve that we want to highlight. So let's just make a couple of Steve's and marries just so we can see what's going on here. So the first thing we wanna do is what kind of highlight to you? So let's first use a fill color. We're going to highlight this. And the reason we're going to do this is we want to grab the interior color and I just don't know what that number is off the top my head. And so this is a good way for you to test yourself if you don't know necessarily what the value is or formatting type is, you can, you can get returned using the developer. So let's go back to our code. And so let's first just comment this out. We could just leave it. Let's just comment it out for now. And what we wanna do is we want to return the interior color up and sell. So do I do debug dot print, rein, aid or interior and dot coupler. So when we run this, this is the interior color to highlight that cell. So, okay, perfect. Now let's go back. We're going to declare our variable. We're going to start at row one. And before we start looping or anything, we probably want to get rid of any highlights in this row as we're updating the highlights, we probably want to start with a blank slate every single time. So what we're gonna do first is we're going to say range ate the entire column, a interior color equals 0. And so we run this. Actually, I believe it's XL, none. And so x is just built into VBA exile nine is just a built-in VBA value for just nothing essentially or a null value. And so by having a null value, it'll make these have 0 highlights are no background fill. Okay, so perfect. So we're restarting with a blank slate every time our number is one. And then we want to use a for loop. For I will use a do while loop, while else. Number, which is our row index column, with the use of a or a number one if you want. And then loop. As we've said, we'll need to increment our number every time the loop. So we'll say number equals number plus 1. So every time we loop will add onto our number value. And then we wanna do an if statement to check is the value that we're on equal to c. So we'll say if cells number, what number we're on, what number we're on. For the cell, it will equal to Steve. And if it's equal to Steve, So we'll say Steve, fun. And we'll end our if statement. And if. And if it is, then we'll say cells. Remember a bad anterior pillar, which is equal to this number that we grabbed. And before we start this, we just need to double-check that we're actually doing an operation. So instead of do-while cells that number a, we actually just need to compare that it's not equal to a blank values are not equal double quotes here. And so when we run this, we just need to double-check that our code actually do what we anticipated. So we can see Steve, Steve, and Steve are all highlighted. And so if we run again, same thing will happen. So what have we act carry and what if we add feed but without formatting, so we just type it in. We go back to our code, will run and we'll see Steve had updated. Now we just want to double-check. We are in fact starting with a blank slates. Let's update this to be scary. So it's no longer Steve. And so that's what should be happening is that this should become no fill and then the rest should become filled values. And so we can see that's what's happening here. So again, this code that we're testing is we're going to start with a blank slate. We're going to get rid of all the bill for all the cells, are then going to loop through all the cell values. We're going to double-check that they have some value in the cells. And if they don't, then that's where our loop will end. And then we're going to use an if statement to check if the value in that cell is equal to Steve. It is equal to Steve. We're going to highlight that stuff. And then we're going to increment our loop so that way we don't have an infinite loop. And then once the cell value is a blank string, then we'll just add the script. 12. Worksheet Codenames: So in this lesson we're going to talk about worksheet code names. So in the last exercise where we were highlighting cells using the code that we've made. The problem is that we're interacting with cells and we can also use ranges. But the problem is we're not telling the code which worksheet we've actually referring to you and specifically which workbook for the problem is if you don't do that, the code will automatically interact with the active workbook, or essentially the workbook you're currently working on or selected. So the problem that we have is imagine we have two workbooks. We have one, which is our example, highlights cells. One is just some made up workbook. But I'm selected on to this second workbook that I don't intend to actually update. But if we go back to our code and run, because I'm selected on to that workbook. You'll see that the values of Steve here were updated and highlighted. So that could be a problem. And then if I select back onto this one, the original workbook, you can see that the values in fact highlighted back here. So I wanted to talk a little bit about using worksheet code names. In a prior lesson, I did talk about how do you reference worksheets. And I prefer we just use code names entirely. Because the issue is we're using worksheets is you can see that both worksheets here are nice summary, summary in summary. And I'll even minimize this down just so you can see that as well. Summary in summary. So if we had used worksheets as well, these have the both the same worksheet name. And so again, we would have ran for the same problem. Worksheets wouldn't have prevented our problem at all is the code still would have ran on to that second workbook that we didn't attend to work with. So what we're gonna do is we're going to select this worksheet. It'll default to Sheet 1 and then she 2 and 3 and so on, the worksheets and the workbook. And let's actually rename this to something that's useful. So let's make it this summary sheet. And then for our second workbook here that's open, let's rename this to other people. So that way we have a clear distinction of what are the coding. The code name here is summary worksheet. And so what we can do is we can start our lead summary sheet like this here. And then we use period and then range and so on. So we'll use something like this. So those cells, so anytime there's a cells are arranged, reference will add summary sheet for the primes. And the great thing about summary sheet, and the code name specifically is the code name is inherently inside of the workbook you're referring to. And so you don't have to worry about Summary shipping reference to another workbook. So even if other sheet was named summary sheet, the code we know you're referring to the current workflow at the module is m. That makes sense. So the modules in this workbook. So we're going to only interact with the code names in this work for. So we can see that that's what's going to happen. If I move my code. Let's restart this like this. We can make the backfill just empty. Eurystheus on so forth. And let's make this married just so we can see that the code is actually executing that we intended to. So we'll go back to our code and let's select the second workbook. We're going to maximize this. So again, we're currently selecting that second workbook. But if we run code, will see Mary's highlight got rid of and I'm Steve is highlighted. And our second workbook, nothing got highlighted. So you can see that the code ran as intended. It only ran based on the current workbook and the worksheets in this workbook rather than any external workbook. That was our goal. And that's what the code does. 13. InputBox: In this video, we're going to talk about how to use input box. And the input box will allow you to have your users enter information into an input window, and then your code can use that. So this is really handy to use if you need someone to enter information like a login or like their email or password or something like that, or really any other inputs that you need. So we have our module here, and we have our sub-test case. And the way that you use input boxes is let's create a variable DEM result as string. So this will be the result from our input box. And using input boxes is pretty easy. So we'll say the result is equal to an input box. We need to use parentheses and we'll see the arguments are prompt, title. And I wouldn't worry too much about this. This is just like the exposition, so where the pop-up box will show. In most cases, you'll just want it to be in the center of the screen. It's phosphate. Enter your name here and the title will just put his name and everything else will leave empty. And then let's just message box that result. And so let's enter my name, Uri. And then we can see that our code took in the word URI. And let's test what happens if someone presses cancel. And we see the result is an empty string. So using the lessons before, if someone puts an empty string or presses cancel, you could wrap this with an if statement, something like this. So if results is equal to an empty value, then let's do something and then Alice will do something else. So if it's an empty value of message box, please enter a value. And otherwise let's message box the result. So it might look something like this. So if someone presses the x or cancel it will do the same thing. There'll be an empty value and we'll say Please enter a value. And then otherwise, it might look something like this where your result. And so you could use this in your scripts. If, if someone presses, cancel or exits out, maybe you don't do anything here, and then otherwise you run your code. Also. You can make the code all that more, more dynamic and maybe test for like an at symbol if you're looking for an email or something, and then if there's not an at symbol, then it's probably not a valid email address. But I'll leave that up to you to try those things out on your own. 14. With Statement: In the last lesson, we talked about how to use a worksheet code names. But as you can see here, we use summary sheet 1, 2, 3, and 4 times. And so you can imagine if you have a very large script, you don't necessarily want to have to eat rewriting summary sheet every single time. So in this lesson we're going to talk about how to use width statements so that way you can save typing texts throughout your code. And if you're interacting with the same thing multiple times, this can actually speed up your code quite a bit. That every time the code runs, it will first look for summary sheet and then look for the range, interior color, and so on, so forth. But if you use a with statement, you're telling the code, hey, code used summary sheet. And anytime I tell you later on in the code that you're interacting with it, you only have to look it up once. So hopefully that makes sense. So let me show you what I mean. So instead of having to type summary sheets so many times, well, we can do this leaves that width statement and we're telling the code what are we interacting with? And what I like to do is I like to indent anytime we use these sort of loops or with statements. So that way, you know, all of the code underneath it is what your where your past statement is or where you're interacting, interacting with underneath it. And then we use the end, we have Saman. And so you've seen a lot of times in VBA, anytime we are starting a statements are a declaration. We always have an M for most of the time. So in this case, we're saying we're working with the summary sheet. And all we have to do is now get rid of this. And the key here is we will always have a period. So if you get rid of the period, you are no longer interacting with the with statement. You're just interacting with the range by itself. And you don't have to interact with the width statements or whatever you're interacting with. So again here a number can still be just a scientist one, so you don't need to worry about that. But anytime you have a period, then you are interacting with that with statement. So this period is indicating that we're interacting with summary sheet. Again, I get rid of the period. Now you're not interacting with somebody. You're just interacting with range a column a in any active workbook or worksheet failure. And so we can just get rid of all the summary sheets here. And you can see our co got quite a bit smaller. Although there's only four instances of summary sheet. This will speed up our code a little bit. It won't be noticeable to the human eye, but it is still make our code faster, especially if you have a really big scripts, can really help to use the summary sheet or a with statement for the summary sheet. So that way you don't have to type in it so many times. And it'll help speed up your code. 15. Object Variables - Early And Late Binding: In this lesson, I want to talk a little bit about early finding and late binding of objects. This lesson probably won't make a lot of sense until we start going through some of the bigger coding exercises. How to interact with say Internet Explorer or Chrome or even outlook. But I still want to show this now so you're familiar with what this looks like. And as you get better at coding, this will make more sense as you look at other people's code. So late binding and early binding just means that you're working with an object. It's an object variable that is, so we'll use dim. And in this case, let's work with Internet Explorer. We're not gonna do a lot here, but I just want to show you what I mean. But you would create an object. Late binding means your first declaring your object variable here. And then if you have an object, you have to use the keyword sets. So you're setting what is that object equal to. So we're going to set object, Internet Explorer object. And we're going to create objects. So use this keyword create object. And then you have the text here. A lot of times you can Google whatever object you're trying to interact with. And many of them are very common, like Internet Explorer about application. You just copy and paste it. And now you can start interacting with Internet Explorer. And again, if you use the keyword set and create objects, so this is late binding. So the key with late binding is you don't have access to what are the methods and properties of that object. So you have to know what they are. And what I mean by that is if you want to navigate the Internet Explorer, for instance, something like this and explore that navigates which will go to some URL. If you google.com, for instance, you can see that navigate never showed us what are the properties or maybe there's other properties and maybe we can check what URL are we currently on for other methods. So we don't have access to that. So you would have to know that beforehand or Google ads or something else. Now we can use pearly binding, but the reason I don't like using early binding is because you have to create a reference. And so what I mean by that is we go to Tools here and then we see references. So specifically for Internet Explorer, we have this pop-up shown here and we can see there's a bunch of libraries interact with. Pretty much all of them are uncheck. These are just defaulted by the VA so you don't need to worry about them. But the problem and the reason I don't like using early binding, as you can see 16 here. This specific library won't cause an issue because it's just whatever office library you're currently on. If you're trying to reference a library, maybe you have an older version or a newer version of that library. Then the user you're trying to send the Excel file Two. If they don't have the same version, it can cause errors. However, if you use late binding, late binding will just find whatever version of that library is that you have on your computer and then interact with that library. So we can try to find the Microsoft library for Internet Explorer. And that library I believe is Microsoft's internet controls. And you can see this one does not have a version number. But for instance, graph does if you are interacting with graphs for some reason. So we can hit, Okay. And the difference with early binding is you're going to declare that variable as that object. But what I mean by that is what leads to pass. We use the keyword new, because every time you run the code, you want to make sure we're creating a new object and we're not using an old object prior run. It's just a good sanity thing to have. And then we'll go to Internet Explorer here. And the difference here is if we comment out these two lines of code, you can see that we have access to the properties and methods of this object now, so we can see what we can interact with so that there's an address far. We can interact with HTML document, that's what this is here. For instance, we can make Internet Explorer go to full screen. We can even navigates, which is, sorry, down here. So if we want to navigate to a specific URL, that's how we use that method there. And so this is nice to use if you're testing. But again, because of version control problems, I would suggest that you don't use early binding, and I would only use early binding in the cases of where you're trying to create a code and maybe you don't know what the methods are, where the property names are. After you're done coding it, I would suggest getting rid of that reference and changing this back. Objects here like this. And then using late binding. Here, it create the object later on. The other nice thing about late binding is you can set your object anywhere in your code. So maybe you have a 100 lines of code here before you actually even need to create this Internet Explorer. Whereas with early binding, that memory is allocated upfront and maybe it will make your code a little bit more sluggish. That's also an event. So I just want to point these out. They probably will make a lot of sense right now. But as we get later on into the coding exercises, this will become very apparent that what I mean. 16. Functions: In this lesson, we're going to talk about how to create functions. So the difference between a function and a subroutine is a subroutine does not return a value, whereas a function, you can compare values, you can parse text, things like that, and then the function can return a value for you. The other benefit of having a function is if you have a repeating process in your code and you can write a function so that way you don't need to keep copying and pasting the text over and over and making your script really long. You can just call the function to run that one process for you. Maybe return a value and then you can use that value in your code. So you just have one line of code rather than copying and pasting repeating. Also, you can use functions and just create your own dynamic functions that you can use Excel. So let's start the way you create a function. If by some function and similar to south, you can make it any, any name that you want. So test, function, that sense. And again, it'll create these parentheses or us. We'll talk a little bit about this in a second. So for test case, if we want to run this function, you can use the word hall, which will then call this function. And then the script inside of this function will run. Also, if you have a variable, you can first create a variable. Let's say, let's say number ten, number as integer. And you can then assign the value of this number based on the output of this function using this. So these equals and then test function. And maybe we'll say has to function is. So let's check this debug that prints the number. For a function to return a value, use the same function name, and then you assign a value to that same exact name. So you see here test function and a test function is equal to two. So this function will return the value of two. And so that's what we should see, a read hit View and media window to open up our media window. So the value here is to repeat this three. Here. Then we have a great We can also use the word col if we just want to call the function and we don't want to return a value to any variable. In this case, they won't do anything. But maybe let's do a Message Box. Message box At three, for instance, and we won't return anything to our media window. So in this case, we return the value R0 here. So you can use the word call to make this function run. Maybe there's a process in year, you just don't want to return a value. And then also you can create a function and then use it inside it, help out. So maybe we'll say has function is equal to three. And then we can test that here. So let me zoom in a little bit. And we can use equals test function. And this should return exactly. We see here. Here we made up our own custom function or a ourselves. And now it's doing some process for us. This is very rudimentary. You probably wouldn't want to use this for anything, but let's actually make this something useful. So we're going to create a function where we're going to parse a string and look just for the numerical values. So you can think of a string like the price is a 100. And maybe we just want the numerical value 100 from the string. So how do we do that? So let's create a string here. Maybe skewing as string. And we'll say made up string is equal to five, is 100. And we want to grab the numerical value from this string. Now one thing we need to talk about before we can proceed is functions can take in arguments. So you can pass values into the function. And then the function can use those values to logical comparisons. Logical comparisons are taxed again or just other, other scripting processes. So you can make this any value one. So we'll say S. And that's just the, the made-up variable name that we're using as a string. And then you can use throughout your code. So let's try that stroke when Message Box S, and then we'll say Hess function. And we're going to pass one argument, which is our made-up string. And so we can see we pass the price is a 100, two dysfunction using this S. And now we can call that S here in this function. And maybe we'll make this FTIR shorthand for string. Maybe that's a little bit easier for us to understand. So STR, so the same thing should happen. Okay, great. So what if we wanted to just get the numerical value? But let's make up a number as an integer. And one thing we want to add at the end of our function is we're going to use the word as. So even our function can return a certain type of variable. So we want our function to return as an integer value. And we will first make number the test function. We're going to pass up our made-up string, which is here again, STR as string. And now we can interact with that STR. So inside of this function, Let's use dim SCR LEN. So we're going to first find the length of the string and then we're going to look through it. So STR, LEN is going to equal to the length of this thing. But we can use message pocket STR LEN. And you can see that the length is 16. And so now we wanna do is we want to loop through every, every string in the text and then check to see if that value is numeric. And if the value is numeric, then append it to our result. So we're going to make it thin. Results as string. We're going to start results as an empty string. So essentially just nothing. And then we want to use a leaper. So C string blue book has integer. And so we'll use for string uber equal to one string length. We essentially want to go through every string within that string. So one letter out. And we can do that by using the MID function. So for me if debug dot, just so I can show you what I need to use MIT STR is the stream we're passing into the function. We're going to start on string Luther, and we're going to just return one character at a time. So let's try this. So we can see th e, e our ICE and so on and so forth. And then we returned 100 at the end as well because that's part of the string. So now we know we're going one by one. So what we can do is we can use an if statement. If. This is also a built-in eBay function is numeric. So we're going to check if this expression is numerically. It is. And we'll use a band, and then we'll use endif. But the values and map will say results. And then we'll append to our results, that character. At the very end. We're going to return our test function here based on the result that we pulled from the string, if that makes sense. So let's try that. What is the result of a number? Let's message box number at the end here. And we would expect this to be 100. And so that's exactly what happened. And now what if we made this a 1000? And then what if we didn't just pick dollars at the end to show, we can show that we are grabbing the text inside the middle of the string. And again 1000. And then we can even use that same function inside of our Excel file here. So you can say this, ice is not a thousand dollars. We can say test function. We pass one argument into our function because that's how we defined it and we can use a string. In this case, we use a cell reference to say this is the string that we're referring to. And that we can see that it returned a 1000. And we can weigh costs $200. And we will just copy this down. And then we returned to a 100 here. So you can use functions at part of your subroutines and then call the functions. You can assign values based on the result of that function. And you can also use functions inside of your own worksheets. If you went create your own custom user-defined functions. 17. Index + Match: Even index match, in my opinion, is a lot easier than using. Vlookups are H look-ups and many business professionals are probably used to VLookups and H look-ups. And you might be used to index matches, but I still want to walk through it because it's a good excel efficiency tip and will be the basis for a lot of the template accretions that we will work with. So this is just some made up data that I have here. We're using sort of a unique ID. I just called it a loop or value. Well, you can think of this as maybe an employee ID for instance. These are just some made up names. Birthdays, salaries, bonus care, and health care coverage costs. The way you use an index match versus a VLookup is you'll use index. You'll see it'll take an array or an a reference. Just think of this as a range of cells or a row or a column. And then you'd use a row number, which is where we'll use our match. And you can also use a colony. You can also use the match here, but let me show you what I mean. So for instance, if we were using an array here, the array that we're going to use is what we want to return the value. So for instance, what if we wanted to return to salary? And maybe we wanted to use a lookup value of speed. So we can use match, which takes an a lookup value. Our lookup array, the range of cells we're looking up again, and then the match type. So in this case, what if we wanted to look at the speed? So we want to look up Steve in this range of cells here. And we want to use an exact match, which is 0. You can also use one and negative one for less than and greater than. But in most cases you won't use these. And we don't have to use a column number because we're only using one column itself. So we will just close our function. And we can see that the value returned to a 120 thousand because that's these value. Now what if we wanted to look for Jim just to show that this is actually working. So Jim, and it should return on Africa. So perfect. Now what we'll use this for is if we can use unique identifiers, we can make this a lot more dynamic. So let me show you what I will go to the summary sheet. And I just created this looper values here. And we're going to pull in salary, bonus, health care coverage, and health care coverage costs. So first let's Kevin, the individual's name. So we'll use an index. We want to grab column B, that's what we want to return. We're going to use our match function. We're going to go back to summary. We're going to look up the value in range I read for I2, sorry. And I'm just going to get rid of this just to make it easier to see what's going on. So we see this is i2 and I make it an absolute reference by clicking or on my keyboard. And we use a comma, the lookup array is going to be our loop or value here. So column a, I use an absolute reference here, and I'll use an absolute reference here as well. And we want to do an exact match, so Hama 0, and then we're going to close off. So we can see this is returning Dillon or looper value of four. Because what's happening here? Now if we change this to one, this becomes Steve to film. You can probably guess what I'm doing here. We can loop this value. And it's going to update the salary bonus health care coverage for this person. So we can start to create pretty dynamic templates this way. But in this case it's just an Excel efficiency tip for now. So let's just copy this. So salary is in column D. So just kinda get rid of those absolute reference. Then update this typology. Salary bonus, I believe health care coverage is right next to it. So we can just copy this over. And because these aren't absolute references, I can just copy it over. And you'll see this app is updating and a G, but these are absolute references, so these won't update as I move over the formula. And so you can see as I update this to 120 thousand, all the information goes from feed of information here is for pile. All the information here is for Sam. And the reason you'd want to use index match versus VLookups. A VLookup will take in a bunch of cells, possibly, you know, 2030, 40 columns. And then you're selecting which column do you want to return. And because of that, formula has to hold in all of those ranges, cells or columns or rows in the formula. So uses a lot of memory. And if you have a lot of VLookups and you've probably seen before the Excel file, it can become really slow and sluggish indexes as you've seen. We can call just in one row that we want to return and matches inherently quite a bit faster of finding the value that we want to look for. And rather than just telling VLookup in as a comparison, what to return, which column to return or which row for HLookup. And if you look online, you'll see there's a lot of performance comparisons where index matches inherently fascinating VLookup as well. It's not apples to apples basis. So I'd suggest you use this in your code. Once you get the hang of it, it's pretty easy to use and it's quite a bit faster and will make your worksheets a lot faster as well. 18. Re group Rows Using Numbering & Filtering: This is a trick I've learned to use to be able to group rows of data together. In this example, we have some account numbers here, descriptions of transactions. So we can think of these as expenses. And we have a mountain, and we can think of this as debits and credits. So we have a positive entry and a negative entry and the offset to 0. And you can think of the 1040 is maybe is it a cash account? And the six thousands and thousands here, maybe it's expensive. And so what we wanna do is we want to group these together so that way we can see that it's offsetting entries or think of them as debits and credits in accounting. So the way that I've done that is huge, an empty column of data. And I start with the number 1, and I take that value and I do plus one. And we just cut to the end before we go to our second group of transactions that we want to combine with, if that makes sense. So we're going to copy these because this is our first group of transactions, and then this is our second group of transactions. So what I did is I copied, right-click and then you can hit V or you can just click this, and then I copy this down. So what we do now is we just select our data less this, these numbers Here. We go over to Home tab. If you're not already there, go to sort and filter. We hit Custom sort. Our data does not have headers. So let's make sure we unclick this. In this case, we've got a column D because that's where our transactions are. Numerical values are, I mean, and you can do there largest to smallest or smallest to largest most scenarios we'll combine the row groupings for you. And so we do that. I'll click okay. And so we can see all the ones are combined, all the topics are combined and all the threes are combined. And so we can see the transactions are also combined. So it's more aesthetically pleasing. And we can see that there's upsetting debits and credits are oscillating transactions. 19. Select and Delete Blank Value Rows: In this video, I'm going to show you how you can remove values from a cell. So this is just some made up data. This is some names. This is some birthdays, salaries, and health care coverage. So you could think of a scenario where you're trying to complete an analysis where you want to get rid of everyone who does not have health care coverage. For the easy way of doing that first is you select a column of where you wanted to find the blanks. This is column D In this case, make sure you're on the home tab here. We go to Find and Select, go to special. We select it, make sure you don't click anywhere else, is you want to keep this selection. We right-click on any of the selected values. We're gonna go to Delete. We'll go get this popup, but make sure you don't use this. The first defaulted value. We want to delete the entire row. You wanted to, you could delete the column as well. If you had your selecting data in a row and you could delete the columns. And so now those rows have been deleted and we just have to the rows where health care coverage. So that's an easy example, but let's go to a slightly harder example. What if we have an example of where we wanted to lead everyone whose salary is, let's say under item on Earth that a 100 thousand for instance. So we could first kind of create our own blanks by using an if statement. So we could stay. This is less than a 100 thousand. Then we'll just return any value. I'll put y here, sort of representing that, yes. And otherwise, we will return a blank. And actually you will want to do the opposite. So if someone is under a 1000 will return a blank. If there are over 1000, move return value. And so it will copy this down. We'll copy our value using control C will right-click and you can either hit V or copy paste values. So I'll hit V. We're going to use it made up header here. Just so that way this is not also blink value, so we'll just call this Test in one last key thing is select the column, go to data, and then Text to Columns. Because the formula is still returns some null value into these two cells even though they're blank. And so Excel won't recognize these as blank values unless you select this text to columns. And then just hit Finish. You don't need to do anything special. Now that we have this selected column out, go back to our Home tab, find and select. Go to special, go to blanks. Right-click, delete the entire row. And now we only have the individuals whose salary is at least 100 thousand. So that's how you delete links. And you can make it this pretty dynamic. 20. Wildcard Character in Formulas: This lesson will show you how to use the wildcard character in formulas. And so this will allow you to kinda make your formulas little more dynamic. And a lot people don't know that wildcard character can be used. So we have some example data here. We just have some names, salary bonuses and things like that. And so what if we wanted an example of where we could grab information where anyone who has the name D. So we have steve S, t bar and they're steve S. Maybe we wanted to grab all the salaries for anyone who starts with. So usually most people would use a sum if and the range that we want to look for, the text that we want to compare against is in column a. And a lot of times people would just use the criteria, maybe steve S. And they would use their summer range is the salary range here. And then maybe they would just copy this and just that. Right, Steve are, and then just add it to. This is inherently, I'm not very efficient. If you have a bunch of different names and last names, this could get pretty burdensome. So the way that you could do this, and before I move on, I just want to show you that sum total is 395 thousand. So that's what we want to find at the end. And just so we can make this more static, Let's just do this. We'll use a wildcard character is by using this here, you can detect the star symbol. And the wildcard character means any value, any text, any length value after this will also be a match. So first we need to find Steve with a space, and then any value of any length will be a match. And so we can see here is 395, which is what we were looking for. Now, wildcard characters don't have to be at the end. They can also be in the middle like this. But I would just be careful because again, it's st than any length of value u0, v0 space, and then any length of values. So just be careful about that. So again, still 395 and you can even have it. And so what I mean here is let's say we add 123 here. This will still return because we're saying any value in between st and then EVE will still be a match. So this is how you use wildcard character. You can use them in a lot of different formulas. And I just wanted to show that to you because a lot of people don't know that they exist. 21. SUMPRODUCT: In this lesson, we're going to walk through some product formulas. Once you get the hang of them, they're really useful because it's a very dynamic formula that lead Taft that many other formulas lot of times can not, or at least can't complete easily. The other great thing about some product formulas is they inherently take an array of data. So you can think of cells or ranges of cells. And so you don't need to create an array formula. So if you have a newer Excel user and they're not familiar with a rate formulas, then they might be confused at what an array formula is and it might mess up formulas. And so as a reminder, the way you create array formulas is that you select into your formula via Control Shift Enter. And it'll create these squiggly brackets for you right here. And so a lot of times people don't know what those are, how to create them. And so they can sometimes mess up formulas and using some products can avoid that altogether. So let's talk about some products. What we want to find here is we want to find Steve are, and we just want to find the values say, above year 2000 to and below year 2010, which isn't always super easy to do with other formulas. And the way we use some product is B. First, we'll create some product. And you'll see it takes in a bunch of arrays which you can just think of references itself. So the first thing we're gonna do is we're going to select the data that we wanted to compare against bursts. So we want all of these, these values here. You can pick at that and maybe it's sales data or something like that. And then once we start doing our comparisons, or equals to or not equals two kinds of things. We'll use parentheses or sorry. Yeah, parentheses. And we will look for are the names here. So first we'll say here, these values here we're going to look for Steve are. And then we're going to close. And we're gonna use a multiplication. And I wouldn't worry too much about this. So I'll explain this in a second. We'll just remember use multiplication and we'll use another parenthesis here. And then we'll look for our date values. So that'll be here in the header. Now the key with the values is we will need to convert what we're comparing against to a d value. So we'll first say greater than equals to. And you can't just go 12, 31, 2018, for instance. Because the formula is not going to recognize that you're referring this to a date. It will think that you're referring to That's just as a string of text. So you need to convert it to a date value using this formula. Now the formula knows that you're referring to, that you want to compare against the peak value. So I think I had one when parentheses here at the end that I needed to add. And the value is 1.19 million. And so we can check here. So we're doing greater than or equal to 2002. And we can see that's the sum total here. Now what we want to do is also compare if it's within a range updates. So we also want to compare it less than. So simply what I can do is just maybe copy. This is probably the easiest approach. And add to the end. And what we're now we're gonna do is we're gonna do a less than, and let's say 2000, which is 705 thousand. So we can compare Steve r's values here. We can see it isn't backed some 105 thousand. Now if you think about some products, the way that this works is what the formula is doing is it's going through this range of cells here to find C bar. And every time it looks, every time it finds a match, it will say this is one because it's true. But this is Sam f, which is not Steve are, so this'll be 000, 000, 000. Now it's going to start multiplying those ones and zeros across this, this raw data here. So it's going to look at this value and it's not going to be greater than or equal to 2000 two. So this again will be 0, 0, 1, 1, 1, 1, so on, so forth. And so all those ones and zeros are going to multiply across. So this one times one times this value is going to be itself. It sends Sam app is 0, 0 times 1 times this value is going to be 0. And so that's what it's doing is it's summing up those quotations. So this will multiply by one, this by one, this by 1, up until 2010, and everything else, we'll multiply by 0. And so that's how this one, this is a really useful formula to use and I would recommend you try it out when you get some free time. 22. Dynamic Template With Index + Match: In this lesson, I'm going to show you the template that we're going to start using or our compensation statement. We're going to eventually automate this so that week we can loop through it. We're gonna save them as PDFs. Later on you're going to see that these rows are going to hide for us. But before we get into that, I just want to show you how you even created this. This is using index matches. And so if you skip the excel efficiency section, then I'm just gonna go over this pretty quickly. So we have a data sheet kinda looks like this. And I'm sure a lot of you have seen data just like this. That's just a big data sheet. What we wanna do is kinda of reformatted so it looks more appealing. And so that way we can do that is we use index. You're not familiar with it. Index is a quicker way and probably a better way to do B lookups and H look-ups. So the way we use index is we have a reference which can be a row or a column, or just a range of cells. So for instance, if we wanted to grab the names and grab names, this is will be our output. And we'll use match function. And the way we use match is we'll use the lookup value. The lookup array that we want to compare two is here, these 1234. And the last argument is whether or not you want to use a less than, equal to or greater than. So we'll use great exact match, sorry, which is 0. And then we'll close our function. So we see here our lookup value is k2, which is the number 2 here. And that's the number 2 here. And we want to return the name there. We wonder return salaries, that's column E. So we can just return column E here. And the reason we're using mash is we want to find a row number. You could also find a column number. And so our match function, instead of hard-coding the row number, can be more dynamic. And so the match function that will find us the match first return the value, and then that will return us the row number. So that's the reason we use index match versus VLookup. But VLookup usually you will use a large dataset, a bunch of rows or columns of data. And because of that, VLookups can take up a lot of memory. And I'm sure you've dealt with worksheets or books that are pretty slow and sluggish because they have a bunch of lookups. And maybe they're looking at 40 columns of data, where in reality they just need to return one column a data. So the nice thing about index matches is you can specifically specify that you just want this one specific column and then look at this other specific column, rather than pushing 40 columns of data into your formula. Also, if you do any Google and searches for index matches, index matches are inherently a little bit faster. And B lookups as well just because the match function is so quick. So that's index matches. If you skip through the Excel efficiency. 23. Dynamic Template With Automated Filtering: In this lesson, we're going to talk about how to automate filtering and our Excel file. So that way we can start creating really dynamic Excel templates. So to start, we'll go to our data sheet. This is just some made up data. Some names, start dates, salary, health insurance, sorts of things like that. And we use this thing called a looper value, but you can just think of it as a unique ID. So this could be an employee ID in your dataset. And our summary sheet is just a basic compensation template. We're using index and match to pull in the information. So we're pulling in column B from the index lookup value and K2. And we're looking at column a and returning the value in column B. So something like column B is being returned and we're looking at column a. So if you didn't look up the Excel efficiency tips, there's an index match video there if you haven't watched it, I'll just briefly go through index match. Index takes a reference and then we'll return, or will it for our row number and also a column number. In this case, we're not going to look for a column number because we're only pulling in one column of data. So index, we want to look for column B here. We're gonna use our match function. Our lookup value is going to be cell K2. And since we're not showing the sheet name, we're still back in the summary sheets. So K2 is referring to the summary sheet. And we're going to look up, we're going to compare versus column a here. And we're going to use 0 for an exact amount. So you can see here this returns date. And as we loop through, the loop value will return. Steve are since we changed the loop value of one and that's a two and so on and so forth. Now in this template that I have here is just a made-up template. I use if you do heights, specifically, if someone doesn't have a bonus, I'm just comparing this value is equal to 0. Let's use this string here of hide and then do not hide otherwise. And then the same thing with health insurance here. And then profit sharing is just going to collapse the entire section. So we don't want, we don't even want to show the header in case someone doesn't have profit sharing for instance. And so that's just all this is doing. So having these if statements is not going to start collapsing our filters automatically for us, we'll need to add some code into our workbook here. So the way that we do that is we'll go to our R code. I've created a filtering module. Let's ignore this looping. Employees will look at that in the next section. So filtering. One thing I always like to do is I use a with statement and we use with patients. And I want to get rid of screen updating so that we'll speed up our code and also enable events, not enable animations. And we'll just turn these to false. This isn't required, but if you have a script that's running a lot, these will speed up your code significantly because anytime the screen updates, if there's any value is being added to the worksheet, the screen won't update for those values at only at the very end. And so you can imagine if the screen is constantly updating, it can get pretty slow, pretty fast. And at the end of the scripts I like to add the same thing, but instead we're going to turn that back on. Newer versions of Excel will automatically turn these back on for you. So you don't really need this section of code, but it's just for sanity. Really. Simple thing we're gonna do here is we want to force some filtering to occur. So we're going to use the with statement and we're gonna use summary sheet. And I haven't mentioned but the code names for the data sheet. Here's the datasheet is datasheet and summary sheet is the summary sheet. Organize it with statement. And we're going to use n with. And what we want to do is we want to look for column H. So specifically something like this. And we want to apply a filter. And the way that we do that is this way. We use Auto Filter. And then there's the criteria, our first criteria. I'm going to ignore field here. It's not very important, but I just want to show you the things that we use once you care about. I'm going to add an underscore here. And that way our code, we'll just sort of texts rap for us in a sense so we can see it. The first criteria we want to filter for is do not hide meeting that. We won't filter out that tax. Also, we're using an operator here, we're using an OR operator. And our second criteria is equal and there's no value. So it implies also not going to filter out any length value. So then we can say the opposite is happening. So anything that is not do not hide and not blank will be hidden. And so that is why we were creating these do not high, high and all these blank values will hide anyway. So in reality, we don't necessarily need to use Hive. We can use any value, but it's just a little bit more obvious what is going on in our code. So if we go back to our script, so first we want to create the filter, but then we also need to force it to apply. So the way you do that, if you do auto filter and then you do dot py, apply filters, sorry. And so this will create our filter for us. Again, this is in column H, will run this. And we'll see now there's a filter and it's not going to feel trapped blanks or do not hide, but anything else that is hide it will start to filter out. So great. We have our filter but we needed to run every time we update. Here. We can see here we have a high value, but it's not being hidden. We want our code to do this automatically, for us, not for us to do it ourselves. So the way that we can do that is we're going to go into our summary sheet, double-click here. And the thing we wanna do is select this drop-down where it says general will go to worksheet. And you can see that there's a bunch of stuff we could select over it. Now, the one that we want to look for is the change event. So essentially anytime there's a change, let me delete this so it's not interfering. In China, there's a change in the specific worksheet. This will be triggered in any code you write in here will trigger as well. And all of this text here is defaulted whenever you select any of these, as you can see here, something like that. And so we're not going to mess with this. I mean, you could think of the target as the range. So this is where any updates in the sheet are occurring. And so if you needed to find out where the changes are occurring, you could do that. But in our case, we don't really care about that right now. Really, all we wanna do is we want to call our module. So we want to call it filtering module. And we want to call our filtering separate team. So we can see that you're filtering here. And so now anytime there's a change in the worksheet are filtering module will automatically get triggered. And so we can see that now. So we can see here, we saw hide there for 2.5th and then everything collapsed or us, without us having to do anything. And you can see as I loop through these values, the rows are dynamically being hidden for us and unhidden without us having to do anything. So we can make really dynamic templates this way. And so it doesn't have to be a compensation template. It could be an investor statement, could be a legal notice, be an invoice if you want. So anything like. So before we end this lesson, I just want to add the same line of code one more time. And the code we're gonna do that is around double-click this workbook. And we're gonna do the same thing. We're going to select this general instead of worksheet, we're going to find workbook. And this is exactly the one we want to look for. We want to look for the workbook open events. And so that just means anytime the work book opens, we also want to run our filtering module. So just in case someone opens the file, we want to make sure that our filtering has occurred. So that way, if there was an era before or something like that, the user wouldn't question why are certain rows uncollapsed? And so when the workbook open, the code will run, everything will collapse and everything will look at. 24. Loop Through The Dynamic Template: So we learned in the last lesson how to do automated filtering and our Excel files. And so we have our compensation sheet. As we loop through this value, we can see that the rows are hiding and on hiding for us without us having to do anything. And that's great, but we don't want to have to manually keep updating this value ourselves. So let's learn. But let's use what we learned in the basic section to use a loop, to loop through that value. And then we can see the values updating or us. So we're going to go to our code and we're going to first use the with statement. We've application. And what's slightly different about the width application that we're going to do. Mostly the last one, the last one we turned off screen updating. And we also turned off enable events. But in this case we want to keep enable events. So we're going to purposely set a to be true, even though by default it's true so we don't need to change it, but let's just be safe. And the reason is, is the events that are occurring are the events in the background at this code, this worksheet, changing that, this workbook open it, that we don't want to enter those from occurring. Because if we enter them from occurring, then the filtering is not want to occur. So we're going to end our code with application. And we're going to just turn the screen updating back on. So again, you don't really need this, but it's just good for sanity. So now we want to actually create our code to loop through all of the values. So let's create a rollover. So this can just be an integer. And we're going to have world loop or equal to. And the reason it's going to be too, is if we go to our data sheet or data sheet starts, I wrote two. So we're going to use a do while loop. We're going to loop through all of these values until we hit a blank value. And then we'll have our code stop. But every time we go through each cell, we're going to update the value in this cell into cell K2 here. And then the value in our sheet will update automatically for us and the collapsing will occur for us as well. So we can just do a do while loop. Well first we'll use a width statements. So with data sheet, because we want to loop through our data sheet and we're going to use a Do While. We're gonna do period. So we can interact with the width statement, else row loop or it because we want to use our row index, the column will use as a or one, whichever you want to use. And I'm going to end our loop statement. And we want to make sure we're incrementing our row loop for each time. Otherwise we will have an infinite loop. So rho looper equals row loop or plus one. And now we're just going to use the simple. We don't actually need statement. We're just going to update the value in cell K2. So summary sheets range. Just so it's easier for us to visualize what's going on. K2 is equal to dot cells. And actually I'll just copy this here, so be a little bit easier. So essentially as we look through column a in the data sheet, we're going to update range K2 summary sheet. And I just noticed that I have two typos here. This has three O's instead of two. And so this is a great reason why we use Option Explicit if I try to run this code right now, Option Explicit will toss the variable is not defined, so it'll tell us that we have a typo in our code. If we didn't have Option Explicit, would have just ran and we wouldn't have known that there was an error here. So let's fix this. Okay, and then we can see row looper is now capitalised. So now let's try to run. When we run the code, it looks like nothing occurred, but in reality would happen is the code ran so fast? Is it looks like nothing is actually happening. So what we can do really quick is let's add a little delayed. And we can do application that we will use now. And we'll add a time series. Time series called takes in hours, minutes and seconds. So we'll do 0 hours, 0 minutes into seconds, just so our code slows down. I won't go too much into detail into this, but this is just a handy tool to use to slow down your code. When you're using it yourself. You probably won't want to use any delays at all because you want the code to run as fast as possible. But this is just for visualization purposes. So don't worry too much about the weight. So now when we run, we can see this is updated to one. We're going to wait three seconds. You can see that the rows are collapsing. It's kinda hidden by my VBA editor here, but you can definitely tell that rows are definitely being collapsed. And once we get to number 7, that's pretty much the end of the code. And what we'll learn in future lessons is then how to automate saving each of these to a PDF. And then eventually it will start to e-mail them as attachments. So that's it for this lab. 25. Automate Saving the Dynamic Template To PDF While Looping: In the last lesson, we learned how to loop through our template. And now in this lesson we want to actually start saving the templates as PDFs every time we loop through a new employee. So this code is exactly the same as before. The only difference is I've just added comments. And so we're just going to start building off onto this. So in my example, I want to start saving the templates, the filenames based on the person's name. And we can use the person's name here in range A1. Because this is going to be updated every time we go through the template. So let's create two new variables. One is person name. We're going to make this a string because we're going to hold the person's name in this variable every time we loop through the template. And then we also want to save the PDFs to a specific folder. And so we'll also make this a string. So for the folder, I'm just going to copy this over. This is the folder where I want to save it. This is just on my desktop. It's part of the coding course and just say PDFs, but you can update this as you need. And then we're gonna go through our loop. So here we're in our loop section. And so every time we look in the code, we want to start updating the person's name based on the value in range A1 summary sheets. So we can just copy all of this. And we'll just make this range A1. So perfect. We have these two parts up data. So we have our folder where we're going to start saving the PDFs and we're saving the person's name. And now we want to actually start figuring out how do we save the templates as PDFs. There's really two ways of approaching this. One is you can just go through Google and try to find people's code online. The other option is to record a macro. And I'll just be honest with you, the code to export the template as a PDF is kinda long. And it's not something that you'll just not top of your head most times. But once you practice just how to get it, It's not that hard. So let's first go to our Developer tab. We're going to record a macro. Macro name doesn't really matter here because we just had it. We're just testing. And now I'm going to press F2, which will open up the Save As prompt. And so we have this prompt here. And this is the folder reminisced saved the PDFs. And we're going to change the Save As type to PDF. And I'm just gonna make this up as tests. Again, it doesn't really matter how are you're saving this. So we're going to stop recording because we're done recording. We're going to go back to, well, you can either hit macros or you can go back to your Visual Basic Editor. So we can go back to our Visual Basic Editor. We have our module one here. And so we see a lot of stuff here. And the one reason I don't like recording macros test stuff is because all of this stuff is just extra noise. And if you leave it in your codes, things could happen that you're not intending to happen. Specifically, what we can see here is this looks like what we're looking for, its export as fixed format. We can see it's XML type PDF, and then we have our filename here. So this is the folder path that I wanted to save two right here. And then this is the test.py, so this is the actual filename. The other stuff here doesn't look like that's important. No. Xl quality standard is just a built-in VBA. Value include document properties is true. So that's fine. Ignore print areas is false, which is also fine because we don't want to ignore print areas. And open after publish is also false, which is fine. We don't want to open up the PDF, so every time we save them, so let's copy this over. And now what we want to do is we just want to start updating what we want to actually happen. So we don't want the active sheet to be exported because again, that could be any sheet, any active sheet. So if you have multiple workbooks open, it can start saving nouns. So specifically we want to save over this or copy over this as the summary sheet. And then we also want to update this here. So I'm going to leave the PDF because we're going to use that as our final extension. We're going to save the file name as the person name. Then we also want our pulpits, you say it. So we're going to first save to the folder. And we just need to make sure it ends with a backslash. And then we have our person name. And then the extension which is a PDF. So perfect, this is really all we're looking for. And so I can give this a shot and just see if this is actually working. I'm going to press start. So we can see we're starting to loop through our template and let me drag over my folder so you can see what's going on. And you can see that in fact the PDFs are being saved. So I can open up one. So we have Steve are here and I'll zoom in. And we can see that as profit sharing is in fact, they're, so let's open up someone else, maybe Dave m. And then we can see that the profit sharing is not a day that Dave EMS PDF. So perfect. We can see that our code is actually dealing what we intended it or for it to do. 26. Send Outlook Emails With Attachments: In the last lesson, we talked about how to save the templates as PDFs while we loop through all the templates. In this lesson, we're going to talk about how to start creating Outlook e-mails and then attach those ETFs to the emails. So this is the same code from the last example with just a few comments added into it. So again, it's pretty much the same thing. What we want to do is we want to start working with Outlook. And as I talked about in the early and late binding section, there's really kind of two ways to approach this. One is you could Google around and try to figure out how people have created emails themselves on things like StackOverflow. And you'll get used to doing those kinds of things as you code more. The other option is we could first do early binding to learn how to interact with the Outlook application and then change it to late binding later on. So let's begin. We're going to create two objects. One is going to call out app. So this is just our Outlook application which will interact with, will create this as an object. And then we'll create our mail, which will be our Outlook e-mail that will play it. So we're just going to create these two. And so we want to create an object or the Outlook application. And the way that it looks like is this. For objects, you have to use the set command to set the object as something. And we use create objects to create the object. So this is late binding, and we're just going to use Outlook application. So you can Google around to figure out what the application name is. I wouldn't get too hung up about this. And version controls, you just do create objects and then you use a string, the application you want to work with. But as I talked about with late bindings, so this is late binding because we're creating the objects after we set are declared the variable. We don't have and access to see its properties and methods. So what I mean is once I've entered the period, excuse me, we don't see all these methods and procedures as drop-downs. So the way we can interact with it to start is we can go to early bind or at least have the reference to this object. So we're going to hit Tools references. We're going to scroll down until we find Microsoft Outlook. And I believe my version is 16. So this is again, the problem with early binding is version specific sometimes. And so if you were to give this code to someone who has an earlier version of Outlook and you did early binding, you might have issues with your code. The one thing we're going to write new outlook and not application. So this is also one way you can figure out how to get this name. So this name here is exactly this name here. So since we did that, we can interact without app. I'm not going to change this code, but we will change this back to be an object later on. So now we see we have all of these methods and different procedures that we can work with. Specifically the one that I'm interested in is being able to create an item. So the item that we want to create will end up being R or email. So create item here. And then we'll see there's this drop-down list. So these are built in to VBA, specifically to outlooks VBA. So you won't want to use these in your code. I'll put a list out on the lesson, a page and you can go to those and you can find that numerical value. Specifically create an e-mail is just 0. It's numerical value 0. And appointments and contacts and distributions have their own numerical value behind them. And so that'll be part of the link that I put onto the lesson page. So let's delete that for now. We only want to create an email every time we loop through our template. So now that we've saved the template here by exporting as a PDF, and we talked about in the last lesson, let's create an e-mail. So register that's Hotmail equals free item 0. So now we've created our email. Let's use a width statements so we can interact with that email. And so it won't be super obvious now, but once I run the code, once we're done with this, you'll actually see emails pop up. And so with the hotmail, what we want to do is we want to first display it so we can see it on this. But you don't have to display the email if you don't want to. Emails will still create in the background. So the next thing we want to do is we wanna create our two BCC and CC fields. So I'm just going to copy these in. These are very self-explanatory. Dot 22 or H2 and I2 to FCC and BCC. And the one thing I just want to point out is if you want to add additional e-mails, all you have to do is just use a semicolon. So if I wanted to add an additional one, IV out here and so on and so forth. And you can do that in all of the fields due to the BCC and SCC fields. Next thing we wanna do is we want to add a subject line, which again, these are all very self-explanatory. This is your subject here. And then another thing we wanna do is we want to actually create an e-mail body. So you can either use body, but this will be plain texts. And if you want to make the email a little bit more elegant, you can use HTML body. And so you can create an HTML e-mail. I'm not going to send these emails, but the way you do end up sending them is you use that send. But in this case, I don't want to send me e-mails. One thing I'm going to append at the end of my HTML emails is the signature. And this is just purely from testing over the years. What this does is going to replace the HTML, HTML body that opens up. So when our email displays, our signature will automatically populate if that's how your email settings are set up. And I've just learned that if you remove this string and you just make it an empty string, it'll just get rid of a few extra spaces before your signature. So that's literally all this is doing is describing the signature, getting rid of a few extra spaces and then just making it an empty string. So that's all it's happening. And then specifically the thing that we want is to attach email attachments that ad. And then we're going to use our same folder path that we just created, the PDFs. So folder to save person's name in PDF. One thing I want to show before I start running this code is what happens if you don't necessarily know HTML. But we will talk about this in the automating web tasks section of the course, is, I would suggest using this HTML editor and I'll put a link to it in the lesson guide. And so if you wanted to write an email and say, Hey Tom, here is your station statement. And I have an extra thing here. And thanks. And then we know our signature is going to be at the end. One thing you can do is you can hit Edit HTML source code. And so again, we'll talk about this a little bit more in the automating what test section about what is this information here. But I wouldn't get too hung up on it right now. You don't necessarily need to know HTML or be an expert. And I just want to show a few things. What if you wanted to change the size of the text? Let's make it 14. And let's make the font Arial for instance. And we can see that there's a whole bunch of extra stuff here, the style. And the one thing I want to point out when you copy it into your code is these quotations is I would first replace them as apostrophes in your code because VBA, when you use a quote, it thinks you're referring to a string. So if you change them to apostrophes, you won't need to change the quotes into double-quotes. So if you have a quote inside quotes, you need to use double-quotes in order to tell VBA you're actually referring to a single quote inside of the string, if that makes sense. So let me copy this in. I'm going to pull my HTML editor over off to the side and then just copy these in. And so once again, as we're copying these in, It's just good to remove this single quotes as apostrophes. So I just hit Control F. And then we're going to hit Replace. And we're just going to go to here. And then we're going to replace with an apostrophe. And then the key here is making sure you're not searching through the entire module. Just use the selected text. So I'm going to select all or Replace All. And just to make sure I only selected the text inside of the post, not the actual post themselves. And so let's just add the rest of the lines here. So I'm just gonna do this so I can paste in easier. And so I'm just going to paste this in. So this getline. And again, we're just going to replace. I think you have to redo the replace, otherwise, you can't hit select a text. So think well CO2 apostrophe. And the last one is just the last line of text here. And the last one we're just going to replace again selected text replace. And that should do it. Before I run. One last thing I want to change is I don't want to have to always say, hey Tom, I'm going to change, this will be the person's name. And so instead of Hey Tom, it's gonna say hey, person name, so Steve or Dave or Sandra. And then this is your compensation. And then Thanks. So let's give this a try. So the emails are being created onto my other monitor, so on the track them over. And you can see that Dave M here. I'll see when. And you can see that the names are matching the name of the actual e-mail body. And we can see our signature came in and it looks normal. Here's our two field, here's our Cc field, and here's our subject line. And we can open up the PDF and we can see it's actually date. So that's how you create outlook emails using Excel VBA. 27. Send Gmail Emails With Attachments: In the last lesson, we talked about how to create Outlook e-mails and attach our PDF templates to them. And this lesson we'll talk about how to create Gmail emails. So before you run this script, which I'll walk through for Google specifically, you'll want to go to this URL here and turn on less secure apps. Because you're sending an e-mail using Excel. And Excel inherently is not that secure in terms of holding your login credentials. It's considered a less secure app. So if you turn it on, your script will be able to run and interact with Gmail. So I'm not going to go step-by-step, like in prior lessons and how to create this, because this code is a little bit more involved. And I had to do some googling to figure out how to interact with the SMTP server. But I still want to walk you through what is going on in the code. So you do have an understanding of what is going on and how to be able to update this yourself. If you want to use this code to say, interact with Yahoo instead of Gmail, for instance. And you should be able to interact with pretty much any major e-mail provider. So a main difference between the code and this code is we're going to actually declare variables for your email address and your e-mail password, which is here. And so you can update this for your Gmail credentials. And we'll scroll down and we'll see the rest of this code is exactly the same. We're just looping through the template PDFs as we go. The one difference between Outlook and this approach is we're going to create a message objects using CBO and CDO stands for collaboration data objects, and they allow you to interact with servers. And specifically in this case, we are going to interact with mailbox servers. So we'll need to create two objects. One is the e-mail itself. And because we're interacting with the server, we need to set up our configuration. So when we send the e-mail, we also can tell the server what to do with it and how to, well, more importantly, how to actually connect with the server. So this is again, I just found online, we're just loading some default configurations. And we're going to create an object where we can set fields for the configuration to it. This looks pretty much the same as the alloc example. We have our front field, which is a slightly different, but it should just be your e-mail. So I'm using the variable here. We have two, blind copy and carbon copy. And the same point applies as the outlook example. If you want to add an extra emails, just use a semicolon. There were a subject field or HTML body. And we're attaching our PDF using the same folder path to the folder to save person name. And now I'll just go quickly through the same example I showed in the Outlook example for how to update the HTML yourself. I'm going to scroll this over. So we have our on-line HTML editor, which is a Lincoln. The lesson. We have just some made up text here. And I'll make this aerial and size 14. And then once you hit edit HTML source code, we'll see that we can copy this directly into our code. So I'm going to drag this back over and we're going to copy this in and just copy it in between the single quotes here. One thing we need to make sure I select the text inside the single quotes and we're gonna get rid of all these single quotes and make them apostrophes. So we'll hit Control F. Make sure we're on selected texts, not current module. We're going to hit Replace. And it looks like I have to reselect selected text. We're going to replace single quotes with apostrophes. As a reminder, the reason that we need to replace the single quotes with apostrophes is anything inside single quotes is treated as a string and VBA. So if you have a single quote inside of quotes, VBA can get confused. So if you want to represent single quotes inside of quotes, you need to make double-quotes. And then VBA will know that you're referring to an actual single quote inside of a string. The other approach is you can just use an apostrophe because HTML is not that sensitive. Html will understand both the apostrophes and single quotes. So that's why we do that approach. And then in this example we'll just get rid of Tom and then update for our variable name, union concatenation for name. So as we loop through our template, this person name will update. So that way the name is unique for each e-mail. The rest of this is just a pre-built HTML template that I have here, but you can update the rest of the lines yourself. And there's no signature here because we're not opening up email on our screen to display. So if you want to add a signature just added as part of the HTML editor, and then you can insert it here. So this section down here is a little bit more unique than the example, because we need to interact with the SMTP server, which is the mail server with Gmail or Yahoo or whichever email provider you're working with. So we're going to create our configuration here. I won't go too much into these ones, but I would just leave these as they are. You want to use SSL, this will make your emails secure. To make this true, you want to authenticate, let's just leave it says one, but the ones that I want to show you and how you can update yourself, let me drag this back over. Is this as part of the lesson as well? Through this link, this is the SMTP configurations. We just scroll down. We found the server name right here, your username and password or just your Gmail password and e-mail. And the port is 465. So if you wanted to say find Yahoo, you can just update this for Yahoo. And you can poke around the website and find other server configurations. We'll just scroll down again and we see that there is not much different. The only thing that's really different here is the server address is yahoo.com, and the port is still 465. Username and password will just be your Yahoo Mail account and password. So if you need to update that, you can use this website to find those configurations. So you would update the server address here, the port year. I wouldn't update the send using and the username and password are just what you set up. And down here, we set the configuration to the e-mail that's going to be cents. And now we send the email. So again, if you need to update this to be part of Yahoo or some other SMTP e-mail provider. Just update configurations here. Now I'm not going to create an email for this lesson just because I don't want to expose my login credentials. But I did test this code. It does run e-mail is sense. The HTML body matches what's sent here. And the attachments do actually attach this PDF. 28. Selenium Download: So this will walk you through how to download Selenium basic onto your computer. So that way you can interact with selenium using VBA. And specifically Selenium will allow us to interact with Google, google Chrome. So this is in the lesson text. Go to this URL, and then let's go to Selenium basic. Download the dot EXE file. And once you download it, run the EXE file and just go through the steps. They're pretty simple steps, just keep hitting Next. So after you're done doing that, go to Chrome Driver, which is also in the lesson text. And you'll see that there's a bunch of different versions here, version 90 to 9190. And so on. The way that you can find a version of your Chrome is pitting these three dots. Go to Settings about Chrome. And you'll see, I'm on version 91. You don't need to worry about the numbers afterwards. And version ID 1 is this version here. So we're going to open it up. We're going to download the Win32 version, that zip. Once it's done downloading, open up the zip folder. And we'll see that the folder just has this Chrome Driver dot EXE. So I'm just going to drag it over here real quick. What we want to do is we want to go to Selenium basic on our desktop, and that should be located at the C drive user's username, You're on AppData. And by default, AppData might be a hidden folder, so you'll just need to unhide hidden folders. Then local and selenium basic. And you'll see that selenium basic comes with the Chrome Driver dot EXE installed already. And the problem is Chrome driver dot EXE is outdated, at least the one as part of this linear basic download. So we just need to update it to the one that to, to the latest version based on your Chrome. So what you do is you go back to your zip, zip folder and I just drag it in. And there should be a prompt to replace. So that's what we're gonna do. And so that should be mostly. But once we test our VBA code using Selenium, you might run into an automation air. And if you do, the way you can alleviate that automation air, excuse me, is by going back to this folder. So let me Basic, go to scripts and then starts Chrome here. So once you click this, a Chrome browser should open up. If it doesn't open up, it'll open up a link to where you can download some development tools. And I would proceed with downloading those specific tools is really just the.net framework. So once you download it, you'll be asked to restart computer. Restart your computer, and everything should be working fine. 29. Learn HTML: In this lesson, we're going to talk about what HTML is and what it looks like. So that way we can interact with it. When we do our web scraping and web automation asks. So way that HTML a lot of times looks like is it has a beginning tag and an ending tag. So something like this. So it'll have a tag in carrots, and then it'll have an ending tag like this. And I can zoom in a little bit so it's easier to see. So something like this and there'll be text here inside of it. And then you can make your HTML elements have different styles. This. And maybe you have a font family, and you'll use a colon here, and then maybe it's Arial. And a lot of times styles will end with a semicolon. And then you can add additional step. Here. Other things that HTML elements, lot of times, habits, class names, which will look like this. And the class name is useful for Cascading Style Sheets. In style sheets are used in HTML to make the HTML webpage look nice and aesthetically pleasing. And it's easier to use JavaScript or Cascading Style Sheets to find class names rather than having to put the style on every single HTML element. So the cascading style sheet will tell every class name the certain tax, maybe have a certain font family and maybe certain line spacing and other things like that. Another common thing that's used as part of HTML elements is ID. In ID's should be unique to these chump element. And this helps allowing JavaScript and eventually our code to be able to find elements if there is an ID. So something like this. And just so you know, these peas inside these carrots are called tag name. So these are different tag names which will be used throughout our code. So p is for a paragraph. Other common ones are span. Spans really don't do much other than just allowing you to mark up your text and make it have maybe have a certain style or something like that. There's also DID which justify its contents. So you'll see this a lot in HTML. And other common one is ordered lists and unordered lists. So it's easier if I just use our HTML editor here. So this is an unordered lists. List one, list two. And then if we edit our HTML source code, we can see that there's a UL tag and there's the ending UL tag. The only thing that's unique about the ordered lists and unordered list is inside the tags will have LI and an ND now lie. And so this just means the list item. And so if you have a longer list, they'll just be more allies. And it'll look, the premise is the same for an ordered list. The only difference is instead of UL, the opening and any tags or OL. And then HTML, the structure for an HTML webpage will almost always look like this. It'll start with HTML. Will be an ending HTML. You'll get a head tag. And then an ending head tag. And inside the head tag usually are JavaScript's scripts and CSS or Cascading Style Sheets. So JavaScript usually will be pulled in like this. It'll have a script. And then a lot of times we'll have an SRC. And then cascading style sheets be similar as well. The difference with Cascading Style Sheets is there'll be pulled in like this link and then use a hyperlink reference. And that, no, and and sorry, I just poked around and actually there won't be an NDA link tag. The only thing that will be different is they'll use this REL style sheet. Now, excuse me. Now I don't wanna go down a rabbit hole here because this won't be too important for us, but I just want to show you what the structure looks like. So I wouldn't worry too much about Cascading Style Sheets or JavaScript, but just know that there'll be typically in the head. Now, the other thing that will be an HTML is a body tag. And this is the actual contents of the HTML. So lot of times when you're doing web automation tasks or web scraping, you're just going to look at the body tag. And that will have all of our divs, all of our P tags for paragraphs and ordered lists and unordered lists and things like that. So that's how we can find our HTML and kinda what it looks like. And so I just wanted to give you some familiarity with what it looks like. So that way when we're poking around the Web Inspector in our browser looking for HTML elements, at least you have a sense of what I'm referring to. And again, I just want to do some tests, tests. And then another paragraph. And then we'll edit our HTML source code. And you can see these are P tags. If I make this an ordered list, we have a well and then list items LI in-between. Same thing with unordered lists. Ul, UL, and I list items in between. And I don't think there's an example here from Ebola use divs, but you can also use divs. If you need to hold your texts. Folding usually is used with strong. And then you would also have the ending tag for strong as well. You can also italicize, which is EM. And then one thing I haven't talked about, but as you probably noticing, is HTML elements can be nested inside of each other. Which means you can have an HTML element inside of another HTML element. So that's what's happening here. We have our unordered list. We have the list items that are in-between the unordered list. And then we have different HTML elements that tells the text to have some sort of aesthetic, so italicized. So we'll be looking at this in future lessons and loping up the Web Inspector to actually automate tasks using Selenium and Internet Explorer. 30. Web Inspector: In this lesson, we're going to talk about how to use the Web Inspector to you the HTML source code in our browser. So the approach here is pretty much the same with every major browser. What we'll do is we're going to right-click and we're going to hit Inspect. And before we got too far, this is just my old personal blog, and we'll be using this for the Internet Explorer and the selenium examples. And the reason is, is because I just don't intend to update this website. So the code will continue to work for, for years to come. So we've right-clicked and we've hit Inspect. And now our web inspector is opened up. Specifically, I'd point you to the elements section, and this is where we'll actually look at the source codes here. And there. In every major browser there's some sort of select an element in the page to inspect. So once you select it, you'll see things are highlighted as I move around. And if we want to look at this post. So in the example is we're going to go through all of these posts. And we can see if I collapse this one. And the last example that I showed you of how to look at HTML and what HTML looks like. We see there's an OL tag because this is an ordered list. And then here's all of our LI tags in between and then our closing OL tag. And as I mentioned before, sometimes elements have class names. So we can see here class equals posts. We probably can use this as a selector. I just want to give you familiarity of what this looks like in future examples. And I just want to point this out now. The other thing that you might want to look at is network. So network will give you all the traffic of the page. So if I reload this page, you'll see a whole bunch of stuff is being loaded in. So we called our, our base URL here, and we can see that there's a dot js, this Java Script, PNG, some images are coming in. So the page gets loaded and then the browser actually pulls in all the JavaScript, all the cascading style sheets, all of the images. So you can also look at the network traffic this way. I won't go too much into that now, but it is something that we'll talk about later on in the course. But I just want to point out the elements. And so that's how you can look at HTML source code using any major web browser. 31. Automate Internet Explorer: So in this lesson, we're going to learn how to automate Internet Explorer to complete some web tasks. Specifically, we're going to look at my old personal blog. And the reason we're using this web page is because I don't intend to update this. And so this should work for years to come. So this link will be in the lesson text. And the goal of this lesson is we want to loop through all of the posts on this blog, grab the post name, grab the hyperlink reference for each of these posts. And then we want to show them on the screen. So we just want to loop through the posts. At the end, I want to show you an example of how you can click an element as well so you can see how clicking works. So we'll click the homepage at the end. The first thing we need to do is we need to figure out how do we even identify these HTML elements. So using the Web Inspector example, we're going to right-click. We're going to hit Inspect. We're going to hit this button here so we can select elements. We're going to go here on the very first post. For now, I'm just going to close this. And we can see that this is an ordered list which is expected because this is in fact an ordered list. And we can see that the class is post. So let's click this, Let's copy it. And the reason I'm copying it, and I just hit Control F to use to find the fine feature is because I want to find if there's any other elements with the class name posts. So we'll just paste in posts. And we'll see here it's one of one. So we know we can use the class as a selector here in this case. And then the other thing I want to check is if we open up these LI tags, we see there's a nested a tag which has our hyperlink reference. And I hadn't mentioned before, but the a tags usually have eight reps are hyperlinks. And so you can use those to find the hyperlink reference. And we see that the text inside the tag. So here's the ending, a tag, here is the actual post name. So perfect. We first just need to grab the className posts, go down to the LI tag, loop through all three LI tags. Grab the a tag inside each LI tag, the hyperlink reference, and grab the post name. So let's go back to our code. I've just prefilled these because we've seen this in prior examples. We're just going to turn off screen updating, and we're going to turn off enable events. We want to declare a variable for our Internet Explorer. So we'll do dim I0. And we could use early binding if we wanted to double-check how to interact with Internet Explorer. So maybe we'll do that here just so we can see what's going on. And the way you do that is again, Tools, references, and then it's Microsoft Internet controls. And so we'll instead create this as a new Explorer. And I don't think you need to put application elements. One is just Internet Explorer. And one thing we need to be careful about is this Internet Explorer is the same as our module name here. So let's just update this to be e x for example. And so we should be able to interact with me now. So see, we're able to have the address bar. We have this container document, et cetera. I'm going to add the keyword new. This is just usually good to deal with early Binding objects and stuff like that. Just add to it. The render, use it with statement so we can interact with IE and width. And the first thing we wanna do is we want to make the Internet Explorer browser visible, so true. And then we also want to navigate to our URL. And so our URL, we'll just copy it in here. Like this. Internet Explorer is not great at knowing when the page is fully loaded. So usually I'll just use a weight. There are other ways of weeding for the URL to load. But I think just using a preform weight is probably yes. So we'll just use the same approach that we've used in prior examples to have like the PDF templates. Weight, we'll just use the Excel application will use weight we use now, and we'll use time cereal and five seconds because this is hours, minutes and seconds. So now that we're waiting for the page to load, we need to grab the host contract supposed to do this, host has object. And the way that you can set the post is set, post that document. And then we're going to use get elements by class name. And so I'll copy that in here. And it looks something like this getElementsByClassName. And then we're looking for the post class name. And because we're looking for multiple elements at the same time, we'll need to tell R code which of the multiple elements we're looking for. In this case, we're looking for the 0 element. So this is zero-based, meaning 0 is the first element. The first element is really was 0, and then the second element is 13, is two, and so on and so forth. And then specifically we wanted to get the LI tags underneath this element. So now we're going to use posts. And we're going to again just use the post that we grabbed four. But then we're going to grab the elements by tag name underneath it. And so the way we do that is like this, getElementsByTagName. And you'll see the convention is sort of similar here. This is by class name, this is by tag name. We're going to look for the LI tags. So now this one, we're not going to tell the code which of the elements, because we're going to want to loop through them. So we're going to add post here. We're going to create a new object. So that way we can loop through all the posts. So for each post in posts will do next pose. Now we're going to loop through all the posts. And what we want you to do is we want to put the information into this Hari sheet. So we're gonna do another whiskey men and women. And what we're gonna do is cell's row looper, which I haven't defined yet, is equal to a blink tag. So we need to define row looper because we need to loop through the rows as we loop through the posts. And we'll make this an integer. And we'll say row looper is equal to two, because we're starting on row 2. Here. We're going to grab the post names and the post hyperlink references. Okay? So rollover is equal to 2. And every time we go through a post we probably should increment row looper. So again, we're within this for loop here. And then I'm just going to copy this because we're going to add data into column B. So now we're going through each post. So in other words, they're going through every LI tag and we know that there is an, a tag underneath every LI tag. And so just to visualize that, let me pull over HTML inspector. To see here we're going through rubbing the post first here, my class name. We're then going to grab all the LI tags underneath. We're going to loop through all of those. And then as we loop through the LI tags, we want to grab the a tag within the LI tag. So what we need to do here is copy this. We're going to use post. We're gonna get tag names within the LI tag. We're going to grab the a tag. There's only one a tags, so we're going to have the first one. Again, this is zero-based. And now we want to grab the inner text. And that will give us the text inside of the element, which is the post name. We can just copy this. And then we can paste this over here because we're just going to grab the same a tag doing the same convention. The only difference here is we're going to grab the href attribute, the element or the hyperlink reference. So great. I think we can actually test out this code to see if it's working. So I'm going to hit Run. And we can see here that the Internet Explorer browser opened up. Wait five seconds. And then it's going to grab all of the post names and the hyperlink references. Now the one thing we didn't do is we didn't tell the code to close or Internet Explorer browser. And so the way we do that is using quit. And that will close our browser. But our code to actually did run. So here are our posts names here, and here's the hyperlink references. So what we wanna do is every time we start the code, we want to delete any values in the sheet. So I'm going to show you something new and it's a new trick you can use to find the last element. We're not the last element, the last row with data in it and the Excel file. And so it'll look something like this. I'm just going to copy this code in and just talk you through it because it's pretty self-explanatory. We have a width statement. We're going to look for the last row with data using cells in the sheet, are going to use the find function. We're going to use the wildcard character, which I talked about, the excel efficiency tips. We're going to search by arose and we're going to return the row with data in it. And then we're just going to check it. The last row is greater than one because our header row will have data on it. And then if, if there's data beyond the first row, and we're just kinda delete rows two on to the last row and just delete them. So we're starting with a blank slate every time. Okay, so we can try that out again. And let me drag over my browser. And unfortunately it kinda went quickly, but it did wait five seconds, grab the post names, and then quit. Okay, So perfect. Now the thing we want to do is now we have the data in our Excel file. What we want to do is we want to loop them. So what we can do is we can find the last row of data in it using the same approach here. We'll just copy this. We don't need to delete anything. I'm just going to indent this here so it's easier to see what's going on. We don't need this. We're just going to find the last row of data in it. And that respite for row looper is equal to two. It's the last row. I'll do an extra looper. So we're just kinda loop from row 2 down to the last row. And then we're going to tell inner explorer to navigate to the URL in cells. Row looper, and column beam, like this. So we're going to navigate and we'll just need to tell the summary sheet is what we're looking at. Some summary sheet that's cell's row looper, which is what we're looping through, and then navigate the very end. We just want to hit the home button. Before we do that, we should add a delay so we can see that the pages aren't absolutely. So I'm just going to copy this. So every time we navigate to a new URL, we're just going to wait five seconds so we can see on the screen. And what I want to do is to double-check the code for the homepage for my drag over my browser. We're going to make this a little bit smaller. I'm going to highlight the homepage. I'm gonna make this bigger again. And we can see this is an unordered list and has a class name of sidebar. Now, I will look for that using control F and then pasting sidebar NAB in, we can see it's one-to-one. So again, we could find this class name. We can see this is an unordered list. So again, we can just loop through all the LI tags, but specifically here, first Allied attack as our own page. And then there's the a tag which we can click. So let's code that in at the very end. So we can really just use the same approach as this. So we could change this to be a new object. So that way it's a little bit more understandable of what we're looking for. In this case. This will just make it a little bit quicker to shows part of the tutorial sidebar now is our posts or not our host, our class name. And then we want to get the LI tags, but specifically we just want the first one in this case. And then we can do posts that clip. And again, we'll just wait five seconds to see that happen on our screen. So hopefully that makes sense. We're grabbing the class name. We're grabbing multiple elements. So we need to tell R code which one of those multiple elements to grab. So zero-based meanings, the first one. And we want to grab all the LI tags underneath it. But in this case we just want to grab the first one because that's the home lie tag. And then we should probably also grab the a tag underneath it. Let's grab the first LI tag and then the first a tag underneath that LI tag. And then we will click that one. So let's give that a try. Though. We just grabbed all the posts. We just went to the first post. Now we went to the second post. Now we're gonna go to the third posts. At the very end, you should see that the home button here is being clicked. And see if we went back to the homepage and the Internet Explorer browser will now close. So this is how you can automate Internet Explorer or web automation tasks using Internet Explorer. 32. Automate Chrome (using Selenium): This lesson will be very similar to the internet Explorer lesson. The biggest differences we're going to interact with Selenium and specifically with the Chrome browser. The benefit of using Selenium is that Chrome will know when the page is actually done loading. And just most websites these days are going towards being able to interact with Firefox and Chrome. And so what sites will just generally be more responsive pathway. And in my opinion, it's just easier to code and selenium. So similar to the explorer example, we're going to open up a reference to Selenium just so that we can early bind and see the different methods that we can interact with. So we'll scroll down here. Selenium type library will check it. And we'll just say new selenium. And we'll say Chrome driver here. And this here, as I showed in the Internet Explorer example, is just going to look for the last row with data in our sheet and our summary sheet specifically going to use the wildcard character to find the last row with data in it. And it's going to return that row at the last row is greater than one. We're just going to delete everything beyond that, essentially meaning that our header row here is what we're going to return our data or header rows already in row 1. So that will have data. So if there's any data beyond that, we just want to get rid of it. And then quickly going through the HTML side of things we're trying to do is we're trying to loop through all of the posts on my personal blog. And again, the reason I'm using my own personal blog is because I don't intend to update this blog. And so this should work for years to come. So we're just kinda looped through all the posts on the blog. We're going to grab the post name, the post URL, or the hyperlink reference. We're then going to loop through those hyperlinked references and navigate to them. At the end we're just going to hit the note are hip, but click the homepage just so that way you can see how to click. So in order to find these elements, we're going to right-click and hit Inspect. We're going to then sense a select these elements here, here, and then C and C, the HTML aspects of these HTML elements. So in the Internet Explorer example, we found that these are unordered lists, which makes sense because it's 123, it's an ordered list. We have our ally list item elements underneath the AOL. And then within each Ally there's an a tag which has our hyperlink reference. And inside is the text of the post name. We see that the OL tag has a class name of posts. And we check in the Internet Explorer example, there is only one class name of posts, so we can use that as our selector. And then we'll use that selector to go through all the LI elements. And then inside each LI element will grab the a tag. We'll grab the H ref for the hyperlink reference, and we'll grab the inner tax or the post name or the text inside of the HTML element. So let's begin. So we've created our Selenium bought. Let's use a with statement and whip. And slightly different than the internet Explorer example is we can use get to get to the URL. So let me just copy that in. The URL. Is here. The nice thing about Selenium is we don't have to use a quick command to close the browser once the script is done running or if there's an error, the Selenium Chrome browser, it would just close for us. We're going to use a weight just to see that the Chrome browser is working. So we're going to use application that weight. We're going to wait now. And we're going to use time series. Let's wait five seconds. So we can run it here just to show that it's working. And so this is my Chrome, a browser. And you can see it's being controlled by automated test software here. The next thing we want to do is then grab the posts faced by the class name. Convention is slightly different than our prior example. Instead of getting class names by class names. So I'm gonna do the internet Explorer example. We use find element by class. And if you want to grab multiple elements and selenium, you'd use the S here, find elements. But the nice thing about Selenium is if you just want to find the first element with that instance. So whether it's a tag name or a class name or something like that. You don't need to use elements. So we can just find element by class, specifically posts. And then we want to find elements by tag. And we don't need to put tag name or className is just class and just tag. And we want to loop through all of the, all of the posts, specifically the LI tags. So we'll do for each host and posts. And then next post. And as part of the internet Explorer example, we just created a post object. Here. We declared it as well as post is the same thing. And let me just wrap this so you can see what I'm talking about. We'll also have a homepage object at the end, which we'll use to click. So now we can loop through all the posts. We'll use a width, say mental in summary sheet because we want to return to b values to our summary sheet. We'll use an end width. And we'll say dot cells, row looper. And then column a is equal to an empty string for now, just so I can copy this. And we'll return a value and also Column B. And we'll have a empty value. As we loop through each post, we want to increment our row Luther split to grow looper equals rho looper plus 1. And now that we're going through each post, you can just say post that find element. We don't need the elements by tag. We're looking for the first eight had and with slightly different about this than Internet Explorer, is will now use attribute. And the attribute we're looking for is the inner text. And then we can do the same thing for Column B. Except what we're looking for is the H Rap for the hyperlink reference. And before we run our code, I noticed that a small error here should be posts because we found that the post elements by its class name here. And so we want to interact with that specific element and find the LI tags and 100. So let's try to run our code. So we have a Chrome browser open up for us. Just going to wait five seconds to show that the page opened up. It's going to loop through all these posts for us. And similar to the internet Explorer example, we have the post names and the hyperlink references. So we wanted to find each hyperlink reference and then loop through them and navigate to that. So we'll just use this code here with this reuse it. So now that we've grabbed all the posts, Let's find the last row of data again. So that way we know which one it is. We'll delete this here because we're not deleting any values anymore. And let's say for a row looper equals 2 to row Lubert, next row Luther. And not to roll loop, or this should actually be last row. We'll go from row two to row four in this case because that's the last row of data which is here. And then each time we go through, let's use the GET statement. And we'll get the value in column B. And we just want to add summary sheet because we're no longer within this width statement here. And then we'll just use a quick weight as we go through each one here. And then we're going to wait. And at the very end of everything, we just want to be able to click the homepage. For the way we look for the homepage. Again, it's the same thing as the Internet Explorer example. We're going to use our Inspector. We're going to select the home. We should see it as an unordered list. It looks like it's the list here. And in fact it is an unordered list, as you can see you out here and then UL and intact. And we see all over the LI tags underneath it. And we see that there's an a tag as well inside each LI tag. In this case, the home page is just under the first LI tag and the first a tag underneath it. So we only need just need to find the first LI tag. And before I switch this over, we noticed that the class name is sidebar dash NAB. And I checked in the Internet Explorer example and there's only one side yard dash nav class. So we can use that as our selected. So what we'll do is we'll just copy this here. Will paste it here. We had a homepage object that we declared above. I showed you. I'm just going to replace posts with that. We saw that sidebar dash NAB is the class name. But that's what will change here because we're going to find that class. And we only want the first LI tags, so we no longer need elements. We just want element by tag. And then we want to find the a tag underneath the LI tag. So we'll just paste this at the end and look for a tag. And after all of that, we'll just say homepage Doc. And then we're going to just wait five seconds to see that code actually did that. So let's try to run our code. I'll drag this over. We're going to wait five seconds. We're going to grab all the post-data. We're now navigating through all the posts and we're going to wait five seconds each time. So now we're on the second post. And now we should be on the third post here in a second. At the end we're going to hit them homepage here. And so we'll go back to the homepage. And we're just going to wait five seconds and we're going to close. So that's how you automate tasks using Selenium and specifically Chrome is insulin. 33. How To Find A Website's API: In this video, I'm going to walk you through how to find a nonpublic API on the web. A lot of more modern websites are using APIs to pull in data onto their website. However, the API is not exposed public, but there are ways for you to find it. So if you have a major website and it has an API, usually there'll be documentation on the web and you can figure out how to interact with it. But a website like this, this is a coding project I just had recently over the last couple of weeks. The data looks like you just are on a web page and there's much images and information. This is specifically art galleries. But what if we wanted to grab information from this website? How would we do that? So what I'm going to first do is I'm going to right-click and hit Inspect. And then I'm going to just drag this down so it's little bit easier to see. Specifically what we want to look for is this network tab. I'm just a little bit bigger. And then what we're gonna do is we're going to hit one of these sections. So it's contemporary design. There's feature galleries near my area, but let's hit this one. And then it looks like again, there's just a web page of information that gets loaded. But in reality, what's happening is there's an API that's pulling in, in this information. And so once we find that at API, we could actually interact with it and grab data much easier than having to scrape this exact web page. So the way that we find it is first we look for one of these posts or one of these galleries here. So fears and con, we can actually search for it. And if you don't have this tab here, you just hit Control F. And then you'll have this search feature opened up for you. So we're going to look for this. And usually the way you can find that there's a nonpublic API is the information will look kind of like this. At first glance. I'm going to expand this just so you can see. At first glance, this doesn't really look like anything useful, and it looks like just a bunch of ugly text. But I'll show you in future lessons that this is actually JSON formatted structured data type. And we can actually parse through this very easily. The other advantage of finding APIs like this is you don't need the web page to load, you don't need all the JavaScript cascading style sheets. And so working with JSON and APIs is significantly faster. Also, you can usually grab a lot more information at a given time. So this is the response. So this is the actual output that we receive. So we can actually copy this later on in parse through it to figure out how we can parse through this JSON data. But specifically what you'll be interested in is the headers. And so this is the request URL. So this is the URL where the API resides. This is the request method which we'll use later on in the lesson. And there's really two main types. There's a GET request and post. A GET request just pings the URL and asked for information. A post requests does the same thing. But the only difference is a post requests hosts data with that request where sends data with it. So that way you can ask for different types of data from that specific URL. Some stuff that might be interesting to you, but it's not super relevant is the response headers just to see what kind of responses are being sent from the server. I don't use these a lot except for looking for cookies, for instance. And cookies are used a lot of times with logins. So a server might give you a cookie that you use. As you poke around the website. The cookie is sent with each request to say that you're actually logged in. But the ones that we care more about are the request headers. And specifically accept is a very common one. And we can see that we're accepting only JSON type data. And other one is also Content-Type. And again, we're looking for JSON data. Other common ones are the origin. And you can kinda mimic as if you are sending that request directly from this URL or from the domain. And then also another one is refer. One I always signed with my request is user agent. The user agent just pretend like you're using a browser. So this is just showing that you're using one of the modern browsers, Chrome or Firefox. At the very bottom is the request payload. So since this is a post request, this is the information that was actually sent with the request. So again, we can copy this and test it and play around with it. So I just wanted to show you how you can find non-public APIs using the Network tab here. And then in future lessons we'll actually look at how to ping these kinds of websites and actually get data from them. And then parse the data. 34. Learn JSON: In this lesson, let's talk about what JSON is and what it looks like. So in our last lesson, we were trying to find a backend API for this website artsy dotnet. And we found a here and we have some data that looks like this. And you see there's a lot of brackets and a colon and a bunch of quotations. Whenever you see something like this, you can pretty much assume that's probably the back-end API. So the website is pulling in data from this URL, here, the request URL, and then it's parsing the data and then presenting it here on the website. So this can be beneficial to us if we want to parse a lot of data from, from the web page, or if we just want to interact with it and maybe not necessarily grab data. We can also post data APIs as well to, to execute things like stock trade for instance. So let's talk about what it kinda looks like. I'm going to copy this, I'm gonna hit control a copy and the response here. And I have a link on the lesson for this URL. And this will just make JSON more reader-friendly. So we can see this is very tough to read, but this actually looks like something that kinda makes sense. So we can see there's data. Category. Total is 3300, give or take. And that's implying that there's over 3300 galleries on art C's website. And then we can see there's counts, ID, and et cetera. So the way bad JSON usually looks is that there'll be a beginning and ending brackets. And then things that are put into quotes that we can have data. And then it uses colon. And then there's some value. And these are usually called key value pairs, and then they end with a comma. And sorry, just to clarify, you'd only use a comma if you're going to add an additional line of information. So something like this. But if you have a comma at the end, you'll receive an error if it's just one line. So the final line of data will have a comma, so you'll just execute it. And so the key is here. And then the thing inside the colon is the value itself. And the reason JSON is really easy to work with is you can find the key of the JSON because keys are unique and then you can find the value pretty easily and it's very easy to parse through. So something might look like this. And then tests is equal to value two. And so we can see, we could find the value of data first, it'll give us value here. And then if we found the key for tests, which we'll see in future lessons how to actually parse through this using Excel VBA. But I just want to give you some familiarity what this looks like now. So that way you can kind of understand how the parsing is working later on. So test, if we use the p-value for tests, then we'll get to. But the values don't necessarily have to be strings. In other words, they don't have to be encased in these quotes. It can be a list of data, so it could be apple and then hair for something like this. And then in this case we still can parse the list of data, but it'll just be a little bit different. We'll have to tell the code which of the list items that we want to return. So this is very similar to the last lessons of Internet Explorer and selenium. When we want to grab multiple elements and then tell the code which of the elements we want to retrieve. And then the other thing is JSON can actually have additional JSON inside of itself. So it can look like this. And then there can be an additional key value inside of that. So key value and just any changes to the double quotes. And so we can see here that this is JSON, and then there's another JSON inside of it. And then the JSON can have even more JSON. So you have test again and additional value 0, and then we just keep it a comma. So hopefully that makes a little bit of sense. It'll make more sense as we go into the VBA coding section of this will actually parse through this. But as you can see right away, that this data is a lot smaller and file size than HTML. And so the great thing with interacting with JSON and XML is you don't need a browser to load in all the cascading style sheets, all the JavaScript, and all those other things to make the webpage look nice and glossy, you can just pull it in the actual data. And then the other nice thing is you can a lot of times manipulate the, the API to give you more data than you can find on the web. So a lot of times websites will limit results to 10 or 20 at a time. And you can update that to maybe 200 or 500 or 1000. And what I mean by that is if we go back to our website, we'll go to our headers. If we pull up. And then we go down to the bottom to where the payload is. We can copy in this payload to copy the full thing. This is also JSON here. We copy it in here, make pretty, we can see there's a structured aspect to this JSON. And so if we wanted to do grab additional pages, we could update this in our code and maybe make the code go through six pages. And then what I mean by being able to return more than the website results. You can see size here is 15, for instance, insight here is nine. So we could theoretically play around with this and test this nine and 15 and then update it with an API tester to see how we can get more results from the API through, through our own codes and through our amines. So hopefully this is helpful and I think it'll make a lot more sense once we actually start coding. 35. API Tester: In this lesson, I wanted to show you how to use a, an API tester so that way you can test on your own and also how to update the values if you're using a post request for instance. So you can see different outputs. So in the last couple of lessons, we've been using this artsy website and we found this JSON response that we've seen before. And it kinda looks like it's probably an API. And it definitely is because the response is a JSON response, then it's being used by the web page to display all the different values. So what we can do is we can go to this REQ BIN, which I'll provide a link on the lesson page. The first thing we're gonna do is we're going to copy that request URL. And then just make sure you're in the inspector, you're on network and your headers here. And we'll copy this here. And then we see the request method is post. So let's change this to a post requests. We're gonna go to this content. I'm going to ignore authorization. But a lot of more modern APIs require that you use a bearer token to verify yourself. But that's a little bit more advanced than I want to describe here in this course. So let's go back and let's grab our request payload at the very bottom so we can copy this. And I've already shown this in the prior lessons, but let's just repeat this. I'm going to paste it into this JSON pre-prints. So I'm going to select all paste and then make pretty, I'm going to take this more formatted output here. I'm going to copy it. You don't necessarily need to do this step, but it just it's easier to work with in my opinion. And then we're gonna go to content here, and we're just strictly going to paste it in. Now, we can paste in our own headers. So just to be safe, let's look at the request headers that were used. A safe one to use is anytime you see JSON or some sort of application dash, Let's just copy it in just to be safe. And we'll paste that in. I'm going to copy refer, Content-Type and origin. Again, these are just kinda safe to use. A lot of times they're not required, but it's good to use. Now I'm going to copy and user agent. Again. I've tested this before. You don't really need it for this specific website, but some websites do require our user agent. Otherwise, the website can kind of guess that you're a BAD out of the gate. And the last thing we do is we just can hit send. So we're going to hit send and then the website is going to send this URL requests for us. And we can see that we got the JSON response. And we can see that the file size here is about six kilobytes. So great. So we could copy this on our own if we were testing and then use the pretty print to parse this out. Which again we'll go through later on as part of the VBA coding part. So again, if you're not familiar with kinda what JSON looks like, we're going to parse it. I wouldn't get get too worried right now. I just kinda want to show you how you can use this tester. So later on, once we go through the coding sections, you're familiar with this website and then how to kind of update values intestine. So one thing we wanna do is we want to try to do is maybe grab more data than the websites providing. So it looks like here the website unit providing a size of 15, nine. So that means nine results every time you paint the website. And what if we really wanted to grab maybe like 200 at a time to make our lives a little bit easier so we can grab more data. And just so we can parse more data out of time. So let's update this size from nine to 200 and make pretty again here. So it updates here. Let's copy this. And then we're gonna go back to content here, select all, and then paste over it. And then let's hit send. And then we'll probably have to wait a little bit because we're requesting more data. And we can in fact see that we receive more data and more response from the specific API. Using these kinds of mess IT methods you could kinda test updating different values. We can also update the value 15 here if we wanted to test, in my own experience, at least testing this one, I don't believe updating this did anything to the API. It was the first one. But again, these are things you can test on your end as you find different APIs that maybe you want to work with. And you can test different values using this website and see what the different responses are and what kind of headers you need to use in order to get actual valid responses from the website. And especially if you're using more modern APIs that do require a bearer token. Again, you could test here and make sure that you're getting the proper output before you start coding on your end. So I hope this was useful. 36. Download JSON VBA Libraries: In this video, I'm going to walk you through how to download to libraries that we'll need to import into VBA in order to parse JSON. Nice thing about these libraries, is there free and open source? And you just drag them into your VBA code and they should work right out of the box. So what we do is we first of all go to this URL, which I have in the lesson text and there's a link there. Just go through whatever the latest version is and then download this source code. And just it's a zip file. And then we'll go to our second link. And then the same thing, just go to the top. Finally this version and again, just download this zip, ignore everything else. So the files should come in something like this. Let me drag over. So there'll be purely just two zip files. So open up one of them. And then let's go to our code. So at the end you should have something like this. It'll be JSON converter and dictionary. So let me first just, they'll remove the, see what I'm doing. I'm going to remove both of them. So let's go back to our zip file. Let me close this. So once we have our zip file here, click into the folder. And the only thing you need to download for the JSON converter one, the BBA das JSON version. It just this dot BIS. You can ignore completely everything else. All you do is you click on the BAS file and then just drag it over and then let go. And it should insert here as a module. So great, Let's close this one. And let's go to the second one, BBA das dictionary. And pretty much we're just gonna do the same thing here. We're going to double-click. And it's small difference here is this will be a CLS file dictionary dot CLS. Again, ignore everything else. I click it, drag and drop. And the key here is this should be a class module. If it inserts as a module, you might be approaching this incorrectly and just make sure you're following the same exact steps. But if you just have these two, you don't need any references or anything like that. It inserted into your code. Everything should be working in the future here. 37. JSON Data From An API (No Browser): In this lesson, we're going to talk about how to actually ping a URL or an API, getJSON from it, and then parse that JSON so we can pull in the information into our Excel file. So this is just my personal GitHub page. And I just created two different repositories here, for example, JSON, and we'll use XML later on for a different lesson. So you can just click on it. And then the key here is just hit rock. And so I just copied this from the artsy website. And the reason we're not going to interact with its specific APIs, because APIs generally change the endpoint or the URL will change over time, and sometimes the way that the data's structure will change over time. So I just copied an example of some of the JSON and then just posted it here so that way it won't change. So the goal that we're going to do is we want to loop through these different ID, name and counts. For instance, I mean, there's a lot of data that you can test with. But the goal here is essentially we just want to see what kind of galleries, what kind of gallery types and how many counts of each calorie are there. So that'll be our goal here. And we can see that the data is structured first is there's a key name for data and there's more JSON, and then there's an additional key name underneath for category. There's an additional key name and there's a JSON here, sub JSON, I should say, and, and there's a total, it's a numerical value. And there's another key for aggregations. And we have a list. And inside the list is JSON, keyword is counts, and then an additional list. And then we have our ID, name and count. And we can loop through all of these different JSON is in the list. So I'll show you what I mean and how we can parse this. It might not make sense right out the gate. So let's go to our code. And so in our code, I'm not going to talk too much about these. We've seen them before. Option Explicit, we've seen, and then these are some variables that we're going to use. We've talked about turning off, screen updating and enable events, and then turning them back on here. And then we're just releasing some objects from memory at the end. And then this section of code we've talked about before as well, we're just going to look for the last row with data in our JSON summary sheet, which looks like this. We're just going to return the ID, the name, and the count. And so we just want to find the last row of data. If the last row of data is greater than one, in other words, it's greater than the header row. Let's just delete all the data so that we're starting with a blank slate. So the first thing we wanna do is we want to make our row looper equal to two. Because we're going to be looping and the responses. And we want to start at row 2. I'm going to turn post data into an empty string here. And I'm going to leave this here for you. If you ever have data you need to post to a URL, then you can add it here. But in this case we're just going to use a get request because the web page that I have is just going to use a get request. And just to clarify that GitHub page that I showed you, I mean it doesn't have the keyword API in it, but you can really think of it as an API, because in APIs you're pinging a URL and you want to get data from it. So in other words, this could really just be an API. You can think of it like that because this approach is exactly the same for other APIs. Okay? So the next thing we need is we actually need to pull in our search URL. I'm going to copy that in over here. So this is just purely the website I showed you. And we'll use this later on. And then we'll need to create our wind HTTP object, which will allow us to make requests to the URL. So you can copy this here for late binding, but I'll show you early bindings so you can see what the different methods and properties are. So we'll go to References. And then we're going to scroll down to Microsoft. And we're going to look for when HTTP services. We'll click OK. And we're going to change this HTTP object into a new when HTTP and specifically requests. So now we're going to create a width statement, HTTP objects. And then we'll say end web. And what we want to do is we'd want to open. So we're going to create EverQuest. And you'll see that the first argument, let me just type this packet is a method. So the method is post or GET. And then there's a few other types, but we'll just use a GET request. And the second one is the URL. And then the last one, since it's in parentheses, it's an optional value. But if you want to send a asynchronously, then you can do that here, but usually just set this to false. And the reason is because VBA is single threaded and nature. And because it's single-threaded, you have to use different methods in order to really get these HTTP requests to be a syringe synchronously, in my opinion. So we're going to use a get request, and then we're going to use the search URL here. And we'll set this to false. The next thing we need to do is we need to set our timeouts. And that's the reason why I like using when HTTP. There's other ways of sending requests with VBA, but you don't usually allow you to set timeouts. And so that'll look like this and actually type this out so you can see what there is also quite so set timeouts. So this is the result timeout, connection timeout, the setTimeout and the received timeout. I usually just set these all to be the same value and these are milliseconds. So that's why we need to make 20000. And the reason I set them to 20000 is because if a webpage takes longer than about 20 seconds to respond, it usually means there's an error. But by default, I believe the timeouts are somewhere between three to five seconds and sometimes a webpage will just hang or there's a lot of data that it's sunny back to you. So it might just take a little bit of time to get the response anyway, so we don't want to time out too early. The next thing we wanna do is we want to set our request headers. And so that's what we've been talking about before, the different headers. And so you can see the header. We use the header name here and then the header value. So for instance, we can use a user agent. And then the user agent value that we're going to use. I'll just copy over. And you can just copy this from your browser as well. And the key here is if you want to add additional request headers, you don't need to do anything special that you literally just copy and additional requests header and it'll get added to your requests. So additional header and then some value. And I'll just comment this out. And the actual code, I'll give you some examples of common ones that people use like cookies and accepting Content-Type. And then the last thing we need to do is we need to actually send the request. So we're going to use Send and we'll use the body, which is our post-data. But again, post data up here is just an empty string, so it's not going to send anything. And a lot of times if you're using a GET request, you weren't actually send any data. So you'll just send an empty string. But if you need to send a post request, you can. And now what we wanna do is we want to grab the response facts, but then also parse it for JSON. So the way that you get the response is used dot response text and there's other types. You can use, that response, body, response text and stream, but usually I just use response text, and that's the response from your request or the response from the server itself. So that's what this is doing here. It's going to hold it into a string. And now let's interact with our JSON converter. So we're going to set a JSON object is equal to JSON converter. And we're going to parse the JSON here. So we're going to parse our JSON. Essentially a, nothing's actually going to be parsed here. We just want to create an object that holds our JSON and it actually can, we can interact with it as it is JSON. And now here's the key that we want to actually talk about. And how do we actually parse the JSON? How did we look at it? So, like I talked to you about, we have our first key value here is data. So let's, let's try this. The categories is equal to our JSON object. And then the way we use the keys is just like this, these parentheses. And here's the key value. If you have a list, you'll send a numerical value. So it's either one is the first value and then 2, 3, 4, etc, in that list. This is not a zero-based, it's one base, meaning one is one. And then so the next, the next item we saw its category. And then we can check total as well. So for instance, like let's push try this real quick. And if a JSON, if a JSON result here, if the key has an actual result like a numerical value and it's not a list. Or if there's a text value, you don't need to do anything. You don't need to say you want to return the text. Once you get to that key. And if there is a value, it'll actually return the value. So what I mean here is let's message box categories. Actually one small change will need to do is we'll just copy and paste it down here. And the reason is, is we're first going to set our JSON object, which will be at the category level, which is here. Here. And then we actually want to return the key value underneath the JSON that we're Add and it will return our response. So what I mean here is when we run, we see that the result of the totals is 3300 is returned to us. So great. Now what we really wanna do is we want to parse through the rest of the JSON and then actually find those IDs, counts, and names. And so I'm just going to paste this in here and then talk you through it. So first we're going to look for category, then aggregations. And so that's what's happening here. We went to category, now we went to aggregations. But aggregations is actually a list. And so we'll want to actually find the first item in that list, which is the first JSON. And now since we're at the first JSON, we want to go to counts and then counts as again a list. So now we can loop through it. And then so what we can do is for each category in categories. And then next category. And what we can do is we can find not fine, we can actually return the values ourselves. So JSON summary that cell's row looper. And then comma one is equal to category and category ID. And we want to increment our row looper. So every time we're going through a category, we can go through it. And all I'm gonna do is just copy and paste speeds because it's just really going to be the same thing. So we want to grab the name and we want to grab the count. And we just want to update the columns that we're returning our values too. So in column one, we're going to grab the ID because we see here that we are accounts. And then as we loop through every item in the list, they're all JSON objects. And then the JSON objects will have key values of ID, name, and count. So let's give this a try. So we ran our code. Let's go back to our Excel file and we see that the value is actually returned here. So we have our ID, we have our name, we have our count. So this is how you interact with APIs in order to get JSON responses. And then you can parse through that JSON. 38. XML Data From An API (No Browser): In this lesson, we're going to talk about how to grab XML data from an API or URL and then parse that data and grab the data into Excel. So similar to the last lesson with a JSON, here's my GitHub page. And you can find example XML here. And I have a link to it in the course as well. And the key here is just hit rock. The difference between XML and HTML. And the reason why I'm not creating a lesson describing what XML is. Xml and HTML are pretty much the same thing. They have the same look and feel like you can see here, there's a title and an ending title tag. So XML and HTML are the same in that sense that they use starting in any tags. And there might be descriptors to define a little bit more about what that tag is or give it some sort of identifier. They have one difference with XML and HTML. And XML doesn't necessarily have these predefined tags, like an HTML, you might have an LI or like an OL or a p tag for a paragraph. Xml tag names can be anything and they can get better descriptors of what the information is. But in terms of parsing XML and HTML, it's pretty much the same thing. So let's go back to our code. So the goal of our code is we want to grab, I'm going to pull this back over. We want to grab the title artists country company pricing year. As we loop through, these are just CDs from a CD catalog. So the first thing we wanna do, and I'm not going to go over too much is we know Option Explicit. And we know that we're going to turn off our screen updating here. And then these are just some variables that we're going to use. And what I wanna do is I want to get rid of some information here just so that way you guys don't see it right away. And then that way we can talk about it. One thing we're gonna do is we're going to use early bindings so that way we can see how to interact with our when HTTP objects so that it references scroll down to Microsoft. Um, I believe it's when HTTP services here. And we're just going to change this HTTP object to new when HTTP requests. And I won't go over this section of code because you've seen it as well. This is just looking for the last row of data in our XML summary sheet. The last row of data is greater than one, meaning it's greater than the header row. And then we're just going to delete all the data so that we were starting with a blank slate. And then this is what our R She will look like. So we should have a title, artists country company pricing year. So we want to start being able to ping this URL to get information from it. And the first thing we wanna do is let's make rolling or equal to 2, row 2. And we're going to create host data is equal to a blank string. And so like in the last lesson, if you need to add a post data, then you can add it here. But in our case, we're just going to use a get request. So there's not going to be any post-data sent to the URL. Now I'm going to copy into things. So this is the same from the prior lesson. This is how you can leap bind the HTTP object. But in this case we're early binding and we're going to leave behind this HTML file. I won't go too much into this, but essentially we just want to create a local HTML file so that way we can parse the XML and HTML and HTML example. And already gonna do is we're going to set the response from the request is equal to this HTML file and then we can parse through it. So I'll explain that a little bit later. Now what we're going to do is we're going to interact with our HTTP object. So we're gonna add width. And we want to open a request. And the same thing with the JSON example is we have a method which will be our getMethod, but you can also use post URL and then async is optional, which I'll put defaults for now because we're not going to use async requests. We'll use a GET request. The URL is our search URL, which I haven't said yet. And we'll set this to false. First search URL. We also need to set, which is just the web page URL that I showed you earlier. Which is this. So this is just the XML URL. So we're going to call that. The next thing we need to do is we need to set request headers. And so these are the headers that we've seen using the Web Inspector. So these are the response headers and you can set your own. So if you need to write here request headers, and we'll just use that either agent. You need a test and add your own. This is the way you do it. I'm going to copy in the user agent here. And if you want to add additional user agents, you just copy this and you just add more values like this. You don't need to do anything fancy to append user request headers. This is just the way you do it. One thing I haven't added as our mouse, and so we'd like to set timeouts equal to 20 seconds because if the page times out, we just want our, our, our request to time out as well. So and that's and then these are milliseconds. So we're going to use 20000. And we see that there's four arguments. One is the result timeout connection timeout. Time out of your seat, time out. We're just going to set all of them to the same thing, which is 20000. And the reason we use 20 thousand or 20 seconds is I believe the default is somewhere between three to five seconds. And if you have a large request, sometimes it'll take longer than that. So we don't want to just by default timeout after a small amount of time, we went, actually went to wait 20 seconds and then say, Okay, this web page or this URL is really just timing out on us. I'm going to comment out, this is just an example of request header. The next thing we wanna do is we want to send our request. So we're going to send post data. But we saw above, post-data is just an empty string. If you needed to actually use post-data, you would add it here and change this to post right here. And then the next thing we need to do is we need to retrieve the response body. So that we do this right here. Xml response equals dot response body. And her actually we're going to use response text. You can use response body, your stream, you can test on your own. But I just use response text. So this is the response from the URL that we're calling. And then the last thing we need to do is we need to set our HTML file. We're going to set its body and its inner HTML is equal to the XML response. So in other words, we're going to create a local HTML file that we can parse. The next thing we do is we want to start parsing through that HTML with HTML file and width. And using the selectors is pretty much the same as the Internet Explorer example. The only thing is you just don't need to use period document in front of it. So what we wanna do is we want to set our CDS because we created an object above and get elements by tag. And we want to find all the CD elements. So specifically here we have a CDL, CD element, CDL net, and we're going to loop through all those and then grab the sub-elements of title artists, et cetera. So we're going to say for each CD and CDs and then next CD. And we want to increment our row deeper, because every time we go through one of these objects, we want to add to the next row. And we're just going to then add the data into our XML summary sheets. So it's L's ROE looper. And then this will be column one will equal. So we're currently on looping through CD. So we're gonna say CDD and then get elements by tag name. And this one will be titled. And since we're grabbing multiple elements, this is zero-based, so we need to find the first one which is 0, is the same thing as Internet Explorer. And then we'll find the texts. And the one thing I need to add here is change this to tag name. And so we should be able to test this. And we can see that our title here actually returned. So I can show you here, empire realists. Bob Dylan. Ever had your heart, sorry, this is the actual artist title is hide your heart. Greatest hits. And so we can just go on and add more of these. You can just copy them in. So this one will be the part is. And I'll just show you that working again and then on your end. And in the code that I post will be just the remainder of the items that we're going to call it in here. So we can see the artist is now in here. And then the code I post online will have the refs. And you can also test on your end to try different things. So hopefully this is helpful. 39. HTML Data From An API (No Browser): In this lesson, we're going to talk about how to Peng a website and grab its HTML source code and then parse through it. So this is pretty much similar to the Internet Explorer and selenium examples. The only difference is we're going to use HTTP requests and we're going to get the HTML source code directly and then parse through it that way. The differences between the browser approaches you can't interact with the webpage so you can't click on stuff. And so if there's JavaScript that's rendering certain data on the page, then you won't be able to retrieve that data because you're not executing the JavaScript using the HTTP request approach. However, the benefit with using the HTTP request approach is you don't have to all the images and cascading style sheets and things like that onto the webpage. And so that way the code is quite a bit faster. So the example here is going to be pretty much the same as the XML approach in terms of parsing. So this is my personal blog again, and the reason we're using my personal blog is I don't intend to update it. And so they coach should be able to work for years to come. We're going to right-click and hit Inspect. And the goal here is the same as the browser approach is. We're going to loop through all the posts here and then grab the post name and the post hyperlink reference into Excel. So what we wanna do is go to the elements section. We're going to highlight the very first post. I'm going to close this LI tag. And as we remember what the HTML example, this is ordered lists because it's 123 and so on. We see there's a class name of posts. And we saw in the Internet Explorer example that there's only one post class name. So we can use that as our selector. Then we want to loop through the LI tags underneath. And we can look through and see what's inside of them. And we see that there's an, a tag inside each L LI tag. And then there's our hyperlink reference which we'll want to grab, and then the post name. So I won't go through the coding example in the same way as the prior lessons because the base code is really the same as the XML approach. We have our search URL here, which is the blog. I mean, we have rho looper post-data and all the other stuff that we've seen before. This section, again is just going to find the last row of data in the sheet. And if it's greater than one, or if it's greater than the header row, if the data is greater than row 1. And we're gonna delete those values. In this section, we're going to start using our wind HTTP object. We're going to set timeouts equal to 20000. And this is in milliseconds. So that way if the page takes longer than 20 seconds to load or to retrieve a response, then we're going to have a timeout. We're going to use a get request. And we're gonna go use the search URL, which again we showed up here. We're going to set a request header using user agents. And then we're going to pretend like we're using a Chrome browser. At least that's what we're going to tell the server. Then these are some additional request headers that you can use. Then we're going to send our request using post-data. And we showed before, post data is just going to be an empty string. But if you need to add a post data, if you're going to send a post request. And in other words, if you're going to update this GET request to a post request, you can send it that way. In this section, we're just going to grab the response text from the server or the response from the API into a string variable. And then we're going to set our HTML files in our HTML and the body of it equal to the response text. So in other words, we can use this HTML file, which is created up here. Again, this is same thing as the XML approach, and we can start parsing through it. So we're going to use with HTML and very similar to the internet Explorer example and the XML approach, we're going to use getElementsByClassName. The only difference between this and the internet Explorer examples, we don't need to use this document because we don't have a web page document. We're just parsing the HTML correctly, but the selectors are exactly the same. So we have getElementsByClassName. And we said we're going to grab the post class name. And with returning multiple objects or elements, I should say. And so we want to grab the first one, which is a zero-based. So 0 is 1 and 1 is 2 and so on. And then underneath those posts are underneath this HTML element that we just HTML element that we just got. We want to grab all the LI tags here. And then we went to loop through all of those LI tags. And along the way, as we loop through each LI tag here, we're going to use a width statement. And then using our HTML summary sheet, we're going to enter the value into our row looper, which we set to starting as two column one or column a. We're going to use the LI element that we're currently on with our width statement. We're going to grab elements by tag name. We're going to grab that first a tag here. And then we're going to grab the inner text. And we're going to grab the hyperlink reference. And as we talked about before in the Internet Explorer example, the hyperlink references don't come in with the domain name. They come in with this about text here with a colon. So we're just going to replace that about here with the colon, with the domain of the blog. And then we're going to increment our row loop group here. So let's try to run this and the code. So he ran a lot faster than the internet Explorer example. And we grabbed our post names and our post hyperlinked references. So again, this is more or less the same approach as the XML approach. But you can see it's quite a bit faster than if you're trying to use a browser. I hope that's helpful. 40. HTTP Cookies From A Website: In this video, we're gonna talk about how to grab cookies from a website. And the reason you want to grab cookies is if you have a login page, after you log in, a lot of times the website will place a cookie onto your, your computer. And then every time you search pages on that website, your browser will automatically grab that cookie and authenticate yourself as being logged in. And then if you remove that cookie from your your computer and I'm sure you've seen it before. If you've cleaned your cash and cookies, you'll be logged out of that website. And so it's useful to be able to grab cookies with your HTTP requests. So that way you can pretend like you're logged in. So you can authenticate yourself, grabbed a cookie, and then actually browse the website using HTTP requests. So let's begin. So I'm going to right-click and hit inspect my go to the Network tab. And I'll just get rid of this. This is from a prior lesson. And I'm going to go just to my GitHub page. And to show you what I mean is if I click on this top URL and we expand it, we can see that there should be these set cookies. These are different cookies. And specifically in this case there was two sets. So there's this one and this one. And you can see with my requests, these request headers here. There was a cookie sent as well, and this is to show that I'm authenticated into my account. So we might want to grab these cookies if we're logging into a website. So I won't go too much into the coding section of this because a lot of it is just repeated from prior lessons with XML, JSON, and HTML. In terms of the setup, all we're gonna do is we're going to hang a website. But the difference here, so this is our HTTP request objects we've seen before. I post data, we're just gonna make empty. This is our search URL, which is the website that I just showed you. And then these are our variables and then we've seen the rest of this stuff here. Also, we've seen this stuff here. We're just going to create a HTTP requests. We have our timeouts here. We're going to use a get request going into that search URL that we had talked about. And then these are our request headers. So after we send our request here, the difference here, instead of getting the response texts, we actually want to get all response headers. And I'm going to come out a few of the sections real quick. And what we can do is we can go to View and media window. And then if we run this, I'm just going to show you on the screen with the headers look like. And then we can use this, this procedure here or this call here to get the response header by its key name, which is Set-Cookie. And I'll show you the problem with using this approach, but it's still helpful to know. So we're going through, so we pick the URL and here's all of our response headers. Here, I'm going to expand this so you can see it. So we have our response headers and a lot of this looks like what we just looked at on the screen, on the browser. And we can see there's three set cookies here. And the problem is, is in our code, we wanted to get the response header set cookie here, and then we're debugged, got 29 here. Now the problem we see the response cookie that we pulled is just this first one here. And in some cases you want to grab all the cookies. So this one and this one as well. And I'll scroll to the end so that way you can see that what I'm saying is true here. So CAA just ends. Now what I've done in this code is I created a custom function or you wish, if you need to use, you can use later on. What we're gonna do is we're going to check for specific headers based on the key name of the header. So Set-Cookie, we're going to pass all the headers through it. And what we're gonna do is we're going to loop line by line looking for the header values. If the header value has a match, we're going to append it to a string, and then we're going to return that string is our cookie. So this is the function down here. This check for header. We're going to send over headers as a string and the header that we want to check for as a string as well. These are just some variables we're going to work with. Specifically, we're going to split the headers based on this VB LF, which is built in to VBA. Vba ALF just means for line feed or line breaks. We're going to start with our result that we're going to send back as part of this function as a blank string. And then we're gonna go loop through the headers. So we're essentially going line by line. We're going to double-check that if the header has a colon. So essentially if it's not an empty line or something like that, we just want to make sure there's an actual colon. Because as you can see down here, the header value separated by a colon. And we're gonna use the split function to split the text by the colon. So the first value is the header value, and then the second value is the actual value of the header. So this is the header name and the header value. We're going to use lowercase or L case to make the value for the header named lower. And we're going to use trim to get rid of leading and proceeding spaces. And we're going to do the same thing for the header that we're going to check. So essentially we just don't want to make this case sensitive. So that way in case you submitted this is all uppercase values for your header to check. And the header value here has uppercase, uppercase, and the rest is lowercase. We just don't wanna have to worry about cases, so we're just going to make both lowercase. And if the value is the same for the header that you're trying to check for. We'll see here that we're going to append your result is equal to your result. And then we're going to concatenate the left of the header value, length of the header value minus one. And then we're going to append a, a semicolon here. And the reason is, is most cookies are separated by semi semi-colons here, as we can see, at the very end, after we're done looping the headers, we're going to return the result. So let's see what that looks like. I'm going to delete the text here. I'm gonna go back up here. I'm going to run our code. And now I'm going to show you the difference of the result from the cookie response from just using the built-in when HTTP function get response header versus just this function. And what we can see is this is the headers from the website itself. This is the response using the get response header we're using when HTTP. We can see it ends here. Well, we can see in our second line here using that special function, we can see the octo here, which is that second cookie that we saw. I'm going to try go to the front. This is that octo, and then it'll probably be hard for me to show you right here, logged in equals now, right here is that third cookie, which is right here. So they're all appended together in case you needed them. So these are two different ways. You can grab cookies and then you can create additional requests afterwards and then use this set request header cookie, and that's at your value here. And then that'll help you be able to go through websites where maybe a login prompt is required. So I hope this was helpful. 41. VBA With Multiple Excel Files: In this lesson, we're going to interact with two workbooks. And specifically we want to loop through a second workbook through its worksheet and then highlight cells. So this is going to be very similar to our very first coding exercise. The only difference is the code that will be in one workbook and the other workbook is the one that we want to interact with. So what I mean is we have our first book, your workbook, I should say. We have some information and then we'll have our second workbook which has different information. And the reason I have information there is just so they can see one is being interacted with and not the other one worked with that is that it's being interacted with. So I won't go over these sections because we've talked about them before. The one difference here is we're going to set display alerts equals false. And that just means that Excel won't give us prompts at the very end, specifically, we're going to close the second workbook. And if we don't set display Alert equals false, then we'll get a prompt to make sure we want to close it or something to that effect. So we just don't want to have to have that pop up. What we wanna do is we want to set our row loop is equal to one because we're gonna start on the first row. And we have the variable declared up here. And we want to set a new workbook equals workbooks app open. And what I haven't said so far is a filename. So that's what we're gonna do now. So we're gonna say filename is equal to the location of that file. So you can see that's the second workbook here. And we're going to open up that workbook here. So workbooks had open the filename. And what we wanna do is we want to set our second workbook. She is equal to give worksheet problem code name. And we're going to pass workbook and the second summary sheet as the toning. So this is going to be a custom function I'll show you now. And we can see in our second workbook, the code name is second summary sheet. So that's why we're using second summary sheet here. And we're going to pass the second workbook into this function. And it looks like I might have mistyped here. So I'm just going to copy and paste. So the issue with interacting with at different workbooks is the code names are no longer accessible once you open up that other workbook. So code names are only accessible inside of the worksheet tab you're working in. And I should say workbook you're working in. And so if you're trying to open up a second workbook, you can't access its code names directly. So all we're gonna do is we're going to make a quick function here that's going to take the workbook that we're looking at, which will be our second workbook. And we're going to look for the code name and we're going to return that worksheet. So we're just going to create a worksheet variable. And we're going to loop through all the worksheets in that second workbook. And then we're going to check the worksheets, code names as we look through them. And we're going to see if the code name is equal to the code name we're looking for. And if it is, we're going to return that worksheet and we're going to exit loop here. That's pretty much all we're doing. There's other ways you can look for worksheets. You can look by worksheets by name, but I don't suggest that approach because you might have multiple workbooks open and the worksheet names might be the same. So now we have the worksheet, and this is really the same exercise as our first coding exercise. We're just going to loop through the rows and then highlight them if they're equal to certain value. So with again workbook, and then we'll end with and we want to reset the column colors. So let's say columns a. And I'm going to say the interior color equals exile none. So again, this is pretty much the same idea as our first coding exercise. We want to reset the interior color of the column to exile nine, which is a built-in PBA terminology, which just means they won't be a background. They'll pillar. And then which can use a do-while loop, do-while. Fat cells row loop one, and make sure that the value is not equal to an empty value. And we're going to loop. And we want to make sure we're incrementing our little loop. And then we're going to use our width, our if statement here, Castells row loop. And actually I'll just copy this so it's easier to type in. So we're going to check if the value is equal to Steve, which is the same time that we did in our first exercise, will use the if statement and then what we're going to end our if statement. And so if the value is equal to Steve, we're just going to change the interior color. Is equal to I don't believe this at the same line as we used in the original coding exercise, but this will highlight the cell to a slightly different color. I believe it's a green color here. So let's give this a try. So we ran our code and we see our second workbook did in fact highlight all this d values. And so we can just test here what if we add a new Steve? We just wanna make sure the code is actually working. So we're going to run it. And our new city value is highlighted. And we can check our first workbook. And we can see our first workbook has not been updated at all. So that's kind of our goal here is just to interact with that second workbook, using our first workbook. And we could instead of updating the interior color, maybe we wanted to use a value and our first workbook and then add it to the second one or do some sort of formatting or something to that effect. But in any event, this is how you interact with other workbooks. So I hope this is useful. 42. Loop Through Files In A Folder (Two Techniques): In this lesson, we're going to show how to loop through files in a folder. And then specifically we're just going to rename the extensions of the files, but it'll show you how to loop through files. And then if you want to, you can open the files with your code. For instance, in the last example we showed how to interact with multiple Excel files. And then we'll have a PDF example, which is bonus material, which you could also theoretically looked through PDFs, open them up and then do whatever you want with your script essentially. So we're going to show two examples of how to loop through files in a folder. So the first one is we can use late binding or early binding, but we can use the file system objects. So just to show you what the file system object does and its methods and properties, we'll go to our references and we'll go to Microsoft scripting runtime, I believe. Yeah, right here. And you can change this object to New File System objects. But if you want to leave behind, you can just paste this. So it's scripting file system object. And we're going to set our folder is equal to episode objects. Back to that folder. Not absolute path. Well there, and I haven't said my folder yet. The folder that we want to interact with is right here. So this is the folder where I have my saved PDF files. And these are the Save PDF files that we created as part of the template creations and then we automated the templates and we saved them. So I'm going to look through all the PDFs there and then change the extensions from PDF to TXT. So we'll say we want to get that folder. And then simply we want to go for each file or each piles in Boulder files and the next files. And we want to show what the filenames are onto the screen. So if I was taught name, and then we want to check if the rates. And we're also going to lowercase if domains, but we're going to check the right side of the files, files that name. We're going to lowercase it so we don't have to worry about the uppercase or lowercase or case sensitivity management the last four. And we're going to check if it's equal to dot PDF. So if it's a PDF file, then we want to interact with it. And we'll use an else if. And then we'll end. And then this one is we're just going to change any TXT files back to PDF files. So we're going to check to see if it's a TXT, and that will just change it back. So if the right or the filename as PDF, we're going to rename it or use the name function. Well, their path and the files dot name. And make sure your folder path has an ending backslash. If it doesn't, then you just need to append it. So we're going to name files dot name, and then we're going to use ads. So that's how you can rename as you use name, the folder, the full, full folder and file path to that file and then use as. And I need to use an underscore here so we can go to the next line. And then we're going to name it to the same folder path, but we're going to rename it. So we're going to grab the left characters of the file. And we're going to look at how long the file is by using the Eliane drilling. And we're going to just get rid of the last four characters. And then we're going to change it to EXT by just appending txt. So if it's a PDF, we're going to make it a TXT, and then we can just copy and paste. And we're going to change this to PDF itself as a TXT. We're gonna make it a PDF. So let me pull it over my folder here for you. So these are the files here is your PDFs. Let's try to give this a run. And we can see all the files are renamed to TXT. And it's very quick. Also we can look at the files that we were able to find, so we can see all the files were in fact found. Now let's try to run this again and see if we rename everything back to the PDF. So we found all the TXT files here. And we in fact renamed the impact of PDF. So this is the first approach of looping through files in a folder. The other one is using a process that's already built into VBA using DIR. So what we can do is we can look for the file name and it's equal to DIR. And we just go directly to the folder path. You don't need to use any sort of attributes. So what's happening here is we're going to grab the first filename in this folder and then we want to loop through it. So we use do-while LEN filename is greater than 0. Then we're going to loop. And every time you look through the file, you'd have to just reuse DIR. So name equals t. So every time you through DIR is, is gonna give you the next file. And we're just really going to do the same thing as the I-System object. So I'm just going to copy and paste the code. So we're essentially just going to look to see if the last four characters or PDF or TXT and rename the file. And then we just need to copy and paste the changes here. So instead of files dot name, we're already getting the filename directly. I'm just going to change all of that here. Files dot name, dot name, and then so on and so forth. And then there's a good chance I probably missed one that I wasn't thinking about, but let's give it a try. Yeah. Exactly. All right. So let's give it a okay. I think that's Last one. All right. Let's give it a tract. And then I misspelled that one. And then we shall they have it go. Alright, well we can see that I worked here, so we have the TXT here. And then if we view the immediate window, I didn't clear from before, but it did in fact find all the PDFs, which are these ones here. This is from the prior run. So let's delete this loop again. So we didn't in fact find all the TXT and we did rename them back. So in my experience, using DIR is a little bit faster than filesystem object. And it's a lot easier because DIR is already built into yay, so you don't need to early bind early. So I would suggest to use DIR for your purposes. 43. Find Word(s) In A PDF File: So in this video, we're going to talk about how to interact with PDF files using Excel VBA. And as noted in the lesson text and throughout the course. This is bonus material because you do need Adobe Acrobat, not just Adobe Reader in order to interact with PDFs using Excel VBA. But I still would recommend that you go through the lesson so that way you know and understand what you can do using Excel VBA it maybe later on you'll decide to purchase it or maybe an employer will purchase it for you if, if you need it. So in this example, what we're gonna do is we're going to loop through texts and PDF files. And specifically we're going to look for an employee ID or unique ID and the text. And then we're gonna resave the files using that ID. And so this can be useful if maybe you have a document portal, I can split out documents using a unique ID and maybe attach it to an investor or an employee. And I use this in my day-to-day work. So what I did is I just added this employee ID into all the PDFs here at the bottom. And so it's really just using the same code that we used earlier in the course. All I did was updated the template to pull it into employee ID and just formatted it into four digits. So I'm just kinda talk about the code just so you know what it does and then you can test it on your end. So this section of code we've already talked about Option Explicit screen updating and we're going to turn to false. So this object here is how you interact with the PDF here. So we're going to create it. This is the folder where we're going to look for the PDF files and using what we learned in the last lesson, we're going to use the DIR function that's built into VBA to loop through the filenames and that folder. And so again, we're going to say Do While the length of that filename is greater than 0, meaning that there's files stolen the folder. We're going to check the extension of that folder by checking the last four digits using the right function to find if it's a PDF file and we're going to lowercase it so that way we don't have to worry about case sensitivity. So we're going to use our PDF objects. We're going to open the folder path and the filename and that folder path. And we're going to get the number of pages in that PDF, which is just built in. It's a built-in function to this object. And the reason we want to get the number of pages is we're going to loop through the pages so we can look at the text inside of it. So we're also going to create this other object that will allow us to interact with some JavaScript. So JS object. And now we're going to loop through the pages. It's 0 based. So we're going to start with the 0 page or the first page. And we're going to look through all the numbers of the pages and we subtract one because again, we're zero-based here. And then for each page, we're going to get the number of words in that page. And then we're going to loop through every word in that page. We're going to use that JavaScript objects. We're going to get the nth page here, and we're going to grab the numerical order of that word. So the first word, second word, third word, et cetera. Then we're going to use trim just to get rid of any extra spaces at the beginning or the end. And we're going to show what those words are on the page. So you can use up to this step to test what are the words are in the PDF. And unfortunately, the way that Adobe Acrobat reads words in a PDF isn't in a chronological order. Or the way that you kind of see up to down and left to right. It's kinda random. So you need to test to figure out which word you need to look for before you can find your unique identifiers. So in our case, we're going to use the STR function. We've talked about this before. We're gonna look at the word and we're going to look at the keyword employee ID. And this is for my testing. So the words are going to be found one by one. And once we find the employee ID word than the word after it is the unique identifier. So that's exactly what we're doing here. So we're going to grab the employee ID by getting the nth word in the nth page that we're on. And instead of looking at the current word, we're going to look at one word subsequent to this are plus one. And then here we're going to trim the world. So we're going to get rid of extra spaces, and now we're just going to make a file copy. So this is similar to using name as what the difference is. We're not going to rename the file, we're just going to create a copy. So file copy is built-in function to VBA, so you just use file copy. You go to your filename and you need the full filename and the folder path. And then you just use a comma. And then the file copy name that you want is the second argument. So we're gonna save in the same folder, you have a sub folder by ID. And then we're going to say by ID. And then we're just going to keep looping through the next word, the next page. At the end, we're going to close the PDF file. And then we're going to look to the next file. That's very much what the code is doing. And so that's what we can see. We're going to look at the immediate window as we run this code. And we'll see that all the words are found in those PDFs. And then you can look one-by-one. How are the words found? How are they being looked at by the Adobe Acrobat? And so we can see here employee ID is the keyword that we're looking for because this zeros 0000 three was tens employee ID. And so we wanted to find Employee ID. Once we found that, we could say that the next word was was a unique employee ID. And also what we need to do is double-check. So we looked to see if we went through all of these PDFs. And now we need to check that by ID folder. And in fact, all of the employees are renamed based on their employee ID. And we can double-check it for you. So this is in fact, let me zoom in real quick as 0013 zeros, I mean, and then one. And then this is two. And then let's try and get like number six. So in fact, that is what happened. So this is an example of how you can loop through words in a PDF file. And then you can grab texts from, and then you can do what you want. So if you wanted to put those text items or whenever the text you found into the Excel file, you could do that. You want to rename the file like I did in my case. You could do that. And so in a real-world example, we grab the documents based on an ID, and then we create a zip folder. And then our document portal can break out unique identifiers based on investor names or something like that. And so this is a real-world example of something you might use. 44. Merge PDFs Via VBA: In this lesson, we're going to talk about how to merge PDFs using Excel VBA. So in the last lesson, what we did is we took these PDFs. We found a, an employee ID and then renamed each of these PDFs based on that employee ID. And now what we're gonna do is we want to loop through all the PDFs here and then merge them together. And so actually you can do that in Adobe Acrobat. You can select all control, a right-click and then combine. The problem is, is if you have a bunch of files, maybe you say over 25 or 30, Adobe Acrobat won't combine them for you anymore. So this example will show you if you want to combine many, many files. And more importantly, if you have a larger process, maybe you want to extract text from PDF and then maybe merge PDFs at the end. Instead of having a manual step of having to select all and then right-click and then have Adobe Acrobat do the process for you. You could add this as a step in a larger process programmatically. So let's go through the code. And so we saw this in the last code. We're creating our acro PDD doc. So this is how we're going to interact with the PDF file. This information up here we've seen before, just declaring some variables and turn off our screen updating. And with slightly different about this one, this code then the last code is we're going to create a second PDF. And this will be the PDF or we're going to merge. So we're going to create a blank PDF and then merge in our PDF files that we're going to loop through. We're going to start at page 0 because our emerged PDF is 0 pages in length, or essentially the first page because it is zero-based. We're going to create that merged PDF. So we have a PDF created, it's a blank PDF. This is the folder where we're going to loop through all the PDFs. So this is that by ID folder. And then we saw in the lesson looping through files. We're going to use the DIR to loop through files in the folder path. And we're going to check if the length of the filename is greater than 0, which means there's still files in the folder to look through. And then similar to the last example, we just want to check if the lowercase version of the file name, because we don't want to worry about uppercases or lowercases. Essentially, we don't have to worry about case sensitivity. We're going to look at the LUT, right, for strings, which is right here. And we're going to check if it's a PDF. And we also, since we are going to save them merged PDF in the same folder, we just want to make sure we're not going to interact with emerge PDF file again. And then similar to the last example, we're going to open the PDFs that we're looping through. We're gonna, we're gonna get the number of pages. And in this example, we're going to show that the number of pages on the screen, but you don't have to have this. And right here is this code is what's going to merge or insert the PDF pages into our emerged PDF. So we start with negative one. We add our starting page. And so in this example, I believe in CR pages minus1 is that very first page. And then we will append the starting page based on the pages in the PDF file that we just went through. So in other words, if the file that we just opened up is one page in length, we'll just add one page. If it's five pages will have five pages. And that way we're just always adding or inserting into the end of our merge. So we're going to use our original PDFs that we're looping through is the one that we're merging in. And we're going to emerge in the number of pages of that PDF file. And this one specifically is not 0 based. So you can leave the number of pages, the PDF file you're looping through. Same. And then we're going to close our PDFs as we loop through them. And we're going to loop through our files. At the end, we're going to save our merged PDF in the folder path. And this is the file name we're going to use, and then we're going to close it. So let's give this a try. We're going to view the media window. This is some text from the prior example. So I'm going to delete that and then I'm going to run this. And we can see all the files here. I one page in length and are merged. Pdf should be starting from person 1 and person to person three, and then so on, so forth. So I'm going to delete this and I'm going to show an example. What if the second person had two files or two pages, I should say? So I'm just going to insert one of these PDFs into here. So we can see number 5 is starting before too. And I'm going to save this just to show that it is in fact two pages in length. And we're going to delete this here. We're going to run again. And we can see our code did pick up that the second file I found is in fact two pages in length and then are merged. Pdf should start with number one. And then it should be number five, which is in fact true. And number two, because we inserted the number 5, number 2, and then it should just be three or five and so on. So I just wanted to show you the code does in fact insert if you have multiple pages and your PDFs and in fact we'll merge them together. So that's how you can merge PDFs here. You can look through them and then merge them into a new PDF. I hope that's helpful. 45. Automate Fillable PDFs: In this video, we're gonna talk about how to automate PDF form filenames. And so this might be one of the more interesting things for you to learn. So in this example, we're going to automate form filling 1099 form. If you don't know what a 1099 form, that's totally fine. It's just a tax form. So I downloaded this from the IRS website and there's many copies. There's copy a here which you can see, and then there's a happy bee or copy one copy be copy C or copy to see, and then so on and so forth. In our example, we're just going to work with copy V just to make things easier. And we can see there's a bunch of fillable PDF forms here. And I just extracted this PDF specifically copy be out on its own. So here. And what I wanted to show you is you can find a PDF form names by going to shimmy edits were actually tools for sorry. And then we go to Forms. And then we can prepare forearm or create forum. I believe it's Create form. And then it will show something like this where you can select the file or scan it. So I'll just press start and then you'll see the names of the form fields here. And if you have a PDF that doesn't have forms, but it is some sort of nice structured PDF. Sometimes the PDF file will automatically detect the form fields for you. Or if you want, you can just insert them yourselves, like up here. You can insert your own PDFs. And so I had already updated as address name to make it easier because the default names are a little bit long and not easy to see or read. So specifically what we're going to work with is the address name, Redbox and this corrected. And all we have to do is just look at right-click and then go to Properties. And then properties, we can just see the name here. So we're going to grab the names and that's how we're going to interact with the PDFs. So I'm going to hide the PDF now. And this is just an easy made up example. This is going to be the value in box one. And this will be the value and the, excuse me, the, the payers name and the address. So Tim C and some made-up address. So we're gonna go to our code and we'll go to fillable forms. So all of this stuff we've seen in prior examples, we're just declaring variables and we're going to turn off screen updating. And this is how we are going to interact with the PDF file. We're going to create an object or acro Expedia doc. So we've seen this in the prior example. This is our folder path where the PDF the 1099 PDF exists. And this is the name of the PDF file. So we've seen this before as well. We're going to open up a PDF file, the 1099, and we're going to create an object so we can interact with the JavaScript. And really all you have to do is with this JavaScript object is used the key phrase getfield and of course the period here. And you're just looking based on the fields name because they're unique. And you can set the value by saying dot value and it's equal to. And then we're using the summary sheet and we're saying range a2 is the value of the address name. Rent box is the value of range B2. And I would suggest that you in case every value is CFTR. Cftr is just built-in VBA function that it just ensures all of the values you're passing in are going to be converted to strings. So even if you think they are string, sometimes they aren't and the PDF file will give you an error. So it's just a nice precaution. But one thing I would point out with the corrected box, these checkboxes, the value two is true or checked, and then everything else, at least in my tests, 0 and 1, or things like that. False. So you can use to for true and then put 001 if you want to uncheck. And then the last thing we're gonna do is we're just going to save a copy in the same folder path or we're just going to call it form filled 1099. So I'm going to close this file. And let's give this a shot. So we're going to run it our codes around. And then we have this form filled 1099 copy B. And we can see as we zoom in that in fact, let's go to the corrective box so we can see the corrective box in fact became corrected. So it's checked. And we have this address field and it's formatted with our line breaks, 10 C address city in California. And then our rent box does have a 1000. And if you wanted to, you can easily add onto the code by adding values into royalties, your other income, pair 10, and stuff like that. And with everything else we've learned throughout the course, you could use loops. If you wanted to loop through many rows and create different copies, and then rename the copies based on a person's name or maybe a unique identifier, and then you can automate form filling pretty easily. So I hope that's helpful.