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.