How to Extract Data From Unlimited PDF Forms To An Excel Table IN ONE CLICK

hi this is Randy with Excel for freelancers and  thanks so much for joining me in this week's   never-seen-before a training video I'm going  to show you how to take data from unlimited   filled out PDF forms and transfer all that data  from any folder on your computer into a single   Excel table and in just one click it's going to  be an incredible training all righty let's get   started we've got a lot to cover in this training  video so here's what we want to do and basically   in other videos we have covered how to take data  and put those into fillable PDF forms and in this   particular video what I want to do is I want to  take PDFs that have already been filled I don't   want to bring that data in to this and when I say  PDF let's go ahead and take a look at what kind of   form we're looking for and we have a specific form  where we filled out some data and we have here in   this PDF form that has already been prefilled out  we've got some basic information name address city   email and some basic we're not gonna worry about  the rest of the data here but they're in this   particular form that could be a lot of data we're  just gonna focus on the main data and so what I   want to do is I want to take this data along with  other forms that I have in the specific folder and   I want to take all of that data and put it in to  this PDF so we have a folder here and we've got   four different filled out PDF forms along with  the data source and so what I want to happen is   I want to take data from each of these four forms  and I want to put all of that data into the first   four rows of this table and I want to do all that  in just one click first we have set a folder here   that is our PDF folder so we've gone ahead and  set that and that's the same as this folder here   our test PDF folder so that is the information  and here's basically what I want to happen when   I click this button here extract all PDFs I wanted  to open up the Foxit reader which it's doing right   now and I want it to find that form wanted to pull  all those forms data and I wanted to extract the   data from all those forms in one fell swoop and  it's doing it right now and it's gonna extract   that data into a CSV format it's gonna take that  data and put it right in here just like it did   now and that is all I have to do so it's a great  trick and it's a great time-saver in fact just   one click this can work for unlimited numbers  of PDF forms and let me go ahead and show you   how we do it the important thing here is we need  to use the free Foxit reader and that is a really   really excellent PDF tool that allows us to do  what we want to do by extracting the data from   those forms and I'll go ahead and show you that is  the free Foxit PDF reader and you can get it from   here I have included a link in the application so  you don't have to worry about memories and I've   put it here under the Foxit reader there's a link  right here which will get you right to that so the   first thing you want to do is to download that  from this site click the free download just go   ahead and download it and then install it once  it's installed on your computer you will then   have your application installed you want to remove  any other one-time messages that come up so you'll   want to open your PDF file here and let's say  you may get some one-time messages so make sure   you do not have any of those messages that might  appear here so that when you open up PD form just   the PDF forms open up with no other messages or  highlights or anything like that secondly you want   to make sure that your form is set to the default  you want to make sure it's set to the default form   and let's go ahead and set ours under the desk  under our test stop you want to make sure that   it is the default reader in my case it already  is but if you want to do it you'll right click   open with and then let's go ahead and move this a  little bit higher up so you can see it right click   and open with and then you want to choose another  app select choose another app even if it's already   displayed and make sure that you select always use  this app PDF files and select that Foxit reader   9.1 you'll want to select that and then click  OK that'll make it your default PDF and that's   a very important step we want to make sure that  it is default that will be helpful in pulling up   data when you pull it up although it may not be  required on every system it is certainly helpful   so set it as to default and you'll know it's your  default when you see this little Foxit PDF icon on   each of your data and it depends on your view  maybe you know if you have it on your details   view here you're still gonna see the icon here so  that's very important now also what's important   is in this folder that you've set you want to  make sure that each of the PDF forms follow the   same format in other words you don't want to have  different PDF forms in that folder all PDF forms   should have the same exact form probably they're  filled out differently however they should be   the same form that is also important as we're  gonna we're gonna teach Excel to just read one   specific form type if you have multiple form types  maybe you'll want to have multiple instances of   this application and then each one setting for a  different folder so what we want to do is we want   to have this folder for one specific form type we  can just click on that and browse for that and we   put it on our desktop so we'll just select that  folder and then click OK that is going to set   the folder and so all we have to do this is a  relatively simple thing what let's go ahead and   pull in and let's go manually what we're actually  doing and so I can show you here is our PDF and   what we're gonna be doing is we're gonna be going  forms right and this is all done on automatic   form form to sheet and combined forms to a sheet  combined forms to a sheet now that all pulls up   this screen but what I've done so in other words  you were gonna use send kids and zenki's and VBA   is gonna automate this process form form to sheet  combine form that's three steps right however Fox   is such an amazing application we can actually  set those three steps into one specific hotkey and   I'll show you how we did that once you have your  reader open you want to go in to the customize the   quick access toolbar here and you'll want to click  on more commands and now under keyboard keyboard   writte we're gonna create a shortcut and then what  we're going to be working with is forms here under   form and what we want to do is I want to go I want  to create a shortcut for combine forms to sheet so   that means I want one shortcut to automatically  pull that pop-up up and we can assign that right   now I've got a control ease but you can come here  and click control E and then assign it or you can   click ctrl Q if if it's already assigned you're  going to get this a sign so you want to make sure   you find something ctrl R let's see control Y  we've got that so if it's available then you   can click assign I'll keep it at ctrl e for my  purposes but keep this in mind control ethat is   what you're gonna have to remember we're gonna use  send keys in VBA to automate that so we're gonna   leave it at ctrl a that's the way I have it now so  I'll cancel out of this let's go ahead and double   check to show you again click on here under the  quick access more commands and then under keyboard   form combined forms to sheet control is our SAR  assigned short key and what that is going to do   so when I press ctrl e it's gonna pop up you see  how that works so that's what we're gonna automate   so that means I want to do I want to pull that  up I want this screen this is the screen I want   because I want to what I want to do is I want to  add files then I want to add all of these files   by selecting all and then I want to open them and  then I want to export so that's what and I'm gonna   export it to this and I'm gonna overwrite it and  that's what so that's the process but that's we're   gonna do that and then I'm gonna tab over here  and click no we don't want to see it so that is   the process that we're going automate in DBA so  the first important thing was I want to automate   this step this step in this step so those three  clicks one two three we're gonna automate it in   one shortcut key and we're going to call that  ctrl e so that's what we're gonna do so let's   go into the VBA editor and see how we have gone  about that so far and we'll walk you through the   entire process this one is actually not too  difficult so we're gonna go through that it's   relatively simple there's only two macros there's  one to browse the folder and one to extract all   PDFs so we've been able to do that in in just  two macros so into the developers tab under   Visual Basic if you do not have the developers  tab available or not visible click on the file   and under the options under the customized ribbon  you can go ahead and see that here the developers   you want to make sure that is selected their alt  f11 will also get you there into the developers   tab and here are our two macros here we have  one module PDF macros we have just two macros   this one is going to get the folder and we've been  over this a few times before all this macro does   is allows us to determine the folder and then it  places that folder in f3 f3 is where our folder is   located that's all its gonna do f3 so that matter  is pretty simple we've assigned that macro to this   browse button by clicking here and clicking inside  to the individual shapes clicking asseline macro   and you'll see right here get PDF folder has been  assigned and that's actually been assigned to the   button and the icon itself so it's been assigned  to both and we do that simply by assigning the   macro to the group assigning the macro to the  group automatically assigns it to the individual   button and the individual icon simply by clicking  assigned macro and get PDF folder then clicking   ok so that's the simple one that just is gonna  allow us to set because we need to tell Excel   where is it going to look for the PDFs inside the  folder so that's why we have that folder set and   that's an important part that covers that Mac  so it's very simple that's a simple macro now   our only other macro is going to is going to walk  us through the entire process it's going to it's   going to basically do this it's gonna launch the  PDF program it's going to launch this program it's   going to do control its which is going to launch  this and this and this right it's going to add the   files it's going to export to a CSV then we're  gonna take that CSV files and we're going to   bring them right in here that's the process and  that's what we're gonna automate so that you can   take extract data from any PDF form and Limited  PDF forms bring that data into your Excel table   in just one click so let's get back into that and  walk you through each step of the macro so I show   you exactly how that's done first in this macro  we've titled extract PDF data and we're gonna   extract the PDF out that's the application in our  case it's Foxit PDF Reader as a variant we also   have a few strings PDF folder is the location  and the export file we've got two strings the   folder that's this folder here right that whole  file path and we also have our extract exponent   export full file that is the file that we're gonna  be extracting in this case it would be data source   data source is that entire file name so for that's  our data source that's our export and contains all   of our data when we open this up we will see some  data in here it's just a it's just a bunch of data   let's go ahead and make these columns very small  so you can basically see it's just contains all   the data in our four forms in a raw format in a  CSV format that's all we need when we bring that   into Excel it then be have our Excel formatted so  there is no need to do any formatting on this and   so this just contains all of our data here as you  can see so that's what that is so that's the file   path that we need for that so we also have some  long some whole numbers client row customer row   we'll go through these individually and we're  gonna be working primarily with sheet one that   is our client so we're gonna using if with sheet  one and the first thing we wanna do is we want   to make sure that we do have a folder in f3 if  that is empty we need to instruct the user to   give him a message box please browse for folder  and we're gonna run this macro for them we're   gonna help them we're gonna guide them instead  of just saying click on the button we're gonna   actually do it for them so that's very helpful so  we'll run this macro which runs this macro right   here and it browses for the folder so and let's go  ahead and right now I want to make sure - so once   they have the folder we can it probably exit this  sub here just in case they don't select a folder   I don't want the rest of the sub to run correct  if they don't if they select if they go through   this Makarand clance alit I don't want the rest of  this to run so we're exiting this sub makes them   click the button one more time just to be sure  so now that they've gone through that process   they've gotta extract folder where all their  PDFs aren't located we then go in we then assign   that folder that entire location to this variable  here PDF folder has a string so we're gonna store   that now we need to know our export file the file  remember that's gonna always be called datasource   dot CSV it's always gonna be called it that is the  default name assigned by fox so we're gonna keep   with that default name no need to change that  and we're gonna make sure that that located in   our PDF folder so we need to assign that entire  file name which is the folder we're gonna store   it here along with the name here and the reason  is what I want to do is I want to delete that   before because we're gonna be bringing on a new  source so I want to make sure that any source   that exists in this folder I want to make sure  this is deleted because we're gonna be bringing   in the new one maybe we've added PDF forms so it's  important to delete any CSV that was there before   so that this three lines does that in fact just  one line deletes it but if there is no if there   is no export file if there is no CSV file in that  folder it could create an air so we have wrapped   that in on-air resume next and on-air go to zero  this ensures that it's deleted if it exists and   if it's not deleted no error will appear a next up  we need to launch our PDF program I need to launch   it I need to do that basically launch this and  our PDF app our shell will locate this keep in   mind this file here could be different for you if  it doesn't work check this right and the best way   to do that is simply by right-clicking and right  click and I'll show it to you in just a second   properties so that I'll bring it up bring it to  your attention here ok all I did is right click on   the shortcut and it is this start in you want to  make sure or the target either way the target is   what you want sorry the target so just copy this  you can use the quotes if possible this is what   you want let's see so the target is where you want  in case it's not in a different folder for you you   can find it right here under the right clicking  the shortcut that you find on your desktop and   looking for target and you can find that location  so it should be it should be this but if it's not   then you'll want to make sure to put it and we  want it to launch in normal focus this is gonna   launch the application this we don't need as long  as as long as it's launched and brought to the   present maybe I'm gonna keep this if the program  is it's launched but it's not in the viewable area   you may need activate so I'm gonna keep this here  in other words once we launch it once we launch   it it should be in front if it's not in front if  it's in the background you may need something like   app activate which will actually activate it so  you may need this although however it seems to be   working just fine on my machine I'll leave this  in but I'm gonna leave it commented out next up   we need to go through the process of sending the  keys so next up right once it's launched what's   the next thing you know it let me let me go ahead  and half the screen so you can we can go through   the process and we can see both of them at the  same time alright and I'll split this for you so   you can see all right very good now this split so  we can see both so what we want to do now is we're   gonna wait a second we want to make sure we give  it a moment and then burgundy ascends keys this   little icon this is control control II control II  what does that do control II it launches this so   when we send those keys to them it's gonna do  this now what's the next thing we need we need   to add files but you see that little underlined  right there that means if we just click a it's   going to add the fight it's gonna launch our open  screen right it's gonna perfect it's gonna launch   our open screen so now we know we just need to  click the a so our next step wait a little bit   and click a send a that's the next step so send  keys we'll do that so now we're ready now we're   at this step let me minimize this you can see we  can follow along in VBA so now what do we want to   do well now we don't our my folder defaulted here  right it's because but we don't necessarily know   where it's gonna open up it could open up here  it could open up on a different folder right so   we don't really know so we need to make sure that  it opens up on that so that's an important part   and as long as it's set the default it should open  up automatically there so let's go back and here   we are this and the next thing we want to do is  shift tab the plus is the shift right I need to   select all of these I need to select all of these  and what I'll probably do for you is I'm gonna am   going to add another line in here that's gonna  set the default location to make sure that the   location is whatever your location is so we'll  ensure that and so the next step is once on once   one let's go through that one more time once we  click add files what do I need to happen I need   to select all right but I need to do it through  keyboard shortcuts how can we do that let's say   shift tab now we're here right and control a  that's gonna select them all shift so those two   steps shift tab control a so we need to do that  in Excel so we can do that here here is the shift   this is the shift plus the shift and tab so shift  tab wait one second control this is the control   and this is a on thank you so control a now we've  selected them all now what do we need to do now we   need to tap tab oh sorry here okay we've all and  click tab and we need to open him and we can click   use and for open right or we can tab so we have  a few options there we need to open that that's   important so under after we've shift tab we have  tab tab tab right or we can do n so we've done tab   tap tab that's gonna put us right here on the open  it's gonna put us right here right here and now   we want to click enter enter so that's gonna add  those forms there so here is our enter so we just   walk it step by step by step and a lot of you may  have different configurations you may want to do   different things the idea here is you want to put  away your mouse when you're trying to map these   steps put away your mouse and figure out how to  do it through your keyboard only I get a lot of   questions on I want to close the application I  want to open the application I want to switch   pages or switch sheets or I want to do a lot of  things and I want to put that in VBA so my my   suggestion is always just put away your mouse  take the batteries out put it far away figure   out how to do it with your keyboard only and then  write down those steps write it down you know some   helpful tents anytime you see underlined like a  you know that just the word a will help so and   then write down those steps and then take those  steps and put them into these send keys and the   second step would be weight now add a lot of time  on these weight now two three four whatever you   want to add that's gonna slow your code down so  you can actually follow it when it and if you're   having issues you can follow it to see where  your air is so both those keys write them down   right down your steps transfer those steps in to  send keys and then add weight now after each line   so you can follow the steps and that's gonna be  really helpful when you're pretty much trying to   do anything using send keys okay so now that we've  pressed enter now that we've pressed enter we want   to move to we want to put the export so we've  clicked export let's see shift tab enter here and   so what we want to do is we want to click export  that's our next step so clicking that it's going   to export now where do we want to export this  to so here we are this step here now we're here   we want to export art remember our export file  contains contains that this link right here it's   just our export file contains that right here so  we want to we're basically going to take that and   put it in here and it also includes art so it's  gonna look just like this and then this so our   export file is gonna look just like that that's  all and then we're gonna click then we want to   tab tab tab and save or we can do control enter  control inter also works so we've chosen control   enter control enter I'm going to do it right now  control enter there we go now it's done okay so   now we've got this screen up so we've done control  enter here we are here now we're gonna wait a   little bit and now we're going to click N and okay  so oh sorry now we're gonna click control enter   wait two seconds and click end and is for no sono  done so that closes that screen and close this   this key when you send crease there's a little  bug in in Excel that turns off your number lock   you know that key that on your number keyboard  just for some reason it turns it off this turns   it right back on so that way your number lock  is in a I'll go now I always use that at the end   of it so that's it now we have our file exported  right now our file is here right here it is here   we just added that right now so now we're good now  we have the file now we need to go into Excel and   get that data now I need to what I need to do is  I need to go in Excel what I want to do is I want   to create a data connection and I want to we have  a lot of data sources we'll all delete them but   what you want to do is you want to create a data  connection and you want to create it from a text   alright and you want to click here right this is  what I want to happen but I want to do with your   VBA I don't want to create that import and I want  to import that and I want to put it on sheet 2 so   we're gonna go through this step we're getting  there commas comma separated values so we're   gonna use com I remember that next net and where  do we want to put that well I want to put that not   on the existing worksheet so let's click - I want  to put it right on sheet 2 8 1 this is where it's   gonna go sheet 2 a1 and then we're gonna click  OK if you look at the properties we do want we're   gonna add some properties and we want to make sure  to overwrite the existing cells clicking ok and a1   and okay we're good and now our data will fill  here and when we have 4 it'll show all 4 so that   is how it's done so that's how we're gonna do it  within VBA and then what we're gonna do is we're   gonna take all the data and we're gonna bring it  over into this table right here alright so back   in to our VBA we go and let's go ahead and pull  up now that we're done with our Foxit we have all   of our data we can expand this a little bit here  so let's go back into so now we're gonna wait two   seconds and now I don't want to do is I want to  clear in sheet - I want to clear out the existing   data so what I want to do is whatever data is  in here from previously I want to clear it out I   just want to take it delete and clear that all out  and delete the query there as well so we're gonna   delete that so we want to start with a fresh data  there so that's very important so we've done that   clear the existing data alright we don't need  we don't need to delete the active workbook but   we may need to in the future if there's a conflict  so I've left that in commented out for us purposes   and now we need to export the file under PDF so  we need to export it remember we talked about   that data right export we need to get that from  text and we need to export it what are we gonna   do we're gonna export this file right here so we  need to do that right within VBA so with sheet 2   we're gonna set up a query we're gonna add a query  and we're gonna give it a connection we're gonna   connect it's gonna be a text file CSV in our cases  and the export file remember this is the entire   file name that includes the folder and the file or  export name equals the folder plus our file name   so it includes everything so that's what we're  gonna be exporting and where's the destination   where do we want that data to go sheet 2 range  a1 that's where we want to start now we're gonna   assign it a name data source I'm going to sign  that name and we are going to we want the field   names we're gonna sign a different beginning of  row numbers we certain things that we want but the   most important thing is we do want to make sure  that we overwrite the existing cells remember we   selected that option to overwrite and we want that  same option right here overwrite the existing data   everything else is pretty much dead we have basic  formulas that you can settings for example this   is going to be a comma where we selected comma  comma delimited file so that's true because we're   not using semicolon or space or tab so those are  all set to false this one is based on commas so   that's how it is and then we just said the raid  these are all gonna be general data types our   data types don't matter because when we bring it  in to our main table then it's already formatted   so it doesn't really matter what our format's are  on sheet 2 because this is already formatted here   for our data so that's beautiful we don't need to  worry about formats because we have pre format to   disk this is why we keep our this is why we don't  don't bring it right in here right we don't want   what we want to do is we want a controlled import  controlled meaning it doesn't matter what format   or what columns or anything in here when we bring  in the data it's just raw data then we control it   by bringing the data just the data we're not going  to bring all the data just the data we want right   into here so our format can be general one is used  for general and so that's basically it's refreshed   by we don't need too much else so we are done with  our query that covers our entire query now what do   we want to do well now we want to make sure that  we get the data ready so that we can bring it in   this sheet so let's go ahead we want to clear  this out with in VBA starting in rows starting   in cell e8 and all the way down to the bottom  however far we want to go we want to clear that   data out before we bring in our new data so we do  that with in VBA as well so we do that with this   line right here now what I need to do is I need  to know the last row of the data when we bring   let's go ahead and run that and bring some data  in here so you can see let's go ahead and run the   macro again and it's gonna select all it's gonna  import our into the export and we'll bring back   data in here it's gonna choose all four forms it's  gonna export that CSV into the folder we choose   it's gonna say no and the data is gonna come in  okay so now in sheet 2 I need to know this last   row Ryan I need to know the last row of data a is  going to be your column these are the names of our   PDF right here so we need to know the last row so  so that we can properly import all of the data so   we have to get that last row that last data row  is sheet 2 column a and then we use a high number   to get the last row that's here now what I want  to do is I want to get the data in to into here   but there's a lot of data in here right there's  a lot of there's a lot of things we don't need   let me go ahead and minimize let me bring that  those columns a little bit smaller so you can see   there's a lot of data here we have and you can  see the names are here so the names are up here   but their home number right we have work number  here so here's your headers and the information   but I only need some information right I don't  need all of it what do I need let's see let's   go ahead and bring this data out so we can see  what we're doing okay that's the that's the cell   number home number so we need don't worry about  the format's that's not important right because   when we bring the data it's gonna be properly  formatted so I need some information but not all   information but I need to know what column this  data is on so let's go ahead and just number our   columns here just so we can see e equals column we  just run a formula here okay so we have column and   we can bring this out all right let's go ahead and  bring it all the way to all of our data because we   only want some of the data and the reason is look  some of it's some of its repeated why is this why   do we have a repeated name in our PDF we do have  some duplication and that's because of of the way   our form is structured so you can see under let's  go ahead and pull up one of the one of the intake   forms here and we'll bring that a little larger  so you can see in our form we have but also the   names at the bottom too so we here you know we've  duplicated the name here it's just a duplicate of   whatever it's at the top so that's why we have  them twice sometimes but we just need one so now   we know the columns where the data is but I want  to map that to this so I want to know that this is   column 17 now how do we do that well we can use  mapping which we've done in some other videos I   put it in Breaux 6 so when we unhide row 6 you can  see 26 is the state 25 is the city 27 s the zip   code so the same thing here 26 is the state 27 is  the zip code 25 is the city so I'm mapping at each   one of these that I've assigned is the data column  this way what I can do is I can run through all   of these from here to here I can I can provide a  loop in fact I can go from this column to the last   column and I can figure out okay what columns are  data in here and we know what row let's put it in   so if let's go ahead and show the columns here  to because we're gonna run a loop using these   columns it starts out at five and we are going to  go all the way to I don't want to copy the format   so we will just right-click paste formulas okay  so it goes all the way to 14 right that's just   the format not to worry so it goes from 5 to 14 so  I want to want to loop saying from 5 : 5 2 columns   14 what I want to do is I want to say ok in Row 1  of our data we're gonna and then we're gonna loop   here we're gonna go from Row Row 2 Row 3 Row 4  Row 5 because we know the last row of data now   right we know the last row we know it starts at  2 we know it goes to 5 so for each row I want to   say okay in column 23 get the address in column  24 get the name so we can do that we've mapped   them out here map them out so when we get to the  first column for example we're gonna run a little   5 to 13 in the first column I'm gonna say okay  on our datasheet in column 33 in row to get the   forget the last name when we get to column 6 call  them 34 in our data get the first name so that's   how we're gonna do that same thing right in our  our data our vba so for the custom column 5 to   14 i've just been through why we're going to go  through those 4 columns we need to get the data   column right our data column is located in row 6  and whatever column we're in our data is located   in row 6 and whatever in whatever column is we're  on calling 5 it's 33 if we're in column 6 our data   column is 34 so we need to know what column that  helps us that mapping helps us data column right   here now we know the data calm now we can bring  our data in so where are we going to put that data   we're gonna put it in row 8 of our custom column  right 8 ok so now we know now we know the column   now we can simply copy and paste the data right if  we know if we know that this name ahead and pull   this up let's go ahead and get the the last name  okay last name we know to and we know our last row   so what I want to do is I want to copy this in a  sense copy but we're not actually copying it right   I want to copy this but we're gonna use it direct  and I want to basically paste the values right   here just like that that's all I want to do we're  not gonna use copy paste because we can use direct   cell to cell so what I want to say is here's what  I want e8 through e 11 equals a g2 through a g5   right so we're gonna use that that's all I'm gonna  say that's all I want to do and then when we go to   the next one I want to say f8 through F 11 equals  aah a3 so it's gonna it's gonna be a direct link   so we're just gonna we don't even need copy and  paste we're gonna use direct so we know what our   starting ROI is here 8 plus the column right this  is our row our first eight plus cells the last row   the reason we're adding 6 is because our they  start undifferenced at our data our first row   of data is line to write our first row of data is  line 8 here we need to add 6 at 6 so that's very   important so that's why in VBA we add 6 so our  last data row plus the custom column so basically   this line of code simply goes it goes from column  to column column and it puts the sheet that's all   that line does it goes from here copies run zip  code it says this it basically says this isn't   equal to this that's all it does and then it goes  to the next one then it goes email this it's it hi   like it goes is equal to this right here wherever  the email is here so that's all that that line of   code does and does it for you goes through each  column and does the exactly the same thing and   that brings our data in so we're good what now  we have covered the data now but I want to do is   I also want to add a link I want to know I want to  link to that orig PDF so when I click on that that   PDF opens I want to see the original document and  I want to add a link to that so it's very easy so   we say hey I see the data but I want to see the  original document so we put a link in there and   that's really convenient and it's a little bonus  that I added for you in there and I want to show   you how I went about doing that and again now  we're gonna run a loop from the road to the road   so we're gonna go data road we know the last data  row so we're gonna run a loop now and we want to   go from Dana row to data row so we're gonna run  a loop from 2 to 5 blasted row so I'm gonna run a   loop and what I want to do is I want to start out  I know in a in a a contains our document name I   know that I know our document name I also know the  folder it's in right so I know the document name   and I know the folder it's in I can create a link  so we've done just that in VBA going to so we're   going to say that oh oh is the oh is the column  that we're going to be putting oh is the column   and we're gonna say that Oh equals the PDF folder  and the backslash and sheet to a in the data room   this is our document name we just went over that  the document that creates a path now we have two   paths so the value is in here that puts the entire  value so if we clicked on that you can see here it   puts in the folder right plus the forward slash  or back slash plus the form plus the name which   we found in the column a so it puts that in right  here so that puts it in but it doesn't actually   link so we actually now have to use the same  information and we have to create a link and   we can do that here so let me go out of that cell  okay and back into VBA so now we have to create   the link hyperlinks add and what is the anchor the  anchor is the oh and is the address right the the   value we're gonna anchor it to this cell here  oh in the client roof what is the address well   it's the same thing the PDF folder plus the back  slash plus the value and then we're gonna text   the display what text do we want to display well  it's the same exact information we want the Lee   you could actually you could actually remove that  maybe that would be kind of nice list let's try   that let's remove the PDF folder let's only show  the document name because it's a little bit big   right so a text to display let's go ahead and say  okay now I only I don't want to show the full link   I just want to show the name of the document  let's go ahead and run that and we'll run the   whole thing and we'll see how that does that'll  just give us the document name and not the entire   the entire link the entire file name which is a  little bit big for this cell so we go ahead and   adjust that code so we can show that we're still  gonna link to the entire document so that's not a   problem the link will just work we're just gonna  update that great that worked just fine let's go   ahead and format that on the left and then we can  also reduce now we can reduce a little bit if we'd   like to great so now we've linked it we don't need  to have that we don't need to have that and now it   links perfectly great that's even better I like  that better so good now we've adjusted the code   to show just the document name but the link is  the full link so back into the VBA code let's   go ahead and finish this up so we've gone through  this loop and now we just need the last thing we   do is we want to activate Microsoft Excel because  our PDF is probably still active like this so what   I want to do is I want to reactivate this and so  that last line of code activates Excel using app   activate and that will activate or sell so that  is it that is how we run this entire macro and   it's really really simple it allows us to bring  data in keep in mind if you do have a lot of data   you will adjust a time for example when we when  we launched this I believe that there's a little   bit of a slow step let me let me go ahead and  manually show it format this one when we do this   and we add files and we select alright we know  we're selecting all and we open at this point   when you have a lot of forms there's going to be  some slowness so that if you have a hundred form   it will take time it will take a little bit of  time in the export which means that you may have   to cancel out of that you may have to increase  so if you have each all I want you to do is I   want you to start increasing these times all right  start in when you see the wait act wait now start   increasing these times to four or five or however  long you want until you get things right in fact   start out using high times and then reduce the  times reduce the times until you get it just the   way you want to so increasing these wait nouns  will move things slower so that you can follow   what's going on in the more data you have it may  require it very well it may require a lot more   time to run this macro so you may want to adjust  those wait now that's the only caveat I want to   make sure that you do adjust those times so that  it works perfectly for you every time again just   a reminder also in this folder make sure you  all of your PDFs have the same format are all   the same format that's very important so that  this works without issue at all I hope you've   liked this training if you did please share it  from our Excel for freelancers pages also please   make sure you download the workbook either with  your email or Facebook either one of them will   work just fine of course I always like when you  like and share if you're on YouTube if you're not   please I'll go ahead and subscribe and click  on the notification icon that really helps us   out and make sure that you get new videos each and  every week delivered right to you thanks very much

Channel: Excel For Freelancers

Views: 226,358

Keywords: PDF To Exce, Convert PDF To Excel, Extract PDF Data, PDF Data Extract, Extract PDF Form Data, Extracting PDF Form Data, Extract PDF Data To Excel, Excel Data From PDF, Excel Data From PDF Form, PDF Form Data Extracted, PDF TO Excel, PDF Extracted To Excel, Excel Data from PDF, PDF to Excel Table, PDF Form To Excel Table

Id: TB7C3pey2rs

Length: 43min 41sec (2621 seconds)

Published: Tue Jun 26 2018