A Recruitment Tracking Tool for Excel

The solution I want to share today was truly a workhorse for me when I worked as an Human Resources Specialist in Otis and it saved me from duplicate chores and fat finger. I'd like to call this tool or workbook, whatever you call it, a quasi-system because it covers the full life cycle of daily recruitment processes. The system is built upon the workflow and standard of the company, but I tried to design it for common usage and to make it easy to customize. I hope it can be helpful to staffing officers and recruitment operators. I'll illustrate how it works through the common workflow. The file are available at the end of this passage, and you can also download it and try it yourself.Some examples such as resumes and personal information are for illustration only and are fictitious. No real association is intended or inferred.

The files and codes are provided "as-is". You bear the risk of using it. Remember to save your work files before testing the codes.

The system tracks and records the progress of every single applicant from the time their resumes are downloaded to the filefolder. The operations such as fetching raw information from resumes, sending short-listed resumes to line managers, writing and sending interview appointments, and generating schedule on calendar are all highly automated by VBA. In general, the system is composed of three parts: agenda, workflow, and database. The workflow, the core of the system, is presented as an all-in-one template worksheet, in whose copies all applicants' information and status are categorized and stored.

main.jpg

Here we got a sample copy of the template for a new open position named Mechanical Engineer (Sample). Note that you do not need to copy from the template worksheet manually to create new sheets for new position. All the jobs is done by VBA when resumes for a new open position is detected, and I'll show that later on. Now Let's take a look at the template more closely.

1.jpg

i. The first part with label No.1 contains the key information of the position (Title, Department, Line manager, Line manager's email, Start Date, and Deadline) in the head rows. The manager's name and email will be used as receiver when sending notice and appointment in the following workflow, so make sure they are set correctly. The title and deadline is filled automatically by formulas in the cells.

From the 4th row and below, the raw information of applicants imported from resumes is stored in column A to column F. There is an Easter egg in column E: To make it easy to review the full resumes without cramming all details into the worksheet, double click any cell with file path will open the resume in no time. If you downloaded the files, you may need to fix the path in according to the actual absolute path in your computer to make it work correctly. For example, if you saved the Mechanical Engineer (Sample) folder under E:, just set all existing path in column D to E:\Mechanical Engineer (Sample)***.doc. (replace *** by the real filename).

2.jpg

ii. The second part, colunm G to J, is for phone interview records. Usually, after a phone interview we need to summarize the notes and decide whether to send the resume to line manager for further screening. Label those who passed the phone interview with Y (N otherwise) and a timestamp will appear in column H. Meanwhile, the records will be added into a separate sheet in format of database silently. Remember that we mentioned Database at the begining? Yeah, it actually is several sheets which store record of different operations in standard format. All the records of the same process, such as phone interview, sending resumes to line managers, interview and offer, are stored in these four sheets regardless of which position they belong to. The benefits are obvious, it makes detailed data available and makes analysis easy as never before.

database.jpg

Back to the workflow, see the first arrow button in the head row? Just click it to send the resumes passed phone interview to the line manager. I prefer to review and check the email before I send it, so the program displays the mail written by VBA for me to check instead of sending it out directly. All the resumes are attached. The mail template is set in html in VBA codes, you may change it as you like. To add your Signature, please find this line in VBE: SigString = Environ("appdata") & "\Microsoft\Signatures\YourSignature.htm" and replace it with your own signature file name.

Sendcv.jpg

iii. The third part is for interviews, and it is similar with part 2. Here we got a date picker for colunm K and R, double click in a cell to call it out.

3.jpg

When interviews are arranged, a appointment mail (to managers) and an invitation mail (to applicants) will written out according to the information in the row at a click of "interview" button. At the same time, records will be inserted into Data sheet automatically.

appointment.jpg

It's time to show you the agenda. All interview schedule can be easily check in Agenda sheet. The agenda is a fully functional calenda, which enable you to have a whole picture of your schedule. Adding new interview through agenda interface is ok, but not recommended.

agenda.jpg

So, how to import raw data from resumes? That's a Good question. Click the button on the right side of the calenda, the system will begin to scan the folders in the same path with this workbook and import newly added reumes. Be ware that you should put the workbook and the folders with resumes in a standalone path rather than on desktop or somewhere alike. Here is an example.

folder.jpg

Create different folders for different open positions and save resumes into the right folder. Then "Import" the data and begin to work.

Notes:

As the fetching function is meant to extract Chinese characters, it may not work well with other language now.
If you want to customized the options in drop-down list you can find them in hidden worksheet.

The fetching function supports standard .doc/.docx resumes from zhaopin.com, 51job.com, and tedahr.com and most of non-standard .doc/.docx resume(RegEx matching).

The solution takes advantages of many open source codes and moudules. I want to list them all but I failed to keep trace of some pieces of codes because of the long time this project took. Please feel free to contact me if you can help to find the authors. I'm indebted here to all the authors for their gorgeous work.

Date Picker Moudule by https://sites.google.com/site/e90e50/calendar-control-class
GetBoiler Function by Dick Kusleika
RangetoHTML Function, Outlook mail Moudule by Ron de Bruin
Change Monitor by 谭国维

File Download:
recruitment tracking beta.zip



知识共享许可协议
除非注明,本博客文章均为原创
并采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可。转载请以URL链接形式标注源地址。

标签: excel, vba

添加新评论