How To Convert Unlimited PDF Invoices To Excel In Just 1 Click [Masterclass + Free Download]

hello this is randy with excel for freelancers and in this week's training i'm going to show you how you can convert unlimited pdf invoices from any folder into excel automatically editable invoices with just a click of a button it's going to be incredible training i cannot wait to share with you so let's get started alright thanks so much for joining me this week i've got a fantastic training for you today we're gonna zero down on how to convert pdf invoices to actual editable invoices in excel we're gonna do be able to do unlimited pdf invoices we're going to convert all of them and we're going to do it in just a click of a button so that's a tall order we've got a lot to cover you'll notice this particular training looks very familiar we did cover this training estimate work order invoice one click a few weeks ago i'll alert you and put that link if you want to watch this so we're not going to be actually creating this invoice in excel we're going to be focused on how to convert this pdf invoice automatically into excel so that it is fully editable so it's going to be a great training in fact unlimited as many pdfs as you have in your folder it will convert them all so we've got so much to cover i hope you do like these trainings i bring them each and every week to you absolutely free here on youtube in fact even this application actually this pdf to excel application is free for you you can use the links down below all you need to do is just click on them and you can get them either with your email or facebook messenger we're going to get that sent over to you for free i hope you do like these channelings there are many ways you can do us a big favor all you need to do is just click that subscribe button below don't forget to hit the notification icon that will ensure that you get these trainings each and every week and you get alerted to those also comment below i'd love to hear your comments your feedback and your ideas that's what makes these trainings available and don't forget to click the like button alright i create these trainings and that will help us out a lot we'll get that youtube algorithm to get us a little bit higher in the rankings so that you get alerted when these videos are live okay very good we've got so much to cover but i just want to let you know if you want to take your excel skills to the next level there are so many ways to do that i've got an incredible mentorship program it's going to take you 132 hours of incredibles going to show you how you can define design develop and deploy your own excel based applications for passive income i've got that going on so i'll include the links down below and also a brand new patreon account if you want to get these trainings you want to have feedback and you want to get your own features or your own fixes or your own focus areas on these trainings i do that each and every week inside patreon so a lot going on there i'll include that link all right let's get started on this this particular week we don't need to design this it's already designed for us we did that in a prior video and i'll include the link down below so all we're going to really want to do in this particular training is how do we get this static pdf invoice how do we get it automatically converted to excel and how what if we have a folder full of them how do we do that so that's what we're going to be taking the focus on we're going to do all that with just a single macro so there's not a whole lot in the design state in fact there's nothing we need to do in design because it's already built for us i've got probably three or four videos on how to design invoices so we're not going to focus on that in this training so we've done that before so if you want to do that i'll include the link down below this particular training we did cover as far as how to create this is basically adding a new estimate adding a new work order and then converting that estimate to the work order to the invoice and then of course if you have one particularly you just need to load it up here you can load in work orders or so it's a relatively it's a really great little product so what i want to do right now is what i want to i want to be able to take specific invoices that have already been created like these pdf invoices here that i got folder and how to automatically convert them that means i want to extract the invoice number the invoice date the customer and all the items along with the total associated and bring it inside to these data tables so that we can automatically load it up so for example i've done this one already i'm going to delete it and we're going to show it again but basically this particular one let's take a look at this invoice number 12. so it started out with this inverse number 12 and then what i did is i simply converted it which is exactly what i'm going to show you today and i converted it to an invoice so when i load that all the items loaded up so normally we're printing to a pds this time we're going to take pdf so that means if your boss says hey i've got here's 100 pdfs of invoices i want to know what the total is or how many items did we use or how many customers or what was the total customers instead of going through a calculator one by one and adding it up all you need to do is put them all in a folder click one button and everything is in there then you can run reports you can edit invoices you can update invoices and do so much so it's really great training let's take a look at what i have in store for you well first of all we need a few folders that are going to help us out so let's take a look at some of the folder structure we have in here i've got here basically a few folders i've got some invoices i'm going to delete these all those are just for sample so this folder structure is going to be in our dropbox folder let me expand that out so you can see it basically what i want to do is i want to have four folders okay and in those four folders i want to do pdf invoices this is where the invoices will go to this is before they get converted right they're going to go in here once they get converted i want them to go in an invoice archive that folder is going to be here once they get converted what i want is our conversion engine is going to automatically create some data that excel can read that invoice data is going to go in this folder once it is read it's going to go in this archive folder this data archive folder so i've got four distinct folders set up in our dropbox dropbox is going to come in handy if you don't have dropbox now is a great time to get it because it is an absolutely free software and one of the best that i've been using for over 10 years so i hope you go and get that it's a free file sharing software and i think you'd be really happy with it it works great for what we want to use it for today okay so what we want to do is we want to make sure that excel and vba knows about these four folders and we can properly place documents and we can probably pull data from these four folders so we need to map those properly so to put those in i have just added them here this the rest of this was part of a prior training this is what i've added here that's the only thing i've really added in this other than a button everything else is the same with this application that's the beauty of it so we have a pdf invoices this is where our invoices will go before they are parsed and the data is extracted out from them we've got the pdf invoice data this is where the data goes when we want to pull that data and i'll show you everything about that where the invoices is the folder where they uh invoices once they get parsed they go into this archive and then we also have the data archive so pretty much those four folders is all we need to work with and those are the four folders that i just showed you okay so when you get this you want to make sure to create those four folders on your machine generally in a dropbox and i've got those in my dropbox okay so basically we're gonna start with this we've got three invoices and what we want to do basically is we want to put the information inside the database once they're inside the database here in excel we can then load the invoice now this particular database is really made up of two individual tables here first of all we have the orders database and notice here we've got our order id we've got an order date order meaning invoice now we call this one order not necessarily an invoice because in this particular application we have estimates we have work orders and we have invoices today we're only going to focus on those invoices so we have an order type this is static we're only focused we've got the customer i've got a service tech a job site and footer these are optional if we want those i've got a total and the estimate id and then a work order id and the invoice id we're just going to focus today on the invoices that's all we're importing so just the order id is the same as the invoice id here and we're going to so i want to bring this information in i want to extract that directly from our pdf our invoice id our invoice date all the items our customer that's associated here our customer here all the items the item name the description the quantity the price and the total and i want our total we're not going to worry about subtotal and tax today but just the total okay so that's all i want to focus on and i want to extract that and i want to bring it in so our main invoice data is going to come here well our those individual items as part of that invoice they're going to come here so we notice here we've got three items and now again we have that invoice or order id the type which is an invoice we have the item name right notice we've got the item name the description here just the way we have it we've got the quantity we have the individual item the total item total and plus i also have the order row now the order row this is that has to do with the order row that we want to place it on inside here so inside our orders we have order row 10 order 11 and order of 12. so that's the order in which they're going to go so that's kind of important and also what we also have is the database row this is the actual row of this database right here on the table and that's going to help us so we need all those things so what i want to do is i want to take all that pdf invoice and i want to bring it i want to extract all that data from the pdf i want to bring it into these two tables here and here once i have it in those two places i can easily just simply load it so all i need to do in this particular is just put that order id and it's automatically going to load what i'm going to do now is i'm going to delete this okay i'm going to clear this out we don't need this because i want to automatically bring it and i'm going to do the same thing for let's take 14 through 12. we don't need that i've got invoice in pdf i've got 12 13 and 14. so i'm going to clear that so if i try to load it now right if i try to load it it's going to let us know it's not found please enter a correct order because it's no longer in the database clicking on add new is going to clear all that out again we can't load it that doesn't exist here and that's what we want we just cleared it out but what we do want to do is take it from a pdf and to do that we're going to use a third-party tool something we did use a few weeks ago the tool worked out pretty well called pdf.co.pdf.co now in a prior training a few weeks ago what we did is we took receipts basically to copy those and we brought it in and that was relatively simple because all we needed was pretty much a receipt id the total amount of the receipt the name and the reference number and we just brought it in this time we're going to add on to that because we need to add in additional information including the order items right so we're adding a level of complexity here but it's also really really important because we want to be able to automatically transfer pdf invoices into excel and then not only just into excel but make them so that they're editable and readable and we can put data on them so we really want them in the right place so that's what we're going to focus on so how do we do that how do we take this invoice and do it well again we're going to use that third-party tool called pdf.copdf dot co super important and this particular one i've worked with them a little bit they're really flexible this particular document parcel we're going to use in beta now we it's a few small issues with it but i'm working with them directly and they seem to be really flexible the support is really good in fact they're so kind last time i did the video they offered us a bunch of free credits notice we got credits up here your free account will get you 500 credits but if you use like a non-gmail right you can sign up it'll it'll give you a lot more credit so something like a non-free like uh like a non-free type of email like a work type of email they give you a bunch free and i did discuss with them and they said okay hey if you put out a video we'll give your members this time we're gonna do 12 members get free credits free additional free credits the 12. so what are we in about 13 or minutes into this video so if you comment below saying hey i want the free credits and let me know either you can put your email or you can email me or just let me know so i make sure it's you i will send them a list of 12 people to get free credit so they're going to give you a bunch of different credits now keep in mind that if you are watching this early on patreon all right you're gonna have to wait till the youtube launch it's only fair because of course on our patreon everybody you get these videos to watch two days earlier and you get to watch see the workbook so it's a great way to on patreon but we want to make it fair for everybody so those of you on patreon who are watching this please wait until the youtube video about 13 minutes in before you comment on that on down below inside youtube and i can get you your free credits okay so that's up to 12 people all right great so we got this so what we want to do is once you sign up on pdf.com it's free there's of course there's pricing but but for our free purposes it's based on credits and this should get you a good start on just on the free credits to try this out so what we're going to do is we're going to focus on document parser right there's a lot of things they've got a lot of integrations in here we're going to focus on something called document parser okay i've done a few tests already so basically it'll say what do you want to do you want a new template and i've got some existing templates here so what i want to do is we're going to click on the new template brand new template okay and there's some instructions on how to use it and helpful so we can close that for now because i'm going to walk you through okay so the first thing what we want to do is we want to load or test a pdf pdf or image so we're going to do a pdf in this case and i've got this one 12 let's focus on this one since we were already working on that one so i'm going to load that up because that's the one we want and then what it's going to do is going to put it here and here's our invoice just as the pdf as we want it and all the data's here so the best thing to do here is you can let pdf.code look let their particular automation run and see if that we can get some type of thing so the best thing to do is just click on you can click on run template or in fact just click on this detect object detect objects here and what that's going to do is going to look for objects and kind of automatically fill it in at least based on the best and there's going to be 5 fields notice it did find five fields so let's take a look what it found it found invoice number and double check to make sure it found the right invoice number okay and if we can test this object here let's go down here and take a look to see if it found that and 12. it did find the right invoice number here and also the date issued right so we're going to call that the invoice date and we can test this one out here and see what date so click down make sure it found the right it did find the right date but i want it in a different format so i'm going to do mm slash dd slash yyyy okay and then again you can test it now we got now let's test it again and make sure that it converts it to the right format that's the format that i want to use inside okay it did very good so we also have the total we do want that i know it kind of goes back and forth kind of a weird thing there but let's test this object out and it kind of expands and decreases but only when you're hovering okay 8 49 52 check that out that is correct that's the number we want we're not going to use subtotal so let's delete that here and then click ok we're not going to use tax so let's also delete that here and click ok but what we do want is we want the customer name right so we've got invoice number data issue and we can let's just change the name of that i like something like invoice number okay and we can instead of date issue i'm going to call this invoice date invoice date okay so we give it some and then we'll just make this capitalize this just so that's uniform you can change the labels on these so it's really nice okay so that we've got that let's give it a template name we'll call this pdf to invoice pdf to invoice very good so we've got that and now what i want to do is i want to add two more things i want to add the customer name so we're going to add an object here and i'm going to add a field from a rectangle selection so i'm going to i'm going to click that and i'm just going to add this rectangle selection right about here and i'm going to drop it down here and i want to make sure it's long enough so that it covers any type of customer here that might be along so i'm going to put that up here and then what i want to do is i want to test this out is it working or not so i'm going to click on here test this object and then we're going to scroll down and make sure that it recognizes that name fred freders we cannot have a video without that fred here he shows up everywhere fred okay they did find fred good so we've got fred we've got the invoice number we've got the invoice date we got the total but we do need to add all the data in the grid so that's what we're going to do now we're going to click add a table based on a rectangle selection rectangle selection so it's going to give us that rectangle and what i'm going to do is i'm going to position that right over all the items and i'm going to zoom out a little bit here i'll scroll up a little bit and then i want to compass the entire table so i'm going to go all the way up including the top headers in this case and i'm going to go all the way down to the bottom okay and that should be good right about there i want to make sure it doesn't get the subtotal so i'm not going to include that and i want to see if this is going to find all of our information so i'm going to click test object here just as we did and then we'll take a look okay let's take a look it's got items now it did find our top header so we don't want to make sure that when we import the data we're not going to import the first row because that's just our header so that's fine not the first row but let's check we have five columns that's correct we have the silicone we have all the items named the three items the quantities we have the price and the total that looks pretty good okay and what i will do is i'll give this a name i'll call this invoice items invoice items okay so we have all that that looks pretty good now we've got everything we've need properly mapped out but what i want to do is i want to upload another invoice and make sure that the other that other invoices are now this particular works great but if they're all the same types if you have a different type of invoice you want to create a template based on that type so what i'm going to do is i'm going to load or test another that's 12. so let's look at 13 here and i'm going to click open and then all we need to do is click run template and we're going to check that out and make sure it contains all the data okay so we have our invoice number we have our invoice date we have our total let's check that total is 3 46 12. we have our customer name that looks good okay and how about our items of course we have the top row which we're going to extract which we're going to leave out of course when we bring in the data we've got all of the rows of data okay the last one i want to do i want to close this out and do one more okay that was i believe 13 and now we're going to do 14. so 14 and then again run the template again just to make sure that all the data gets getting extracted properly okay let's take a look at this 14 the date the total and our invoice items this invoice only has two items two items okay so it's perfect it only got those two items good i like this the way it is so what i'm going to do is i'm going to close this out i'm going to click save template and return save template we've given it a name and what that's going to do is give us a list of templates so i'm going to be focused on this one pdf to invoice pdf invoice okay so that's good we have our template set everything's good we're confident with the way that it extracts data now what we need to do is we need to get some automation what i want to do is when i put a pdf inside this folder i want it to automatically upload to this so this one i want to send this link to this engine to the api engine and i wanted to convert it and then i want the data to come back into dropbox okay so we do that of course with a macro so the first thing what we want to do is write a macro that's going to check this folder if there's any pdfs in this folder i want to upload it inside dropbox so we need to write a little bit of code to do just that it is that code that's going we're going to assign to this particular button right here so we're going to do that inside the developers and vba is where we're going to write our macro and i've already dimensioned some variables to help us move things along okay let's go over some of these variables we're going to demand we need the invoice database row all right i need to know what row to place that so we're going to press it on the first available row so it's going to be the invoice database row so we need that inside of variable i also need the invoice database column we're going to be looping through the columns going from 1 all the way to here going from 1 all the way here so i need to loop through the columns adding data so we need that inside of variable 2. we're going to do the same thing for the invoice item row invoice item column again based on the invoice items we need the row and you know which row we're going to be placing it and we also need to place a need to loop through that so we're going to use a variable to loop through the columns going through one okay so we've have that i need to know the order row right or the row may not need that order i think it's going to use invoice database row so the pdf count i want to know how many pdfs we create and i want to know how many data you know how many data sheets that we use and i also need to know the invoice item right we're going to have invoice items inside an array so we're going to need that i need to know the pdf invoice folder what folder is that and i also need to know the invoice data folder remember we have those folders that we've created and i want to know the invoice archive and the data archive so they've got four different folders so we got four different variables to cover those particular folders also i need to know the file the data file path what is the file path of that entire data file and a specific file name that we're going to be working with when we work with those and also the database path as a string that database path is going to come in handy okay also i want to know the invoice id we'll put that in as a string it could be a long variable but in this case we'll use a string invoice data as a string and then we need a few arrays that we're going to be working called the invoice data array we're going to be using text files we need to create an array from that and then split that up and then run that okay also i need to know the main invoice data as a string main invoice data we'll go through these as we write the code and also the main invoice data so basically we need to create invoice data and also we need table data here and table data array so we're going to keep that and also the road data as a string the row date array and url we're going to be sending something over using a url and using it as json so we need those as strings and we need an object http okay so that's those are our variables the first thing we want to do is the most important thing right before we can even get started we need to make sure that we have proper folders right if if you get this file it's probably going to be blank and if it's not if i forget to remove these certainly these file paths are not going to work for you so we need to make sure in vba that all these file paths are correct starting at j28 going all the way through j31 so that's what we're going to do inside the code so we're going to write some code the first thing is the pdf folder pdf folder actually we have the we have a few different ones so let's do pdf invoice folder is going to be equal to what admin dot range and it's going to be that first cell j28 dot value so dot value and i also want to add a backslash onto that okay so and a backslash okay i want to add that backslash onto it that's going to be the path of that folder but i need to check that so if the directory of the pdf invoice folder and then vb directory equals empty or maybe the variable is empty or pdf invoice folder equals empty or double quotes then let the user know what are we going to tell them message box please set a correct invoice folder in the admin screen in the admin capitalize that admin screen and then exit the sub we can't move on unless we have a correct folder path now we're going to do something similar for the remaining three so i'm going to copy and paste this down here here and here okay so but their folders are different first of all this is going to be 29 this will be 30 and this will be 31. and the variables are going to also be different so we'll need to update those variables accordingly okay so for this one right for the invoice folder this one we're okay let's put a little note here so we know which one it is this would be the in this case pdf if you can't read the invoice pdf invoice folder check i want to check that one next up what i want to do is i want to check for in this case the invoice data folder so in this case we'll call this invoice data folder we need to update of course the variables within that bit of code so it's this we're going to copy and paste it here invoice data folder and also here as well so there we go so those three one not there the whole one there this one here okay pasting that in there i want to make sure that the invoice data folder contains a value so again we'll call this invoice data folder check that's where the data goes when it gets converted from it pdf invoice okay so we're checking that next up i want to check the invoice archive right invoice archive that is where the invoices go once they have already been converted you know it's archive and of course if you're not sure of these variables you're not sure you got to write just lowercase if it changed the uppercase then you know you've got it right so i'm going to copy this here and i'm going to paste it here and i'm going to paste it directly here and of course this one's the invoice archive so we'll call this invoice archive check and then lastly i have the data archive right data and that's where the data goes when it's completed so we'll call this data archive okay again small letters because i'm not sure if i got the variable right just in case and it goes to code data archive in this case where the data goes once it has been converted i'm going to paste it and update those accordingly here and also here and this we're going to be is our data archive folder check okay so once we know we've got the folders are all correct and the paths are all correct i want to check that folder i want to look inside this folder right here this pdf invoice folder i want to see does it contain any any pdfs if it does then we need to loop through those so again let's write here check for pdfs to convert so i'm going to set a file name that's a variable already it's going to be equal to the directory of in this case pdf invoice folder and right and then what we're going to do is what else any file that basically ends with dot pdf so we can do the asterisk which is the wildcard dot pdf close that okay so we're going to focus that means any file that i want to set that file name to any file that ends in pdf okay that's just to check so now we're going to run a loop it's going to be called do while loop and this do while is as long as the length of the file name is greater than zero because as we loop through those we're going to be clearing out the name so this is the start of our loop start of loop okay because we need to loop through all the files i want to do this for every single pdf inside that folder so what are we going to do inside that well the first thing what i want to do is i want to get that full file path that full path data file path right is going to be equal to that folder right and the file name so the combined together file name full file path of pdf right we need that that's very important and now what we want to do is i want to know what is that excuse me i said it was database path but it's really dropbox i should change this dropbox path right what i want to know is what that dropbox path is and what does that mean that means that i only want to know the portion that is after the dropbox right so here's dropbox right so what i want to know is once we put it inside right once we send it to dropbox to create the link and i'll show you what that means basically it's going to ask for this like it's going to be something like this and then we'll call it invoice something like this dot 12 dot pdf right so what i'm going to be looking for is just this part right here that starts with drawbacks so i need to extract that including i want just this part right i don't want anything else so i need to extract this part and i need to remove this part from the full here's our full file path so i need to remove this portion of it because when we convert it to a link to do that inside we're going to use integromat and inside that all they want is this part so i want all i want i don't want this because we know we're using dropbox right so once we upload it i only need this part so that's exactly what i want to do is to do that and we can do that with a few line of code so what i need to do in this case is i need to look for the word dropbox and remove everything to the left of that including including the characters that go with that so including those 8 characters or seven characters or whatever they are i want that here so to do that we can just write a little bit of code that dropbox path okay so let's write that in right now so the dropbox path is equal to we're using the replace command and i also want the right i want to know the right of that we're going to use the right command and i want that full data file path data file path and we're going to use the length i need to know the length of that data file path okay in this case we're going to say it's going to subtract right we're minusing or i need to extract i need to remove certain amount of it so what part are we removing well we're removing the part that says dropbox right so how do we get that well we can do minus we're going to look for that prop in string and then what are we looking for data file path right we're going to look for entry and what are we looking for i'm looking for dropbox drop box that's the word i'm looking for inside that path and what i want to do is i want to subtract 6 from that so how do i do that well we can do minus 6. so that's going to be this first six part but what else do i want to do i also want to replace right i also want to look for all the backslashes and i want to replace them with forward slashes and i'll show you why in coming up in instagram but basically it won't recognize the backslashes but it will recognize the forward slashes so what we're going to use the replace command and i'm going to look for those backslashes and i'm going to replace them with forward slashes just like that okay and so that's it that's all we have to do again let's go over that we're replacing this part of the replace covers this basically all we're replacing is the backslashes in that file path with forward slashes because that's going to come in handy okay we're going to get the right i want to extract this the data file path i only want the right portion and i'm going to look for dropbox and i'm subtract 6 because i don't want to include the word dropbox or the path so i'm extracting six and only what i want and we'll put a little message box here to make sure we have the right message box db path okay so that's it now let's just go let's just run that so far and see make sure we have the right path exactly the way we want it so what i'm going to do is i'm just going to inside our folder here i'm going to add one single pdf here copy that here and put it inside here and i'm going to paste that in there now what i'm going to do is i'm going to run the code up until now and just run it here so that's all i want to do and this is what our result is back uh would be forward slash pdf to excel right pdf invoices invites that's exactly what i want right i don't want anything else so that's exactly we're good to go on that okay we've got to delete that let's break that error since we're in a loop we don't need that all right we're in a loop but that's okay we don't need that right now okay but basically we're not going to loop it out once we remove clearing that file name right we're going to do that at the end of that we'll get to that in a moment okay but everything's right so far we don't need the message box let us continue so now that we have that what i want to do is i want to send that and i want to send that information to integer mount and we're going to get into that in just a moment but let's build out the ability to send it to them so the best way to do is we're going to do is do this set object http right it's going to equal we're going to create an object i want to send it using of course http so to do that we're going to create an object and what type of object are we going to create well it's going to be an msxml2 dot server x mlhttp so that's basically we're going to set that object basically it's going to allow us to send that information via a web hook create object ms xml2 dot server xml http okay so now what i want to do is i want to we i need to know what we're going to send to them well basically i want to send that url right i want to send that message by that information but we can do that with a url so we're going to use url is going to be equal to and we're going to send a web book we don't have that web hook just yet so we're going to put that in right in a moment right and then what i want to do is after that web book i want to put a question mark and then what i want to do is i want to put in that label what is that label we'll call it pdf link that's any label we want to a equals what it's going to equal and whatever that drop box path dropbox path because that's very specific for dropbox so once we have that right we've got that dropbox path that is we'll call that we'll call that the dropbox link includes dropbox so and of course this part remember right before this question mark we'll be adding that in just a moment okay so but i want to add a little bit more in before we get to that part so what are we going to do well now what we want to do is we need to send that that information so we're going to use in this case object http again we're focused on that dot open we're going to use open in this case and i want to know what we're going to be up and we're going to use the get we can use get post in this case but get dot url false okay so we know false next up what i want to do is i want to set the requested header what is that header well it's going to be content type application json so how do we do that again we can copy this because we're going to be using it a few times object http in this case we're going to set the request header and that request header is going to be in this it's going to be content we're sending it json type right content type that's the label and what is it type is going to be application backslash json okay so that's our header type header and next up once we have that header we want to send that information so this could be basically dot send and we're going to send it in the json format okay we're sending that send information send link detail okay so we're sending that over basically we're going to send it to integromat we're going to send it actually we're going to send it to dropbox and then dropbox is going to create a link for us and it's that link that we're going to use like a download link and i'll explain that for you in just a moment okay so once we have that what we want to do is before we end the loop we want to clear out the name file name this is how we get rid of the loop file name equals directory right and then just empty this clears what this does is it clears out current file name so it can loop through other files right if we have just file to clear it out what i want to do after this is actually we'll stop at this point right here but what we're going to be basically doing is we're going to use a weight now and but this is okay for now with the rest we do need to get that link let's get that link so we can actually send this information so we're going to do that we're going to do that with integromat another great application let's get into that and it's called integromat now we've been over this a few times if you've watched my videos if not sign up for the free integral mat intake or matt i'll include a link down below and it allows you up to about a thousand operations per month so it's really cool as far as a free one and of course you can pay if you want additional or you're using it for business purposes what we're going to do is we're going to create a scenario so i'm going to click create a new sorry once you get logged in you'll call create scenario and this scenario is going to use about three different components and you can put the common components down here so we can do that so if we do search in the first thing i want to use is a web hook you can also search here and click here but let's search here so we can easily access them so webhook is the first one we're going to be using and that's right down here webhook it is that webhook that we need and we're also going to be using pdf.copdf right that's important one.co and let's type that in here the search kind of a little bit quirky huh pdf there we go dot co that's the one i want pdf dot co and of course we're using dropbox too so let's type that in dropbox these are the common ones that we're going to be using and notice it doesn't search unless i go out and back in notice that drop box okay drop box if i can spell okay there we go so now we have our most frequent ones here and these are our favorites that we're going to do so the first thing what i want to do is i want to create a web hook notice your favorites will come here and that web book is going to be called a custom web hook this is what we want to do so we're going to click add and we'll call this let's call this pdf2 invoice and we don't have any ip restrictions so we'll click save and what that's going to do is going to create this web that is what we need to copy so we're going to click copy on that one then what i'm going to do is i'm going to go into our code i'm going to paste that right before this question mark here saving our work as we've done so far and now we're ready to run it so basically what i want to do is when i run this code i want this thing to go to green and i want to make sure that it's been received i want that to send that web book so that's what we're going to do right now we're going to run this code here and let's fix this part here definitely not a herder unless you're in cows or something probably a header and continuing on all right and of course we've got lots of things set not setting it sending it okay only read a little bit of code but make enough errors okay so that's of course we're sending it or outstanding it and then continuing on okay so we fixed those up now what we want to do is we're going to check that now it's gone to green it's successfully determined that that's exactly what i want to happen right i want to make sure it's recognized i'm clicking ok all right so now let's continue a little bit on with the code and then what we're going to do is we're going to be able to send that so to do that let's we only have a little bit more in this portion of the code so what else do we want to do well the first thing what i want to do is i want to give it some time now in my further in my tests i know that this next portion when we send it to dropbox and then we send it to pdf it takes about 30 seconds right and i want a single macro that does everything this macro is going to do everything and that means we need to wait for that conversion to happen we need to wait to send it to the api for for a pdf dot co we need to let it convert it and then we need to get the information back into dropbox all that's going to take about 25 seconds so what i'm going to do is i'm going to put a weight now and i want to wait that amount of time so to do that first thing what i want to do is do application wait so application dot wait okay when do we want to wait we're going to wait now plus i made about 30 seconds so we'll do plus time value and then of course we don't want any hours and i don't want any minutes and i want about 30 seconds so we're going to add 30 30 seconds onto that okay so let's put a comment wait 30 seconds for conversion so now that we've waited conversion now what do i want to do well basically what i want to do is i want to take this file path that basic invoice right and i will put it in now that it's converted we've uploaded it so we know it's in here i want to take it and i want to move it basically to this invoice archive i want to move it here we've already used it now keep in mind that what i want to do is this to send a dropbox i want to basically right click this you know not and i want to create a dropbox link so copy dropbox link that's what we're going to do with the api because it is that link right let's take a look at what that link would be if we see it's going to link it's going to look something like this except it's going to be a one at the end of it one means the down button so what we wanted we're going to do through aps i want to send this link to pdf.co because this link is going to allow them to take that pdf and convert it right we need to tell it to do that so we're going to have dropbox automatically create this download link for us so that pdf.co can automatically upload that and then convert it so that's exactly what we're going to be doing but what i want to do is once that's done we can then move that invoice from the invoice folder into the archive so let's do that but if that invoice is already inside the archive it could create an error right if it's already inside this here it could create an area so i want to if it's found there i want to delete it with the same name okay so we can do that with this with an if statement if the directory of the invoice archive let's do that archive and then and right we need to do that and the directory right i just want the name data file path right that's going to extract the name right dot vb directory i'm going to check if it does not equal empty does not equal empty then what do we want to do then basically i want to kill it so what are we going to kill pasting that in there killing that and what that's going to do is ensure that it is not if exists then delete very good so now we've covered that now continuing on now that we can know it's there now we actually have to pay move it there so how are we going to do that well first thing what i want to do is i'm going to use the save as and that's basically going to move it there and so we can use in fact this case what we'll usually use file copy what we'll do we'll first copy it and then we'll delete it file copy what's where we it's going to come from data file path that's our source and what's our destination our destination is this invoice archive and path right and basically you're going to copy file to archive folder to the archive once we've copied it over then we can delete it right then we can kill right basically so it's going to basically copy data file path okay very good and also what i want to do is i want to run account i want to know how many pdfs that i'm converting to do that we could just do we've got a variable long variable called pdf count so we're going to pdf account equals pdf count plus one count pdfs converted so now that's the end of our loop so that's all we need to do okay so i'm going to of course save our work right and now we have make sure that we have a pdf in that folder right inside our folder here pdf invoices here okay good so now we have that but let's continue with increments so now we've finished that portion of the macro so we get this web book and what do we want to do with it i want to send it to dropbox and what's dropbox is going to do dropbox is actually going to create or update a shared link that's what i want to do just like that link that i showed you so where are they going to get that folder from what do we want to select okay once you add your folder i've already added my dropbox but you'll have to connect your dropbox using add but i've already done that so what i want to do do i want to select a specific file i don't want to map it why is it going to be mapped and it's coming from this webhook and what is that file path now here's now look at this here you see this path right here this is what i'm talking about this is why we had to replace our backslashes with forward slashes this is why we had to remove that dropbox because all that it's looking for is the back slashes plus the file path plus the name and that's using forward slashes not backslash so that's why we have it so we know we've got that it's coming from that label here's the label that we gave it pdf link notice that that's exactly the same label that we gave it right here pdf link right so we know here's the the label the field label here and here's the value so it's the value we're sending it over so again it is this value that we're going to place directly inside here so pdf link there and the rest is it public link links expiration date we don't need that the access level and click ok now that we have that what else do we want to do so i'm it's going to create that link and now what i want to do is i want to take that link that it just created and i want to send it to pdf.com and once you connect your pdf.com what do we want to do well what i really want to do is i want to parse a document right we've already created it but i've created a template i want to parse the document so i'm going to click parse document and so what do i want to do in this case i want to import a file from a link we have a url it's coming from dropbox so we're not going to upload we're going to import from url and what is that url that url is created by that dropbox and it's going to be called that download link right download url right here that's the one we want so we're going to map that i'm going to put that download here i only want now we've got a document so here's what's the link of the url this is the url that we created that's why dropbox was so important in this case because it actually created it for us now we have to know what document parser template id we just created a template here called pdf to invoice remember we created that's one and notice it's got an id number 1921. that's the id that we need to use 1921. so i'm going to put that id right in here 1921. okay our output file is going to be json right we remember remember it needs the number of seconds so we're going to put about 15 seconds but really it takes about 20 22 23. okay profiles we don't need any don't need to worry about anything with that and the output is going to be json cool so let's click ok and that's pretty good so far so saving our work here saving here and what we're going to do is we're going to just this all auto align it so that's going to be a good start basically what we're going to do is we're going to upload a link we're going to convert it to a public link and we're going to send it to pdf2go and then we're going to check it out let's take a look so now what we've done we've saved it of course we can give it a name here if we want to give it a name we'll just call this convert pdf to invoice good so now what i want to do is i'm going to click run once and what's going to do is going to listen for it so now as long as i have that pdf in this folder here which i do oh pdf invoice let's put it in there copy that and then put it inside here i'm going to put that one in here the pdf folder now what i want to do is i'm going to minimize that and i'm going to go and i'm going to run this macro right here okay if there's any issues and now what we're going to do is minimize that here and take a look inside here and it ran already it ran here and now it's currently running through remember it takes about 15 to 20 seconds or something so it's running here and we're going to check all the values of that run once it's complete and i'm going to go step by step to see exactly what it found and what it had so it's running that and everything looks good no errors no issues okay we can close that out so again let's take a look inside here our output of our web hook is this pdf this is what we said here's the label and here's the link that we sent it it's sent to dropbox now what did dropbox get dropbox got that link and what did it do with it link it created a download link and what is that link file download it is located right here this download url that's what it created its job was just to create that download url here inside pdf what did it do let's take a look inside here and it got that import options here it got here's the import it got that import download and it came from a url great now what was its output what did it get okay let's take a look inside here the output here the name is invoice 12 based on what is the body okay let's take a look here we've got outlook time alec value objects value okay so again body keep that in mind objects value not objects objects is generally time for it but objects value invoice number 12 invoice notice these are the labels that we created inside the template the 12. invoice date we have the total and we have oh we didn't add a customer name to this but we'll get to that rectangle 12 let's do that right now that should be important i'm going to edit that that should be customer name we didn't give a name to that but that's okay rectangle 12 here let's give that a customer name we should want to give that customer name okay good so we've done that saving and returning all right so that's fine so but the values there that's the important thing and so what else do we have now we got to focus on the rows let's take a look at this the row the first row of row 0 items description quantity well we know that but we don't need that but that's okay we're not going to import row zero right we are going to start out with row one let's take a look at row one column one bath and vanity cabinet the quantity the column four is of course the price and then the total same with columns so all that data got brought in all of it is here perfect so now what i want to do and here we have invoice but this is all we need right i have so all the data came in now our job is to take this and put it into a text file and bring that text file back and then excel can read that so how are we going to do that well what i want to do is i want to create a text file so i'm going to add to this right and now what i want to do is i want to go into dropbox in this case what i want to do is i want to create or overwrite a text file create or overwrite a text file so i'm going to add that here and so what i want to do is i want to override this so overwrite okay and i'm going to use a map will be selecting a file we're going to map a file we can select a file right and then sometimes it what we'll do is we want to know where we want to put it so i'm going to put it basically inside pdf to excel and then i want to put it inside where i want to put it i want to put it inside the invoice data folder that's where i want to put it so we have that so that's where it's going to go but what is the file name but it's not going to allow us to do that so pdf to excel invoice data is where we want to send it but instead let's map a file so we can assign a distinct name for it again so we're going to do something and if you're not sure all you need to do is just make sure that we're putting it directly inside here we want it to come into invoice data so this is where i want it to go right here so pasting that exactly where you want it to go so but this case we're using of course this time backslashes here right forward slashes here invoice data i want to give it a unique name a distinct name and i want to make sure it's a txt file so we've got template name we can do this pdf to invoice but i don't want the template name i want to use something unique right so we use the file name this case we can use dot pdf as long as we end it with dot txt it's fine we've got a few other names here so for example if we want to do a name here we can do objects value but let's do this let's do let's use the file name pdf but i want to make sure it ends in dot txt because it's a dot txt file that we are creating and also what i want to do in this case what is the source file the source file i want to map it i want to put those file contents in what are those file contents well it's going to be based directly on our data tables right so the first thing is our based on our order database so what i want inside this i want to put that order id then i want to put the order date and then i want to put the type in this case it's always going to be invoice so it's fixed i'm fixed in this case so let's do that let's add that in right now first thing is i want to get that invoice id which is this invoice number here i'm going to place that directly in here now i need to separate that with the next field then you need to use the delimiter and we're going to use the asterisk as a delimiter the next step of course we want to put that date that invoice date i'm going to use another delimiter and then what i want to do is always going to be invoice right this particular type so i'm just going to type in invoice and then what i want to do is type another aster so i can separate it and let's take a look inside our database in the order we have the customer name so we want to do the customer we didn't assign a service tech a job site or footer so we want to create three different blanks customers going to come next okay so in this case let's do this let's add that in here so in this case our customer table in this case we also have how many times did i say in this case where is it going to be it's coming directly from here rectangle three we've renamed it so let's what i want to do is probably going to run that again it may not recognize it now because we have a different name on that so let's click ok right and what i want to do is i'm just going to right click here and i'm going to unlink this and i want to run it one more time and i want to get that updated field so if you miss a field like we did i want to update it so we can get the right field so i'm going to click run once here i'm going to have it convert one more time so again our empty is blank that's fine i'm just going to copy anything here and place it directly inside or in pdf invoices here and paste that in here okay now we're ready to run our code here let's just run our code one more time make sure we get that updated label coming back in here okay we're going to update that let's take a look okay and now we see that updated one going and we'll check to make sure that that updated field name gets converted instead of label three we wanted to set it to customers so we'll let this finish and then we'll reconnect it again to make sure that that updated label got set in once it finishes all right great now that it's finished we're going to take a look at this that of course we want to look at the outport right we want to make sure the body here template object's value here and now we have customer name that's what i wanted to put in there now we can reconnect it right i didn't want it happening i want to reconnect it there go back in here right go back in here and now what i want to do is there we go now we've got insider objects value we're looking for customer name right i want to relabel that so i want to make sure that that label okay so after our customer name here inside our orders database we have service deck job site and footer now we didn't add these fields in which we easily could but you get the point so what i'm going to do is i'm just going to create some blanks so let's do that inside there so first the astro separate then i need one asterisk to separate another one and another one okay so that's going to create three one two three individual spaces that's what i want so it's going to skip those after that what do we want well of course we need the total so let's put in the total we have that already here inside our objects variable we have our total so i'm going to put in total then what happens after that then i'm going to have estimated id and work order we can skip those two and i want to put the invoice id one more time so again two blanks and then the invoice id into here so again one blank here and another blank here that's two blanks then again i want one more time that invoice number here okay let's go over that again we have our invoice number then we're going to put in an asterisk then we have our invoice date then we have a static invoice right we want to put in the invoice then we have the customer name then we have three blanks we've got the total we've got two blanks and then we've got the invoice number that's perfect okay that covers all the header information but what about our invoice table we still need to do that now i need to differentiate right between our table items and our header items right so i need to have some kind of a different delimiter in this case okay so why don't we put something very unique let's do backslash it could be anything as long as it's not a common right we'll do ti and then another backslash okay so that means after this point is going to be all their table information okay so basically our table information is going to be all the listed information so where is that located of course our first one we don't want to include the item description these are our headers we don't want to include that but we do want to start with our rows so let's go ahead and put that in right now i'm going to put in column number one now keep in mind there might be a way to aggregate all of this into easier but that's going to take a little bit more time so in an array so but if you guys experts can figure out a quick way i'm sure there is maybe the next time we update these we'll figure out a quick way we can automatically update all these rows we're going to do it the manual way a little bit it's just a few more minutes so basically i want that first column row that description in column one then i want to separate that by an asterisk then i want that second one separating by an asterisk the third one that's the quantity separating by an asterisk then i want the fourth one separating by an asterisk and then the fifth one separating by action okay again so row one column two row one column three row one column four and then row one column five we've got them all but now again i need to separate i need something distinct that's gonna show we're at a different row right we're starting a brand new row so why don't we use something similar like backslash or forward slash in this case r and then r that's perfect that'll differentiate when we start a brand new row so now all we need to do is just to do the second row now we're on row two again column one relatively easy separated by an asterisk column two asterisk column three asterisks column four asterisk and then column five and then asters okay perfect i like that again brand new row r so all you need to do is just duplicate this for each of the rows and you're good to go okay the last one we'll do and that's going to be our row three so again all we need to do is again do our clerical one asterisk column two asterisks we're going to double check this three asterisks four asterisks and the last one five estrus and the last one five we don't need an ending asterisk okay so let's just take a quick look at our data so we've got here row we'll go run column one two three four and five of our first row one two three four five of our second row separated by that row i think we don't need this last answers right so we don't need to make sure that we just need the row like this one don't need the last answers just this to separate the rows then we only have an asterisk again one two three four and five perfect i like that way click ok that's going to save it okay again saving your work here okay so now that we have that we've got that all brought in it's going to be brought in to this invoice data file we're going to map that file we're going to bring it all the way in okay let's take a look why don't we run this and make sure that that text file and see what it looks like and make sure it ends up in there so i'm going to click run once here and then what i'm going to do is i'm going to again transfer invoice into that i think i'm going to refresh that that's going to be gone so i'm going to copy this one here i'm going to paste it directly into our pdf invoices here into that folder where it's going to be looking then what i'm going to do is i'm going to run that macro up until that point okay so i'm going to run that macro here and then we're going to take a look inside that and make sure that that macro is recorded that's recognized okay and there it is all right and so as it's moving through taking about 20 or so seconds it's going to create that and then it's going to transfer the dropbox okay let's take a look inside here and then we'll take a look inside dropbox again we have our input all the information our output let's take a look at our output here that looks good pdf pdf invoice text files got created let's take a look inside our pdf and our invoice data file here this is what just got created so let's take a look inside that file and see to make sure we have everything we want again we have our invoice number we have our date our invoice our static term our customer we have one two three blanks we have our total we have two blanks one two our total then again we have a duplicate of that invoice number we have our table our table is going to start here our first information here we've got a ceramic bathroom sink here perfect i think we're missing an asterisk right here we are we are missing an asterisk there okay sync quantity here remember we have the item description so it looks like we missed an answer let's double check that i see we did miss an on the first one so all we do need to do is check our out right after our first one to see that we miss an asterisk column one here oh no it's right here here it is okay so ceramic bathroom see we've got our asterisk and everything looks good again total and then we have our next row right our plies here's our next row then we have that our asterisk our information our quantity here our amount and our total and our last row so everything looks really good now all i need to do is have excel read this through some dba parse it properly and place it in the right place so that's what we're going to do right now so we're going to continue on so let's continue with our macro and focus on the last portion of our foot macro so we can do that again we're going to start a brand new loop this time we're going to check for text files in that specific folder so again check for text files to import invoice data we're going to do that with a new loop again this time again file name equals in this case directory of what our invoice data folder different folder here and again we're looking for different types of files what are we looking for i'm looking for text files this time and dot txt files that's what i want to look for we're going to also run another do while loop do because i want to do it for all the data files in there do while the length of the file name same as we did is greater than zero okay again that's a good start of the loop start loop okay so now that we've got the start loop do while file name is greater than your length of the double double parenthesis here length we don't need the one at the beginning of length okay get rid of that okay so now we have that so do while so what are we going to do the first thing we want to do is of course assign that path name just like we did before i want to assign a full file name to that path so what is that it's going to be this case data file path is equal to again our folder invoice data folder and also i want the directory the directory of that name so what is that file name is going to be that file name that we had so that's going to complete the complete file path of the text file okay so we've got that file path now what i want to do is i want to open that open it of course it happens very quickly you don't see it it's behind the scenes that's why i love working with text sure we could bring this in an invoice in another excel file i mean but that would take longer so i like using these much easier to work with text files at least much faster you can do hundreds in seconds that's why we work with text files data file path we're going to open that file path for input as number one okay so that's going to open that text file and then line input what i want to do is number one i want to assign that a variable so number one is going to be equal to invoice data that is our going to be our string variable called invoice data so all of that data is going to go in a single stream everything you saw in that file is going to go into that single string once we have that what i want to do is simply close it close number one okay so that's going to close the file so all we do is open it we put everything that entire string inside this invoice string create string it's called string of data so now we've got that so once we have it we can then move on i want to add that ready to add the invoice data so the first thing what we're going to do is we're going to take that and we're going to parse it right i need to separate that let's take another look inside this thing and so what i want to do is i want to separate it they're going to start separating into two portions the first portion is going to be based on this file here ti okay so wherever i find this ti i'm going to take this first portion here i'm going to separate that that into a string everything else is going to be into another string this is for the table this is for the main invoice data okay so different so i want to separate that to do that and we're going to use this unique text to do that right there so we're going to split that and that's all we need to do inside here so that's what we're going to do first inside vba so to do that we just can use the split so i'm going to use this case going to set the table data in this case called invoice data array right that array here is going to be equal to again i want to run that split what's that split going to be i want to split that into two i want to split that into two okay so we're splitting it what are we splitting the invoice data what we just got and what are we going to split it by that ti ti that forward slash ti forward slash that's what we're going to split split string based on main invoice data and table data hey bold data so we're going to put it into two different sections okay then we can work with each individual one so what i want to do is that what about that first one that first one using that array is going to be called the main invoice data that in a string already equal to invoice data array that array that's the first value the first portion of it remember there's two portions the first portion always in an array always gets that zero assignment that's our main invoice data right there's two parts the first one is zero inside array and the second part is one main invoice data and what about the second part let's call this table data that's good enough equals again simply the second part here just this second part here so how do we do that is simply this invoice data and then one right let's change this to one this is our table data so we let's get rid of that this is our table data called table data okay now we've separated into two different strings we can work with them individually so to do that with the main invoice data i'm going to split this even farther right i want to take this let's put it up here i want to take this now that we have this in a string all the way from here all this industry i want to split this even further i'm going to split this based on the asterisk so we're going to use the split and our asterisk is our delimiter so to do that we do that here we're going to assign it just one more label here and that's going to be called the main invoice data and it's going to be an array is going to be equal to split what are we splitting we're splitting that main invoice data and what are we splitting it by we're splitting it by that delimiter we created that that asterisk there there we go not the 8. that asterisk okay we're going to call this split main invoice data okay and also remember we also have the table data we have table data array i want to create another one based on the table data i want to split that in this case 3 but i'm going to use another split remember now that we have the rest of it right so now what we have is all this i'm going to split this one more time but i'm going to split it based on the number of rows so i'm going to use this r here to split it in this case it's going to be 1 2 and then we have another one here so i'm going to split it into three sections using a split and using table is going to be equal to split what are we splitting here our in this case table data and then we're going to use that r that's going to split it based on the rows split table data based on rows okay there was a lot of splitting and some array so let's just go over that again i'm going to extract the entire string this entire string everything you see here into one variable once we have that we're going to then separate the main invoice data from the table invoice data into two different sections again that is this here based on this ti we're going to separate the first section and the second section all based in so then we have two sections then what i'm gonna do is i'm gonna do a third split i'm gonna take this table i'm gonna split it based on the number of rows so i'm gonna be row one here and this is gonna be row two here and then we're gonna have row three here so we're gonna and then that's called the table data array splitting it based on the rows once we have all that i'm ready to add inside our main invoice data so add in main invoice data okay to do that we just need to add know where to add it so where are we going to add it here it's going to be in their orders database so i need to find the first available row inside our order list and place it in this case it's going to be row 15. so to do that we need to get a variable so we can say the invoice database row is going to be equal to the orders database that's the sheet name range a and then 999999999 dot end excel up dot row okay so that's going to simply dot row and then plus one we want the first available row first available row okay so once we have that first available row then all we need to do is just run a loop okay but i'd like to put that invoice id inside a variable we're going to be using that invoice id a few times so i'm going to call this invoice id is going to be equal to now how do we can extract that invoice id that invoice id is right here it's the first value inside our array so that first value is going to be zero inside an array the number the number zero zero item number zero so we can pull it so it's gonna be the main invoice data array in this case zero it's gonna be set that invoice set invoice number into variable so once we have that variable now we're ready to do that so it's very very easy all i need to do is run a loop now i'm going to run a loop from column number one all the way to the last column what is that last column that last column is 11. so i'm going to do 1 to 11. and i've got the invoice database column that is going to be our variable so we could do 4 invoice database column is equal to 1 to 11. and then close our loop next invoice database call so with that we're just going to place that data so we're going to place it where inside our orders database dot cells we're using cells because both the column and the row are variable the invoice database row and then the column invoice database column dot value is equal to what it's equal to basically we're going to use the variable inside that array so we can do that very very easily and now remember right our array started zero but our row our columns start in one so what is our array it's the main invoice data and then that array here is basically going to be our invoice database column minus one y minus one because when we start on one right our invoice database column starts on one we need to in our the value here is actually zero the first one's zero and then it goes up so we need to subtract one it's going to add main invoice data to table that's it just one row will add all those 11 right and the ones that just have asterisks of course they're going to be skipped right remember we have some with just asks like here here and here they're just going to be skipped because we have those asterisks there okay so that's going to add all the data good we're that's done for the data all we need to do is add in our table data now so that covers it so add in table data so to do that now we have it first of all i need to know what row again just like we did here i'm going to copy this here and i'm going to update this so in this case we're going to say the in this case invoice item row is going to be equal to not the orders right this is a different database right we're focused on our here it's going to be called our order item database orders order item database so let's do that update that order items database and to make sure you got the name right you just add a dot here and make sure the intellisense comes up and it does okay good so that's our first available row we only need one equals there okay so that is our first available row inside our items database once we know what row we're going to be adding at least the first row right we want to do is we want to get again that order row what is the order row i want to set an initial order row why is that important right if we look in here we keep track of order rows i need to know what row that means what row it's going to be placed on our initial row is always going to be 10 i'm going to go to 11 12. right so in this case our first row for this particular format is going to be 10. so let's set that initial row to 10. so we can do that inside our order row it's going to equal to 10. set initial order row and then all we need to do for every item is increment it one okay so now what we're going to do is 4 or loop invoice item row is going to be equal to but now i need to know how many rows how many rows are we going to be right we need to loop for every rows well this one right here split table based on rows we know this particular array right is going to tell us exactly how many so i need to know the lower bound and the upper bound of that array that's going to tell us exactly how many we do we're going to do so we can do that so 4 we're going to put up is going to be the l bound the lower bounds of what of our table data array so that's going to be the bottom balance to the upper bound u bound u bound which is the upper bound of that table data rate table data array so that's it next invoice item row okay so basically that's going to create a loop for every row right in our first experience it had right we split this in this case two times so we have three different rows so we have three different rows so the lower plus the output would just start at zero to two creating a three so it's going to go zero 1 2 creating three different loops here so for each loop what are we going to do well let's look back on our database and take a look and see what we need to add well we have here we knew we have let's make this fix so we can see it i'm going to of course make it view freeze the panes and then as we scroll so we can easily see the column headers okay so we have here we know the invoice id right in this case order idea invoice id this is going to be fixed but we're really adding only this information right so i'm going to start with columns a and b and add that information then i'm going to add this information then i'm going to add this okay so let's do that let's add a and b first right before we get into the data right so how do we do that so in this case we're going to do orders items database order item not orders order items database dot range a and what our invoice item row dot value what is this going to be then of course that's going to be our first invoice id which is invoice id we've already placed that in a variable invoice id okay we already put that in a variable and what about b b is going to take on of course a static term which is going to be called invoice equals invoice so in this case we're using vba to place that static term in the case above we used our data here that we mapped okay so we have the invoice from caller set static order type the order types always going to be invoiced in this case and that's of course column b not a okay so now columns c let's call it c all the way through in this case let's go column two all the way through the total we have another so you can we're going to go from two all the way to column seven from three to seven we need a loop and we're gonna add that data in here so four the invoice item column is going to be equal from three to seven i'm closing our loop next invoice item column okay so once inside of inverse again we're going to update that database order items database dot cells again dynamic what's our row our row is our invoice item row what's our column it's going to be our invoice item column dot value what's going to be equal based on that array and so what is that a row it's called the row data array in this case our row data array is going to be equal to the invoice item column minus 3. why are we subtracting three in this case because we're starting on three right the first value in this array is always going to be zero right so how do we get to zero we know we're going to go three right so we need to get zero one two three and four so that's gonna place our table data place table data right in there okay just need equals there okay so row data array right remember our row data rate is coming from here right we've got all that information oh gotta do that all right so let's add that almost missed that okay so right here our row data here is going to be equal to we got to set that up row data road what is our road data let's just add that here up here equals the table data array based on the invoice item invoice item what does that mean and that means as we're looping through each individual row remember going zero row one two we got three rows we're going to extract that data for each row all the information that i wrote into a single row of data right i want all that rate all we have is the whole table data right but i want to extract each individual row and put it inside the row data i want to then split this row data right inside an array row data array okay in this case is going to be equal to we're splitting that again splitting row data comma and then asterisk and i'll show you exactly what that means in a minute okay so we're splitting that once again so all we've had up until this point is the information that we have inside each injured row so now what i want to do is i want to split this row data all of this row data right here here i want to split it by the asterisk so we get one and then another one right and then another one the quantity and then the price and then the total splitting it by the asterisk so we can then work with each individual item and that's going to be on the row data and that's where we're going to get this row data array because we create this array here and we split it up by that asterisk so we can then individual columns it is that that we're going to separate there so we have that okay but i also want to have a conversation what if we have a blank row well then i want to run a little bit of an if statement right what if the data is val you know let's say what if we have an empty row right if in this case let's check it out row data array i want to check for the first one first available array what is that first item it's going to be 0 right if it's equal to empty then i want to go to the next file go to in this case next file basically i want to skip it we have an empty row there's nothing we can do so not in that file so we just skip that right that's going to be the end of the rows right let's say you're mapping 10 rows but you only have two rows of invoices once you get to the two rows we're done with that file right we need to skip out of it so we're going to go all the way in this case to the next row so we're going to loop at it right before the loop we need to go exit out of that so we can do that right here so we'll just call this next and add to that in this case next file okay so we're going to write more okay so that's it so we want to skip all this in case we have the blank rows we don't need to continue okay so now that we've mapped out each individual row data and split that row data based on the answers we can then add each individual item inside that row from three okay so now that we've added that but so basically all we've done at this point is we first added those two columns then we added all of this from three to seven now i need to add the last two the order row and the row so i want to add those last two in focus on column h and column i so we're going to just going to copy this and we're going to update the columns on this case so h and then i so this one's going to be h and this one's going to be i so now what is h going to take on h is going to take on we need to have that column i need to know what column the order row here so we've had that already the order row here so that's the variable it's going to take on we just need to make sure we increment that equals the order row order row and then of course the last one is simply going to take on the item row or in this case we could also put in a formula so that should be sufficient equals in case we decided to delete a row we'll put in that formula there so that's called the row call that row formula okay so we've now we've added that but we need to do is we need to increment that row we also need to increment the item so there's two things we need to increment here i need to increment this invoice item row i want to make sure that we do that increment the invoice item row how do we know that this invoice item right as we're adding items down here right we're starting here we need to increment the rows as we move down and we also need to increment the order rows 10 11 12 right so we need to increment both of those so that we're going to do that so the invoice outer row is going to equal the order row plus one increment order row and also the invoice item row we need to increment that so we can copy that here paste it down here equals plus one increment we'll call this invoice item database row so we're incrementing that as well and that's it we can go to the next item very good all right let's make a this should be invoice item i'm going to loop through this invoice item and then invoice item not row right okay because otherwise it's confusing now keeping my invoice item we're looping through all of the items in there and then this is why this invoice items work as we move through those right in this case it's going to be 0 1 2 right in this case for 3 rows so that's how we can pull up 0 is going to pull up that i know it's a bit confusing so as we loop in this case 3 items this is going to go from 0 to 1 to 2. so we're going to put all the data inside this row we're going to then split that row based on the asterisk and then we're going to add every individual item within that row okay good i'm glad we got that cleared up now we're ready to go okay so now what do we want to do now we've brought all the information and we've done it for everything so everything we need we just need to clear out the file just like we did before just like we did here we cleared out the file name as we did up here so all we need to do is do the same thing again so i'm just going to copy that just as we did here and then bring it down here so all we need to do so now once we've got the file name clears out the current file name and i do want to check again just as we did before we can copy that mostly and just make some updates on here so again i'm going to copy this here we don't need any weight in this case and i'm going to paste it down and we're going to make the appropriate updates so if the invoice archive right in this case we're not focused on invoice archive we're going to be focused on the data archive so we're going to put that data archive i want to make sure this go that's clear in this case it's not going to be the data file path right and the directory the file path and basically what we want to do now is take that text file and bring it over to the archive right the archive the data arc i want to put it there but if it exists there already i certainly don't i want to delete it so if it exists there clears out the current file name then we're going to basically move the copy that copy the data let's put this data file now we're copying the data file to the archive file so file copy data in this case it's going to be called data archive so data archive is where we're going to put it data archive and it's going to go to the data file path so copy the data file path it's going to move into the data archive so we must copy that data.txt if i want to put that in the archive that will do it then what we're going to do is simply delete it here scale the data in this case instead of pdf count i want to count the data so we're going to call this data count equals data count plus one call this data because i want to know the difference data count so count data text files converted all right i want to know how many tests involve looping and lastly what i would do is just want to provide a message box to let the user know how many were configured message box pdf count right count and what space will do pdfs have been uploaded how many got uploaded all right and let's do a space and maybe a new line new line right and then we'll do another one let's say data count how many data files data count and then and then a space invoice data files have been let's call this processed or uploaded or whatever we want to do that in this case process been processed okay okay good so that's it that's going to let us know and just update it here and quotation here okay all right good so we're good to go so that's pretty much it that's all we need to do and we got too many ants here okay we're good to go okay looking good now we have that we can clear out the extra spaces here we don't need we'll take a look over our code saving that work of course and looks like good of course if there's any issues we'll fix them as we speak and we'll try it with one and then we'll do multiple here okay the next thing inside back in the increment we we know this is working now we're good to go what we're going to do is we're going to save any work we're going to close this and we want to make sure we turn it on okay and then run scenarios immediately right and then we can do that so that's we'll just going to run it immediately and activate that okay so now it's saved automatically we're going to save it so we can just save it and then it's always going to be running now automatically when we add a web hook it should go through this process okay so let's take a look inside here our data file here inside our folder and let's take a look let's clear out the invoice data that we have and i also want to clear out the invoice data on ours let's take a look inside our pdf invoices here making sure there's nothing here in this folder here okay good and i also want to clear out the data here so the order database 11 that looks good i want to make sure there's nothing here okay so there's nothing here good good good and let's go ahead and add in here now what we're going to do is we're going to simply bring a file and then run our macro so let's take this one and put it inside our pdf invoices pasting it here then of course inside of here i've taken this macro here i've already assigned it to that and it's going to be the pdf that's the macro we just created called pdf to excel so that's the one i'm going to run i'm going to run that and let's take a look here main invoice data all right that should be the array right the main inverse data array of course that's the array that we have let's to continue on with this okay let's take a look inside to see what's going on now of course it's going to take a few seconds to get it there but let's take a look inside our folder here there's our pdf if we refresh that in about 20 or so seconds right so let's take a look if you want to know what's going on right you can click on here history here right you see it's running now right you see it's running 19 seconds or so if we refresh it gives you an idea that it's going on okay good now let's go ahead and take a look back inside our scenario here take a look inside our folder here refreshing this our invoice is gone good that's what i want to share our invoice data here is gone good because it went through our invoice data archive here shows an archive good i like that our invoice archive shows the one we just added i like that there let's take a look inside our data here one pdf has been uploaded one invoice data files have been processed very nice insider order database okay here it is 12 invoice fred good skipping here skipping these two adding that that looks perfect take a look inside our orders invoicebathroom23 that looks good everything looks good according to that so if i want to load the invoice all i need to do is enter 12 here and our invoice is loaded perfect all right let's try it with the other two and see how that goes okay so we did 12. now we're going to do 13 and 14. all i'm going to do is drag them into the in pdf invoices folder i'm just going to simply run it our macro and take a look alright and after a moment or so two bdfs have been uploaded and two invoice data files have been processed looking back in our orders we now have orders 13 and 14 here order items we now have 13 and 14. three rows for 13 two rows for 14. well back in the side loading up or order number 13 loads properly 14 we can now make changes if you want 14 does fantastic excellent in this training i have shown you how you can automatically process and convert unlimited pdf invoice files into excel to make them fully editable in just a click of a button with a single macro it's been a great training if you do like these trainings please don't forget to subscribe also if you like these workbooks i've got 200 of my best workbooks available for just 77 that'll help us out i'll include the link down below and don't forget our patreon where we've got even more content and of course we'd like to hear back from you on your feedback of course if there's any features you want or any fixes you want me to make or maybe you want me to focus on that i'm doing that in patreon so we'll see you over there thanks so much [Music]

Channel: Excel For Freelancers

Views: 116,550

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Convert PDF To Excel, PDF To Excel, PDF Convert Excel, Convert PDF, PDF Invoice To Excel, PDF invoice In Excel, PDF Invoices, Free PDF Convert, Free PDF Conversion, Free PDF Convert To Excel, Convert PDF Invoices To Excel, Excel PDF Conversion, PDF Invoice, Invoice From PDF, PDF

Id: h_XKJIDvJlg

Length: 87min 57sec (5277 seconds)

Published: Tue Oct 26 2021