Collaborate on Create PDF Invoice for HR with Ease Using airSlate SignNow

Create pdf invoice for HR

hello this is Randy with Excel for freelancers  and today is training we're going to be taking   information from a table and automatically filling  in multiple PDF forms with a click of a single   button so please join us for this amazing training  all righty let's get started so we're starting off   with the table in this table we have a total of  10 names and this table basically contains basic   information for our contacts in this case we're  going to call them patients here's a reason why   you might want to have this type of macro running  let's say you work in a medical office and your   boss asks you to complete patient intake forms for  each and every patient coming in he asks you right   out there their last name first name the address  city state zip and email well now you could write   each one of these out individually however if you  have a list of names we can actually 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 that's basically it this is  going to format our phone number just as we have   it in Excel okay so that's great and we're going  to wait after that all right let's go ahead and   check that to see how we're doing we're going to  close our current form okay run it and let's go   ahead and run it now and see how that works for us  all right it's open and we're typing the last name   the first name the address city state zip code  email home phone number format is great okay and   now we're back on the cell okay but we're done I  mean we're done within it we've entered all the   data so now it's time to save the form and just  as if you were now we're going to in we're going   to print it we're going to save it in our case I  want to make it visual for you so what I'm gonna   do is I'm going to print it to PDF and save it  in your case you may just want to print it to   a local printer but what we're gonna do is we're  going to print it and save it to print it to PDF   and save it which is basically we're going to be  mimicking options print okay and I already have   cute PDF this is a great PDF writer I'll send you  links okay and this is going to create a PDF so   basically what we want to do is we want to mimic  that action of manually putting and then click   print okay and once we print it right then this  pop-up is going to come up and it's going to tell   us it's a save dialog where do we want to save it  okay so and that save dialog is right here okay   and basically it's going to say okay where do we  want to save it and all we need to do is basically   get down to this and put in the entire the URL of  where we want to save including the name so we're   going to go ahead and write that and let's cancel  that out and now let's do that through code okay   and basically the way we do that is we can go in  and first thing we want to do is we want to use   control P remember control P is also a shortcut  for clothing like if we do file print control   P you see that shortcut there we're looking for  that shortcut because that's going to tell us to   send the keys ctrl P so we need to write code that  says ctrl P okay so all we need to do is write in   application send keys and then quotation marks  and then we're going to use this little upper   triangle above the six we're going to use that  along with the P in parentheses okay and there's   a great website on excel and I'll show that to  you on all of these locations and it's here and   I'll include this link as well and it basically  includes all of these send keys that you can use   and especially so if we have shift we would use  the plus sign control the caret that's what it's   called and the alt okay so I'll include these with  it because that's a great great website there and   so I'll go ahead and close those parentheses and  that's going to basically say launch our print   okay so once it's printed once we have it up now  we also want to wait we want to win do it we want   to make sure that print dialog opens up before  anything else happens so let's go ahead and wait   about three seconds before that happens and then  when that once that opened up all we had to do is   press ENTER okay all we need to do is press Enter  so we can do application send keys enter so what   that's going to do is once it opens up just click  enter okay so all we need to do is change this   tab to an actually enter okay and that's going  to mimic the keyboard behavior of enter okay so   we're good there and then and then we want to wait  a good amount of time for that remember that save   dialog already took a while to open up so we want  to we want to also give it a while so let's do   five seconds okay and then once that happened we  also we want to go directly to the path the path   where so now let's walk you through that what  we want to do is we want to print it okay and   then once it's printed let's go ahead and remember  to click enter enter that's all I'm doing I just   click the enter key and then that save dialog  is going to pop up okay and now what we want   to do is we want to get to this field as quick  as possible right we want to get to this field   because we want to save it and you see that little  line unto the end okay that means alt n all right   let's let's go ahead and let's go into another  let's go in here okay and then alt and you see   how it's going to direct so regardless of where  we are around this on this pop up if we click alt   and it's going to go to this field so we need to  do the same alt n we want to mimic that behavior   so cancel that out back in our code here all we  want to do is alt N and if you remember our alt   n is the percentage to alt as a percentage sign so  let's just copy and paste so we can avoid typing   too much and then alt is the percentage sign and  n that's going to highlight okay that's going to   highlight our our text and now we're ready to  enter and what are we going to enter well we're   going to enter the PDF folder remember the save  folder of where we want to save it and we're going   to enter the name and the date okay so let's go  ahead and do that now all right so once again   we're going to use our send keys command and  what what do we want to send is let's go ahead   and put another weight in there so we give it  time to type out before we enter that zenki's   so after we after we highlight that let's give it  another second and let's go ahead and put in send   keys okay I don't think we need applications not  always important but let's keep consistent here okay and we want the save PDF folder that's the  folder that we designated to put our saved folders   in and okay we need that a backslash there for  the folder designate designation and we'll go   ahead and put that in and what else we want to put  in the last name okay because we want each file to   be saved differently so let's go ahead and put in  whatever lastname we're on and let's go ahead and   put a spacer in there so that we can the space  between the date and the name so we'll go ahead   and put a space in there and then another and and  then what we're going to do is we're going to put   in we want the date and the date is a appointment  dates okay but we don't want just the date okay   we actually want to format that day just like we  formatted the phone number so we're going to use   that format command format appointment date and  what format do we want to give it to well you   can really use any format you want but in our case  we'll go ahead and do DD underscore mm underscore   and then why why why why okay but you're going  to sign any type that you want and then we want   to make sure to close those parentheses okay so  that's going to format the date and we also want   to make sure we enter the extension which is  PDF okay dot PDF that's very important so that   it saves properly as a PDF document okay and so  that'll that will go ahead and put it and after   that what do we want to do we want to also again  give it time to type in that name properly so   we're going to put in a waiting time we'll put it  in two seconds okay so two after they type it in   okay so we'll do that and then what we're going to  do is we also want to let's see what is the next   step we will want to save that okay and what is  the quick step let's go back in to that PDF and   and basically to save it is alt s okay alt s will  get us to that that was to get us to the safe so   let's go ahead and put in that and okay and then  all we got to change all this and that's going to   save it that's going to click Save so then we're  done and we're also going to put in another wait   time after that okay so let's go ahead and put in  another wait time of two seconds after that okay   let's see how that works let's go ahead and run  that code see if we have any issues and click on   run okay okay we need to save PDF folder all right  so we gotta fix that all right now we can run it   all right and we see the last name first name Main  Street California a good good email reference home   okay great and okay the format good and then save  all right and I think it worked great so let's go   ahead and check the PDF that we just created in  a safe bf over here it is James 14:8 we got the   date let's go ahead and open that up and look it  looks just right that's exactly the way we want it   okay good so we can go ahead and close that and  close that and now that we got it working great   we can go ahead and complete our loop with all of  the necessary rows so let's go ahead and do that   there's a few conditions I also like to put in  there's one line of code that I would like to put   in in case there's an error and if you if you're  continue to run this code you may get to the point   where where that the file already exists the PDF  may already exist in the folder and so what we   want to do is we want to say we want to basically  put in an option says if this file exists delete   it okay so that way we don't get a pop-up that  says this file exists would you like to save it   or write we don't want that pop-up we just want  to delete if it exists so we can do that with one   line of code and what we'll do is we're going to  copy this okay copy this that's basically the file   name if dir that stands for directory right we can  do if dir right if that basically were saying if   that direct sorry that file name is not empty  right does not equal NP then we won't want to   basically we want to delete it and then right then  we just want to kill it right killing is the same   thing as deleting it so paste again so basically  with this line of code what we're doing is we're   saying if if the file exists delete it and now we  before we get to the save part that way we don't   get a pop up and that works really well so we'll  go ahead and put that in and what that's going to   do is that's going to delete this one if it exists  before we ever get to that so we'll go ahead and   add that in and a few more codes so once once the  loop finishes what we want to do is we want to   close the application and let's go ahead and to do  that all we have to do is I believe it's ctrl Q so   we go into file and exit so once all things ctrl  Q so let's go ahead and add in at the very end and   aluminum but that's after our loop completes so  we're going to put in and our loop stops here so   it keeps looping out so after it completes that's  where we want to end that ctrl Q so let's go ahead   and put that in now when you can do that with the  send keys and basically we can just copy this what   we've had here and then put in the caret let's  go ahead and copy it and then the caret which is   that above the 6 and then the Q okay and that is  going to also close the application there's one   strange bug in Excel VBA that somehow messes with  your number lock and you can simply fix that with   this line of code so I've got a line of code kind  of an odd thing basically it reverts your number   lock back to the way it was because every time I  seem to run send keys it is my number long from   locked unlock so this line of code takes care of  that so you don't have to worry about that all   right I think we are good let's go ahead and test  this out and see it perform so we just have away   and there's one other thing why don't we go ahead  and assign this macro that is create PDF form okay   to the button that we created for it so we're  going to copy that and go back into our excel   and we created a button for it so what I'm going  to go ahead and assign that macro to it paste   that in there or you can click here okay creepy  difference and now we can perform all this action   simply by clicking on the button here so all right  but before we do that we've got to readjust the   code so that make sure it runs through all of  the rows so we'll go ahead and back up here and   now we're going to go back get rid of that five  and basically now we say for customer from five   to the last row great so now we're ready now we've  assigned the macro here let's go ahead and try it   all right and now we can see it opens it up the  last name goes in and then the first name now we   have the address city state zip code email along  with the formatted home phone number and we're   going to print it to the PDF writer it'll open  up it'll launch the save as dialog and then it   will update the name with the last name and date  and then it will start replacing the information   with the second line now it's going through the  second line it's replacing all of the same things   and it's going through and this is the second row  of our table it's going to go through the same   process and I'll go ahead and speed this up as the  videos lunk and then we'll come back at the end all right we're back the application has  closed out it's finished and let's go   ahead and check the folder under the safe  and here we've got ten names all different   names all different files all PDFs filled  out differently and there you go that's how   we automatically fill out PDFs using Excel I  hope you like this video a little bit longer   than normal but I hope you appreciated it if  you have any questions comments I would love   to hear and as always please share and like  this video as much as you can thank you so   much please don't forget to subscribe to our  YouTube channel and select these send me all   notifications options so you can get our videos  to you right away as soon as we create them