Large companies use costly software to scan resumes using their custom built keywords list to find candidates for all open positions in their company. If you are a small consultancy firm and if you are still scanning your resumes manually, do not worry. Now you can build your own resume scanning tool.
If you want to start to use the software as soon as possible, then you can:
Otherwise, if you want to DIY, you can read the contents below.
Let’s prepare the GUI
As shown in the picture, create two sheets and name the first one as “Menu” and the last sheet as “Resume”. As the name says, the sheet “Menu” will be the GUI for our tool and the sheet “Resume” will hold info from Resume files that were read through our tool.
On the sheet “Menu” create a field to hold the full path of selected Resume. Please do note that the resume should be in doc or docx format. Add two buttons. We will later add macro to these buttons to make it functional. Let us name the button which is to the right of the field as “Browse”. Let the button at the bottom of the field be named as “Read Resume”. To make the tool visually good, you can add icons as I have added a Word doc icon.
Let’s prepare the database
It has become very common that all companies have a resume template on their website and job seekers are asked to use this template. So preparing the database is simple. Just add column headers corresponding to fields in your company’s resume template. As shown in below images, I created a sample Resume template and added corresponding headers in the sheet “Resume”
Let’s make it functional
Copy the script and paste it in a new Module. The script has two subs. The first sub is to allow user to browse and pick the resume file. The second sub is to read and import data from selected resume. Now go ahead and add these macros to corresponding buttons on the sheet “Menu”
Let’s test it
From the sheet “Menu” hit the browse button and select a file. Now you can see the full path of the selected Resume appearing on the field. Click the “Read Resume” button and the tool will instantly read data from Resume and put it on the sheet “Resume”
How it works?
The script treats the resume as a table with rows and columns. Each row and column is parsed and data is passed from word into Excel.
The tool you just built reads a single resume. You can easily modify it to read multiple resumes from a folder. Modify the first sub to allow user to pick a folder and modify the second sub to read and pass each word file from the selected folder.
Sub pick_word() 'Allowing the user to pick the resume Dim wd As Office.FileDialog Set wd = Application.FileDialog(msoFileDialogFilePicker) With wd .AllowMultiSelect = False .Title = "Please select the Resume" .Filters.Clear .Filters.Add "Resume", "*.doc*" If .Show = True Then Sheets("Menu").Range("G5").Value = .SelectedItems(1) 'show the picked file on the Menu sheet End If End With End Sub Sub ImportWordTable() 'Read the resume Dim obj1 As Object Dim file1 As Variant Dim t1 As Integer Dim ir As Long Dim ic As Integer Set obj1 = GetObject(Sheets("Menu").Range("G5").Value) Sheets("Resume").Rows("2:1000").Clear Dim c As Long c = 0 With obj1 With .tables(1) For ir = 1 To .Rows.Count c = c + 1 For ic = 2 To .Columns.Count Sheets("Resume").Cells(2, c).Value = WorksheetFunction.Clean(.cell(ir, ic).Range.Text) Next ic Next ir End With End With Set obj1 = Nothing End Sub
In case you have damaged Excel xlsx file file and you haven’t noticed it before, it is now time to fix it using recovery tools such as DataNumen Excel Repair.
Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc corruption and outlook recovery software products. For more information visit www.datanumen.com