It is possible.
If you restructure your Worksheet to look
EXACTLY like this (including putting the contents into these specific columns, your first chapter starts on
Row 4, etc., and including copying the formula for
Cell D4 and pasting it into
Cell D4 and filling down as many rows as you need to)
- But you don't have to have the formatting
- You can change the novel's folder name in Cell B2, the file extension to .doc in Cell C1, and the folder location in Cell D2
- You can delete all contents and formatting of Cell A2.
Then, once you insert the following code into your Excel Worbkook (and save the Excel Workbook as
.xlsb file extension, not
.xlsx), when you
double click on a cell with a chapter number in
Column A (and there is a file location to the right of it in
Column D), then if the file in the file location exists, it will put the word count in
Column C.
There are Four scenarios of what can happen when you
double left click on the chapter number cell.
For scenarios 2 - 4, it will update the word count in Column C.
- The Word Document doesn't exist.
You get a notification that it doesn't exist.
- The Word Document exists and is opened.
It will update the word count in Column C (but it will not open or close or save anything)
- The Word Document exists but is not opened and Microsoft Word itself is opened.
It will open the file without opening up a second instance of Word (if you don't know what I meant by "a second instance", then don't worry about it).
- The Word Document exists but is not opened and Microsoft Word itself is NOT opened.
It will open up Microsoft Word and then open up that Word Document. (The Chapter Document will be the only Word Document opened after the fact.)
When you
single left click anywhere in the sheet, if you have one (
or more) of the Word documents/Chapters
already opened, it will update
ALL of the Chapter's word counts.
But on condition:
- Let's say that you are starting your day and there are no Microsoft Word Documents already opened.
- If you know you want to edit Chapters 1-4, and you select all of them in the folder and press enter (to open all of them at once), then the code will only "recognize" the first Word Document that it opened up.
- There needs to be only ONE Microsoft Word application opened . . . which you can see in Windows Task Manager.
- So instead, just open up one of the Chapters first, and then you can select as many chapters that you want to open up and press enter to open them all up simultaneously. Then it will update all Word document chapter's Word counts that are listed in your Excel Spreadsheet/Table of Contents. (If you have other Word Documents opened for another purpose, it will ignore those.)
So, here's the code I wrote to do this. If you don't know how to insert code into an Excel Workbook already, follow steps 1-4 of
Insert and run VBA macros in Excel - step-by-step guide - Ablebits.com .
Insert this code into a new module (as shown at the link above).
VBA Code:
Option Explicit
Sub OpenWordDocument(filePath As String)
If File_Path_Exists(filePath) = False Then
MsgBox "The file could not be found.", vbCritical, "Opening file Failed"
Exit Sub
End If
Dim WordApp As Object
Dim wordDoc As Object
If IsFileOpen(filePath) = True Then
Set WordApp = GetObject(, "Word.Application")
filePath = Right(filePath, Len(filePath) - InStrRev(filePath, "\"))
WordApp.Documents(filePath).Activate
Set wordDoc = WordApp.ActiveDocument
Else
If MicrosoftWord_Is_Already_Opened = False Then
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True
Set wordDoc = WordApp.Documents.Open(filePath)
Else
Set WordApp = GetObject(, "Word.Application")
Set wordDoc = WordApp.Documents.Open(filePath)
End If
End If
ActiveCell.Offset(0, 2).Value = wordDoc.Words.Count - 1
End Sub
Sub Test__MicrosoftWord_Is_Already_Opened()
MsgBox MicrosoftWord_Is_Already_Opened
End Sub
Function MicrosoftWord_Is_Already_Opened()
'Loop through all processes in task manager to find out.
'This code (which accesses Windows Task MANAGER) is from https://stackoverflow.com/a/26303330
MicrosoftWord_Is_Already_Opened = False
Dim oServ As Object
Set oServ = GetObject("winmgmts:")
Dim cProc As Variant
Set cProc = oServ.ExecQuery("Select * from Win32_Process")
Dim oProc As Object
For Each oProc In cProc
If oProc.Name = "WINWORD.EXE" Then GoTo Exit_Sub
Next
Exit Function
Exit_Sub:
MicrosoftWord_Is_Already_Opened = True
End Function
Sub Test__File_Path_Exists()
MsgBox File_Path_Exists("C:\Users\cwbasset\Documents\My Novel\Chapter 1.docx")
End Sub
Function File_Path_Exists(filePath As String)
File_Path_Exists = Not Dir(filePath) = ""
End Function
Sub Test__IsFileOpen()
MsgBox IsFileOpen("C:\Users\cwbasset\Documents\My Novel\Chapter 1.docx")
End Sub
Function IsFileOpen(FileName As String)
'https://stackoverflow.com/a/9373914
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error ErrNo
End Select
End Function
Sub Update_Open_Microsoft_Word_Documents_WordCounts()
If MicrosoftWord_Is_Already_Opened = False Then Exit Sub
'The following line of code is needed because "Bad file name" will pop up
'if the user selects 2 or more Word Documents and opens them rather than
'opening up one first and THEN opening up as many as needed
'(one at a time or all at once (by selecting multiple and pressing enter).
On Error Resume Next
Dim lastRowInColumnD As Long
lastRowInColumnD = Range("D1").End(xlDown).Row
Dim filePath As String
Dim WordApp As Object
Dim wordDoc As Object
Dim i As Integer
i = 4
Do While i <= lastRowInColumnD
filePath = Cells(i, 4).Value
If InStr(filePath, ".doc") > 0 Then
If File_Path_Exists(filePath) = True Then
If IsFileOpen(filePath) = True Then
Set WordApp = GetObject(, "Word.Application")
filePath = Right(filePath, Len(filePath) - InStrRev(filePath, "\"))
WordApp.Documents(filePath).Activate
Set wordDoc = WordApp.ActiveDocument
Cells(i, 3).Value = wordDoc.Words.Count - 1
End If
End If
End If
i = i + 1
Loop
End Sub
Now right click on the Tab name of your worksheet with the table of contents (at the bottom "status bar looking part of Excel) and select
View Code.
Copy this code into that module.
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'When you DOUBLE left click.
If InStr(Target.Offset(0, 3).Value, ".doc") > 0 Then
Cancel = True
Call OpenWordDocument(Target.Offset(0, 3).Value)
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'When you SINGLE left click, DOUBLE click, or right click.
Call Update_Open_Microsoft_Word_Documents_WordCounts
End Sub
If you have been using hyperlinks to open your Word Documents from the Excel Table of Contents, you may
remove them, as you won't need them anymore. But if your Worksheet is already interactive, then just append the above code to your Table of Content sheet's code module as needed.
Final Notes: (For this post)
- Clearly this spreadsheet "template" formulas for Column D assumes that all of your Word documents have the same file extension. (They are either all .docx or .doc.)
- I chose double left click rather than single left clickto carry out the action because you won't be able to rename the chapter numbers, as you have to select a cell to change its contents.
- Usually people double click on a cell to change its contents, but you can single click on it and change its contents via the formula bar.
- It is possible for Excel to update the word counts every, say, 5 seconds or so without you having to do anything. If that's what you wanted, let me know. I just wanted to show this ("less intrusive") method first.
