I never started writing after a conscious decision. Tulagi Hotel, my 134,000 word novel, came about after I chanced to write the first chapter at the office, over an extended coffee break, and with no intention of making it into a full-fledged book.

But when it was published, I found myself writing short stories. In a span of six months, I wrote 16 stories. At this time, I became interested in tracking the word counts of the stories. This is interesting for two reasons: most short story competitions have word limits, and it is nice to know the progress of your Work in Progress (WIP) files. Of course you can just note the word count and manually copy it to an Excel sheet, but I thought to make that process automatic. I will explain how, and also, share my file under the CC-BY-NC license.

To do this, you need Excel, and its VBA (Visual Basic for Applications) editor. Microsoft has built in a system in all its application software that allows you to programmatically glean information about a file, without actually opening the application that you used to write the file. The links to the files can be refreshed every time you open the summary Excel file, and you can manually refresh the word counts if you want to, by selecting Data – Refresh All in Excel. Let’s look at the Excel file structure first.

I have two worksheets in the workbook. One is called FINISHED, and it has no live links to files. This is because every link you have slows the opening of the file, and if a file is finished, there’s no need to check its word count. The other is called WIP, and that one has the live links. Of course there is no reason for not having them in one worksheet, but I preferred this for clarity.

I first decided to have the columns CODE, NAME, GENRE, WORDS and PAGES. Code is my way of telling whether the story is finished or not: the story F18, Yessirree, is finished, and it is the 18th story I wrote. Name is just the story name. Genre is a handy column, because for competitions, genre is important. Words is the item I pull from the Word file, and Pages is just Words divided by 526.

I also have the columns COMMENTS AT, PUBLISHED AT, COMPETITIONS, and SENT TO. These I use to track the life of the story, because I used to lose sight of where I had entered the file to competitions or magazines. This is how the Finished worksheet looks:

The color codes are used merely to identify similar items by color too, and are not at all essential to the operation of the file. The important thing is to have the columns named like this, because that enables you to sort the file by any column. There is one noteworthy piece of Excel on the sheet, and that is the totals of word counts and files on the top of the sheet:

109917 words in 34 finished stories and 206 pages with 33993 words in progress

This is a handy way to see at a glance what is your total word count, and how it is divided into stories. The formula that calculates it is as follows:

=D38&” words in “&COUNTA(A3:A52)&” finished stories and “&E38&” pages with “&wip_words&” words in progress”

The cell D38 contains a sum of the values of the WORDS column, ie. =SUM(D3:D37). The function COUNTA counts the number of cells that are not empty in a range. The ampersand is a handy way of combining text in quotes with formulas, values, or other text.

As for the WIP where the information is likely to change as I add stories and edit the other WIP stories, there is only one rule. I have to use a naming convention that enables the custom function to work. For this, I have one named cell, PATH, which contains the folder under which all the WIP files are in their respective folders. Every folder has the same name as the story, and is preceded by the sequence number I have for each story idea. Therefore a story may reside in a folder called 47 Nueva Congo, and the story name is Nueva Congo.rtf ( I work in RTF due to ease of sending files to readers; everyone can read RTF).

So, the PATH cell has the value C:Usersheikki.hietalaSkydriveshorties, and the setup of the worksheet looks like this:

The function that gets the WORDS column values is the only tricky part in the whole system. It is called GetWords, and I found something like it on the Internet and then crafted it to suit my needs. It is called like this:

=GetWords(path&""&A3&" "&B3&""&B3&".rtf")

So, you see the function call is sending the path, the file folder, the file name, and the extension .rtf to the function, and it is returning the word count. In fact, this is the way you always work when you craft a custom function. The cell references are relative references, so we can copy the formula down as we add new WIP stories.

Let’s create the custom function. First, open the VBA editor by pressing Alt+F11.

This blank editor needs to have a code module added to it. For that, open the dropdown under the menu, then select Module from that list:

This gives you a blank module:

After this, you can paste the following piece of VBA code into the module. Remember to get the entire snippet of code, else it will fail.

Public Function GetWords(strInFile As String)
Dim objWord As Object, objDocProps As Object
Dim i As Integer
 On Error Resume Next
 Set objWord = CreateObject("Word.Application")
 With objWord
 .Documents.Open strInFile
 Set objDocProps = objWord.ActiveDocument.BuiltinDocumentProperties
 For i = 0 To objDocProps.Count - 1
 If i = 15 Then
 'Debug.Print objDocProps(i).Name, objDocProps(i).Value
 GetWords = objDocProps(15).Value
 End If
 Next i
 End With
 objWord.Application.Quit savechanges:=False
 Set objWord = Nothing
End Function

You should have it looking like this:

When this is done, click on the Save icon in the toolbar, and then select File – Close and Return to Excel. Now you have a custom function in your Excel that you can use. At this point, save the Excel file as Excel Macro-Enabled Workbook.

As I suggested, you could have a “master folder” under which you can have all your stories. The only consideration is, Excel must know the path to the file. Therefore you can either have the master path in a cell you name Path, and then add the folder name for each of the files. Or if you have all your files in one folder, you can just change the filename.

In my solution, I call the function this way:

=GetWords(path&""&A3&" "&B3&""&B3&".rtf")

This corresponds to my Excel structure, and combines the path with the contents of cell A3 (folder name), then a space, then the cell B3 (file name), a backslash, and the file name again, topped with “.rtf” (file format). However, you can use the GetWords function in any way you like, because the only thing is, it needs to be passed your file location as a string. Therefore, if you have all your files under one folder, and have all of them in the .doc format, you could insert your path into the cell that is named Path, and then edit the function call to be like this (assuming the cell A3 contains the file name):

=GetWords(path&""&A3&".doc")

In the extreme case you want to check just one file, it can be done with this function call:

=GetWords("c:myshortstoriesmylateststory.rtf")

Of course, you would need to edit the path to suit your own setup, and make sure you have it in quotes, so it is passed on as a character string to the function. When you decide on the columns you need, all you then have to do is to update the function call so it combines the column contents in a way the gives the function a valid path to the file.

In case you are not entirely sure how to edit custom functions, I have provided the working file for you as is, and you can take it into use merely by editing the path cell. To test it, I placed a test file in the path you see in the file, and entered its name in the cell A3. As you then see, it gets the word count. When you change the path and file name to suit your needs, the system updates itself. I have given the sum cells a range of 100 rows, so it will fit quite a few stories. Also, do not delete the cell that calculates the words in the WIP files. It is called wip_words and its presence is needed for the Finished worksheet’s summary clause.

Contact me if you have problems. I hope this works for you as well as it has served me.

Get the Excel 97-2003 format file from here: Wordcounts

UPDATE: here is a slightly edited and easier to use Office 365 version of the file: