Have Excel automate that through VBA to automate filling out that PDF and we do have a PDF form available so that we're going to be using that and we have it here right in here and now what we can do is that what we're going to do is we're going to automate filling in the information of using this PDF form using the last name first name address to the state and the fields and then what we're.
Going to do is we're going to have it save that in a folder and now we can also have them print it out but in this case we're going to save it individual each one with their own individual and distinct name so let's get started first what we're going to do is we're going to add some buttons up here and you'll see I've pre-populated the table with data and we also have a PDF template and what we want to do is we want to know what template we're going to start out with.
What is the PDF template that we're going to use and so this gives users the ability to change the template should so we'll go ahead and enter some buttons here and we'll go to what we want to do is we want to locate that okay so we'll go ahead and enter a browse selected and that's going to help us locate where the PDF form is and I've already written the macaron that in just a very quick math row so I'll go ahead and show that to you and first we'll go ahead and format.
The button to see how we like it and let's go ahead and give this blue color and so basically in excel vba and we can do it alt f11 to get to it or you can use once again the file options and go to customize ribbon if you don't have the Developer tab you want to add it and if you do have it simply click on developer and then visual basic or alt f11 okay and here what I've got two macros that we have and basically one is a PDF template this is going to allow us to browse for a specific file and the.
Other is going to allow us to browse through a specific folder so they're very simple macros that have written that allow us to do that since this is not really part of what I want to teach I've gone ahead and included these macros here so even have to worry about that okay so the idea is that we want to browse for that PDF template so let's go ahead and assign one of those macros so we'll go ahead and and add in that macro and it's the PDF template here okay and we'll click OK on that and then now.
We'll go ahead and copy that button we want to also add in a PDF saved folders here so we'll go ahead and define this called it saved saved PDF okay because we want a folder to contain all of the PDFs once they get completed formatted alright now that we created the button I'll go ahead and we'll go ahead and move it up up here and so you can see the options so when we right-click it we can define the macro and we're going to assign the macro that.
We fit a PDF template okay and then back down here we'll just go ahead and duplicate that button control D and that'll duplicate it and we're going to use now for our second one and this is where our saved PDFs are going to go into okay this is the folder in which our safe PDFs are going to go so we want to assign a specific folder for that okay so let's go ahead and right-click that and that you can see going to pull it up you can see we're going to site a macro to that and that's.
The saved PDF folder those are those two macros that I've created already for you and we'll go ahead and bring that back down and now when we click on for the PDF template it's going to basically ask us what we want to create and so we want to search for that PDF template and I've created one here in the patient intake form so we'll go ahead and add that in and then we want to have the folder in which we are going to have those saved PDFs and we can put it here so I've created two folders and so all the safe.
PDFs are going to go into this folder and the template in which we're going to use is this alright so that covers us with you and now we have to add one more button and that's going to be for our automation and that's when we want to basically put all the names in the PDFs so we'll go ahead and duplicate this button again and then we'll bring this button up to the top and give this button a little bit bigger and so what we want to do is we want to here create.
Patient intake okay so we need that button to actually run the entire macro and we'll go ahead and increase the font and change it a little bit to give it some style and so these are going to create the patient intake intake forms okay and this will be the macro that we use now we haven't created this macro yet and we're going to we're going to walk through that right now so why don't you we'll go back into the VBA alt f11.
And here's where we're going to create the VBA so we can do sub
Create PDF forms okay and we're going to start off basically we want to add dimensions to that and then basically what that means is we want to define certain variables that we're going to use and so to do that we can use dim or short which is short for dimension and let's go PDF template file okay and.That's going to be the actual template file we also want to define a new PDF name because each one we're going to be assigning a new name so we want to define that okay we also want to define the say folder the folder in which we're going to be using we want to define that so we'll add that to that save PDF folder okay and also since we're going to be using we're going to be using saving specific names for the each PDF we want to define certain aspects last.
Name okay so we'll go ahead and add in last name because we're going to use that as part of the file name and all of these are as string these are all string valuables okay we have others okay so and then also we want to use the appointment the appointment date as part of our filename as well so we I want to use when we create these new PDFs I want to use the patient's last name and I want to use the date okay so when I use both of those as part of the name so we're going to define the appointment date and as a date file okay and then we.
Are going to do we have we have to track when we go through when we go through this list right one by one by one we have to track what row we're on and we also want to know what last may be you have a list of names of 100 maybe F 30 so we have to find out which is the last row so we have to define that too so both the row that we're on and the last row and those are long values so we can say as long so we will say in a customer row we'll just call that the row that we're currently on and then last row and.
Those are long which means the whole number as long that's a whole number okay so basically that helps us to find it and next we want to define the sheet okay all of our work is going to be done on sheet 1 so we can start with with sheet 1 okay and that helps us so we don't have to keep writing sheet 1 sheet 1 CH 1 and we do need to tend with here ok so that's a good start to our macro okay next up now we want to actually define the last row we want to know what is the last row of the data in this case it's row 14 but it may be something different.
Okay and we're going to use E as our column to define it because these values are in s okay so there's nothing in E other than the data so we're going to use that as our column to define it to do that we just simply has to write some room we've already defined last row previously as well as a whole number B equals okay and then we can put dot range all right and then remember it's row e ok e e and.
Then let's just go something long which would be the maximum less than 999 dot and Excel Excel up dot row and what that's going to do is that's going to give us our last row and I'm just going to put some comments in here anything after the apostrophe the comments and we'll turn green on my screen and that helps us see what we're doing I always like to comment it's always a good idea when you're programming to add those comments in here throughout the coding process so that you can always know.
Where you're at and what lines don't especially when you revisit an application after a long time it really helps you do that okay the next thing we want to do is we want to define our PDF template file and if you remember that is in row G 18 okay so we have to define that okay so we'll go ahead and give us some space here we want to say PDF template file and you see how that F is lower taste right if if I do it lower.
Case and it changes automatically that means I know I have the right value means I didn't mess up the spelling because it changes automatically based on what I've written here so it's kind of a nice way sometimes I do all lowercase and then I just hit the next line and if things change then I know yeah I didn't misspell it so that that's a little helpful trick there okay so PDF stem file equals and then dot range we don't need sheet one because we've already defined sheet 1 up here so we know it's with sheet 1 okay and it is going to be G 18 G 18 okay dot.
Value and then once again template file name I'm just adding that comment in
Okay we also want to define our saved folder remember that's in G 20 so once again our space PD PDF folder equals dot range and then G 20 okay dot value and that's our PDF A's PDF folder okay so that's the folder in which our PDFs are.Going to go and once they get completed okay and you see everything changed upper case here so that's good we know we're on it and now what we want to do is we want to open that PDF we want to open up the PDF so let's go ahead and do that we can do that with this code we can call it open URL and then we're going to add a double quotes so that it knows is text and the PDF template file.
Through but basically all we're doing is we're telling Excel to open this file okay open template file and then we're going to put some double quotes and that just helps it know that it's a text that starts and ends with text so that it treats it as text and then we want to open it just show a maximize okay we want to open that update PDF all right so that's been open it up so we need to open if we're going to be working on it and another thing is one of the things that we're using throughout this code is.
That is a code called weight or application weight now if we do things too quick in this code they won't catch up if we start typing and we're already coding onto the next thing without waiting for it to fin typing it's going to get messed up so what we want to do is we wanna we want to add in some code to just basically pause the code and allow that allow this PDF to open up completely before we can actually start filling it in so we need to give it some time and keep this in mind every computer every application is.
Different so this is something you're gonna have to test out on your own for me in my previous test this about 6 6 seconds or 6 tenths of a second their application or 6100 dot wait okay it's not watches wait okay and then basically now the current time plus and then we're going to add in a decimal and then four zeros four and then a six my test about about six it was about six seconds for it to completely open it and we're.
Giving it lots of time so that make sure it can open up completely okay so now our file is open okay and why don't we just go ahead and run this and see if it's working okay we can run it just as is alright let's go ahead and write and by clicking run okay and we're giving it a six seconds and it's open okay great so it works fine all right so our PDF opened and everything looks great okay so we're going to close it for now and now the next step is basically what we want to do is we want to we want.
Excel to go through every single row and as all the details add those last name the first name the address city state zip code email and home phone we want to dab all those fields for each row so we have to define we have to have it loop okay so we have to set up a loop so we're going to set up a loop called for next and we're going to do for customer row customer row is the row that it's on okay so it's going to start out at five okay because our first if you look our.
First names on five that's not going to change okay and then equals five to last row and if you remember we've defined last row at the last row of data and this is going to help us define that okay last row okay and then we're going to close our loop with a next customers oh so that means everything is going to be written in here all of our code for each line is going to be written in here okay and actually for our purposes what we want to temporarily we don't want to go to the last row I want to go to just I.
Want to go to just one row I want to make sure everything looks good before we loop through all ten of those it gives us something that is that we don't want to go so what we can do is just comment out comment out last row okay so all I did is now it's a comment so all it can do is going to go through one record once we know everything works just right then we can erase this five and we can actually go to the last row so temporarily we're only going to do one single record and that's good we don't want it we want to make sure everything runs perfectly before we go.
Through that okay and now we can start defining our data for each individual patient okay and remember we've set a variable for last name so we can go ahead and do that let's put in last name and that's going to equal dot range okay that's our first column okay so that's e and and the custom customer row okay dot value that's the last name okay so that means that means whatever row or on.
E-e-e here right and whatever row that's going to be the last name so we're just defining the last name we need to do that because we're going to use it twice in the code there so we want to make sure we define the last name we also want to define the appointment date okay so the appointment date is equal to dot range and that is a G okay and customer.
Rough okay dot value and that's the appointment date okay and once again right column G that's the appointment date okay so we've defined that because we're going to use that when we name the PDF so we want to make sure we've entered that and then what we want to do is then we're ready to fill out our application now when the PDF opens we need to know what state is in you know what we're going to be using is something called the send keys and the send keys is really.
Important for let's go ahead and open it we'll run this code and the send keys that basically allows us to mimic those keystrokes so this opens up and now we want to put in the last name right but it's not ready I mean if we can't just start typing right right if I start typing nothing up so what do I have to get it in last we have to get the cursor in this last name how do we do that well in this particular PDF we just hit tab tab write tab gets the last name so we need to tell Excel hey we need to send a.
Key tab to get the last name okay so we can do that all right okay and again we're going to use the application send keys so we'll go ahead and type it in application send send keys okay and then we're going to use quotation marks and then open bracket tab close bracket and then close quotation marks and then true we want we want it to wait until it completes it before moving on to the next line of code so you'll always want to write true because yes we do want it to wait okay and then we want to go.
Ahead and at the top we want to into that last name so let's go ahead and do that and then we're going to do that again with send keys we can use application ten keys again okay but this time we're going to do is the last name last name all right all right so let's go ahead and test that out and see how that works all right and then we're going to put true on the end of here so it's always consistent we're waiting and now let's go ahead and click run on that and then see how that goes in the PDF opens up and it tabs over and types.
The last name perfect okay great and then the next step we want to put in the first name and to get to the first name we're going to do a tap ok so let's do that and then all we have to do is basically repeat this step and then except use the first name okay we haven't defined the first name as a as a specific which is okay but we can use the actual sheet and range so we've already defined the sheet so dot range for the first name okay and it's going to be F okay range and that's F and the.
Custom customer row that we're on okay dot value okay and now there's actually one more line of code in here in fact it's when when Excel does it it's too fast and often times the last name won't complete before moving over and so we have to put in a timer here and I'll show you I'll show you what that looks like let's go ahead and close this one out and let's see it made it okay and generally it gets cut off because it's so quick in this case it worked but generally we'll put in a timers when the.
Names are longer it it'll it may only type in JM before moving over to the next so what we do in these cases is we put basically a wait we always want to do a wait so let's go ahead and put it in that way now because we're going to be using that application right now so we can just simply copy and paste that and what we want to do is we want to put it right after it into the last name we want to then wait so we're going to go ahead and wait now and we'll use one tenth of a second or one second but it's basically one second we want one second.
In order to before we go to the next field and that's going to really help us wait until make sure all the text has been typed in before we go on to the next field so now that we've entered and we're going to go ahead and mark this as first name so we know where we're at and then we'll go ahead and do another wait now okay so here we have we have tab send keys first name and then wait now okay let's go ahead and run that one.
More time so we can get to the next one and it's going to open up that PDF and it's going to enter the last name and then the first name and then it's going to tab and then the next is we don't we have no age right so that's one tab and two depth so we actually want to enter tab twice to get to the address okay so let's go ahead and do that so we're going to wait to the first name is completing and then we're going to enter tab twice okay to get to the next field once twice okay that's going to tap it now the next one is the address right so we want to put in the address here's the address.
So that's pretty simple all we need to do is we need to write in we can just go ahead and copy this because it's easier to copy and paste and let's go ahead and copy all of this because we want to do that for each field okay and but this time we're going to be entering the address so we'll change that to address and address of course it back in our cell is column I so we want to change that to column I so let's go ahead and do that all right now it's column a and.
That's all we have to do to get that address there okay and we can continue on further and the next is the city so and the city is one tab away so weekend we've already got the tab and the city is in column J so let's go ahead and change that all right - J wait now and tab once the city in the next is state okay so we'll pop copy and paste that and let me go ahead and put that in there so we know we're a city state also one tab away and the state is also.
Column okay so let's go ahead and put column K there and then the next last is the zip code for the address part of it so we'll go ahead and write in zip code and the zip code is in L okay that's it so so our code so far we've entered the first name address city state and zip code okay let's test it out and see how we're and then I was urge you dug route before you run any code to save your work in case something messes up which I often do so great to save your work because once you've run code there is no undo so oftentimes you have to close it.
Out without saving so it's always good to save before you actually run your code so let's go ahead and click run on that and we'll see how that works okay and it's okay we already have open so what we want to do is want to close this one out first all right we don't want that running up and then we're going to control break the code okay and okay so let's run it from the beginning so let's go ahead and click run them once again because we had it open so we want to close it let's go ahead and run it and we see it entering James John Main Street Angeles.
California and zip code very nice okay and we've already tabbed over and we're ready for the email excellent so let's go ahead and put in our email address okay and again we're going to paste those three lines of code and email is M okay M okay great and then we have the home phone number and I believe the home phone number is two tabs away okay once we've entered email we don't have a referred by it but we do have a home phone then that's two tabs so we do have to enter tab twice here so this is going.
To be female and if we're going to be copy-paste the tab we want it done twice so we want to tab twice over before entering email and the email is in row M okay so we have that there excellent okay and the last one is the phone number okay so the phone number is also I believe two taps away so let's go ahead and double check that and email want to phone okay good yeah it's two.
Taps away we've checked that out so let's go ahead and type that in again tab one more time okay good and let's see is it email okay good so actually we don't need this tab here not after zip but we do need after email so the extra tab is only after the email and now we're going to put in a phone number but in Excel the phone number is is formatted in Excel so if we put that.
Phone number and this formatting you see the number in Excel it's just a number we would like it would be nice to have the same formatting in VBA and we can do that in VBA as well so why don't we go ahead and do that okay so in this case let's go ahead and just copy and paste that a so we're going to put the phone number in but this one's going to be the phone number and the phone number is n okay but we have to write a little code.
Before that so we don't want to enter it exactly as it is we want to enter it formatted so we can do that by right before clicking format okay and what are we going to format we're going to format this phone number and then the next after the next comment is we're going to put a column extra comma in there and what is the format that we want well we want I think was three and then a dash and then another three and then a dash and another four okay and that's the format close the parenthesis okay so.