Collaborate on Fake Invoice PDF for HighTech with Ease Using airSlate SignNow

Fake invoice pdf for HighTech

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 ingly   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 ingly 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 i