PDF to Excel Converter in Excel VBA

[Music] hello friends welcome back my name is Brianna Kumar in this video you will learn how to create a PDF to excel converter using BB so as you are seeing my screen I have created this PDF to excel converter in Microsoft Excel using BB so here I have this setting seed in this seat I have PDF files folder path and here I have Excel files folder path so this will be my input files and this will be the output files this is the PDF files folder here I have few PDF file I'll show you the PDF file also so let me show you the test one file here I have some data this is the employee performance related data here available in this PDF if you will try to copy and paste this data directly on the Excel then it will not be in correct format so let me show you if I will select this copy and I will go to the axle and let's say on this c2 I will paste this data then this data will come in this format which is not correct right because it is coming in one column only so how we can convert this data into excel all right so let me close this I have one more this form 16 sample here this is the stand image you can see so friends it will not convert this scanned image if you will try to convert the scanned image then it will simple paste that image on that excel so let me show you how it will work and if I will show you the excel files folder so you can see there is no file is available now I'll go to the this now I will click on start conversion so just click on start conversion and now here you can see it will start converting the file so first it has converted from 16 then test one times two and the other files also it is converting now if I will go to this excel then you can see the masses as done so all the PDF files have been converted into Excel now I'll show you the process how it is converting so this is the process of PDF to excel conversion now first of all what this macro is doing it is opening a PDF file as bird file then it is comping the word range means all the data available on that word file it is just simple comping that data and now it is pasting the data on a new excel file then in the fourth step it is simply saving that file and closing that excel file and then it is opening the next video file so this is the all four steps to convert PDF to excel now I'll show you the VBA code also so here I will go to the Developer tab and Visual Basic and in this module one I have this code which is PDF to excel now first of all I have taken a variable for the setting seat so setting underscore as such as worksheet and I have assigned this setting underscore SS to this workbook dot seeds and the setting seat I have different two variables PDF path and Excel path assistant and I have assigned the PDF path of this setting seat range he 11 and Excel path settings hit range eat well basically in this cell I have put the PDF file folder path where in mind all the PDF files are available and the excel files folder path also where in I will save my excel file all right now here I have used the file system objects to use the file system object you need to add a reference I will show you so go to the tools reference and here I have used Microsoft scripting runtime so you need to add this reference just close this now here I have used them FSO as new file system object them FOH folder and F as file and I have set the Apple on PDF path all right so what I'm going to do I'll open each and every PDF file so that is why I have you effect fo FSO dot get folder and then PDF path so friends here I have used few other variables BMW a as object W a I am using for void application than the dock as object name W R as object for world range and then here I am using the create object method set W a equal to create object bordered application you can use the a simple method like dim W a as new void application this method also but in that case you need to add a reference for white application I'll show you that reference also you need to add this reference but Frank's I'm using Excel 2013 if you will use this application in Excel 2000 and then this reference will be different and it may give some error but that is the reason I have used this create object method however I have put this code here also and I just have comments this code you can enable this code and you can add the reference and you can click this code from here all right then I'm simply using W a dot visible equal to true that means it will make the void application as visible here I have taken two variables them and WB s bar book and dim Ana such as worksheet because I will add a new Excel workbook so I have taken this variable and this I will assign to worksheet the first worksheet of an WB alright then here I have used a loop this for each loop for each F F we have taken four files so for each F in fo the PDF file folder so for each file in PDF files folder it will check all the files available in PDF file folder and then here first of all what I have done I have set doc equal to W a wide application dot documents thought open and then I have used F dot path F dot path will return the complete file name along with the extension then I have said the WR word range word range I have simply used talk taught paragraph 1 dot range and then I have used w r dot whole story and WB as new workbooks or have used workbooks dot add and NS h i have assigned to the first seat then i'm simply copying the blue r dot copy it will copy the word range and it will paste that on n SS and SS dot paste and then for end of new be the new workbook i am just using save S & W be dot save as and Here I am using Excel path because I will save this file in Excel path folder then this last and I will put the file name with extension keep this same file name but the only difference is the file extension is dot PDF we want to convert it in dot xlsx so I have used this release formula now I will close that document and I will close that excel file also right and then it will check the next file so once it will convert all the file then we will quit deep void application also and in the last we will display this message sometimes this is process to convert the PDF to excel all right friends I hope you have enjoyed this video friends if you like this video please hit the like button and please subscribe my channel to get the regular updates of my new videos thank you so much for watching [Music]

Channel: PK: An Excel Expert

Views: 64,637

Rating: 4.8731709 out of 5

Keywords: ADVANCE EXCEL, VBA MACRO, PDF to Excel Converter, File System object in VBA, Word Application in VBA, Create Object in VBA, Word Range in VBA, Open PDF file as Word using VBA, Copy PDF data on Excel using VBA, Multiple PDF File converter in VBA, Offline PDF to Excel Converter, Free PDF to Excel Converter

Id: JW9i9GnQAZQ

Length: 8min 17sec (497 seconds)

Published: Mon Aug 05 2019