How to Create Your Own Resume Reader with Excel VBA

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.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Download the Resume Template Now

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.  Prepare The GUI

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”Sample Resume Template

Add 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.

Modify it

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.

Script

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.

Author Introduction:

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

Leave a Reply

Your email address will not be published.