Turn on Track Changes in all word files in a folder, including sub folders

[dcgrove]

Hello, I am completely out of my element so I come asking for some help.

I have found the code below that appears to turn on change tracking for all .doc files in a specific folder. What would need to be modified to get this macro to work on .doc and .docx files, and recursively in subfolders that could be multiple levels deep? If it would be easier, I could generate a list of file paths/names that need change tracking turned on.

Sub TurnOnTC()
'
' TurnOnTC Macro
' Turn on track changes for all documents in directory
'
FName = Dir("C:\Resume Project\*.doc")
Do While (FName <> "")
With wrd
Documents.Open ("C:\Resume Project\" & FName), _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
wdOpenFormatAuto, XMLTransform:=""
ActiveDocument.TrackRevisions = Not ActiveDocument.TrackRevisions
ActiveDocument.Save
ActiveDocument.Close
End With
FName = Dir
Loop
Set wd = Nothing
End Sub

Thanks!
Clayton Grove

[Frosty]

Well, for starters... this isn't a "TurnOn" routine... it is a Toggle routine.

You're setting the ActiveDocument.TrackRevisions property to the opposite of what it currently is, rather than a set value (true or false).

Do you want the ability to toggle? Or do you want the ability to turn the track changes on and off?

Also, what version of Word are you working in... that can change the answer.

Roughly speaking, how many documents are you talking about running this process on?

[dcgrove]

Frosty, please excuse my ignorance when it comes to MS Word. I am using word 2007, I need to turn the track changes on for all documents, which will probably be close to 3000 documents. Additionally, if possible, I would like to turn on protection so that change tracking cannot be turned off.

Thanks!

[fumei]

As Frosty has pointed out, it is a toggle. It will reverse the existing state. Can it be done? Can it be done to make sure whatever the original state, the final state is ON? Yes. It takes additional logic. So. Are you SURE all of them have Track Changes off, or do you need to test?

[dcgrove]

As Frosty has pointed out, it is a toggle. It will reverse the existing state. Can it be done? Can it be done to make sure whatever the original state, the final state is ON? Yes. It takes additional logic. So. Are you SURE all of them have Track Changes off, or do you need to test?


Testing would probably be wise. I can't be adsolutley certain that it is not turned on for every document.

Thank you for all of your help!
Clayton

[Frosty]

Sorry, I got distracted by money work. If you're sure you want to turn track changes on, that's less the concern than whether or not you want to toggle.

I'm actually working on something similar which should be of use to you... I should have a working model tomorrow.

HOWEVER -- are you sure you want to run code you got for free on the internet on 3000 documents? Make sure you test thoroughly. These kind of processes should be very scary. Caveat Emptor and all that. If something goes wrong (and there are so many scenarios where this relatively simple code could interact very badly in your specific environment), with your inexperience... you could have a real problem on your hands.

[dcgrove]

Frosty, I look forward to seeing what you come up with. And don't worry, I fully plan on testing this thouroughly on a backup set of files before unleashing it on the actual files.


Thanks again for all of your help!
Clayton

[Frosty]

dcgrove,

This is a work in progress... but this is the basic structure of what you're about to do. It currently just prints out the file name in the immediate window.

Does this cause a problem in your system? There are a couple of "big picture" questions which will make the below structure a lot more complex. That's why I post this, because this is just a framework.

This is the first big picture question:
1. What do you want to have happen when you fail to do whatever you want to do on an individual file?

Option Explicit
'-----------------------------------------------------------------------------------------------
' The macro accessible from outside the code window
'-----------------------------------------------------------------------------------------------
Public Sub UI_ProcessFilesAndSubFolders()
fWorkOnMainFolder "C:\Test"
End Sub
'-----------------------------------------------------------------------------------------------
'Work on the main folder
'-----------------------------------------------------------------------------------------------
Public Function fWorkOnMainFolder(sMainFolderPath As String) As Long
Dim oFSO As Object
Dim oMainFolder As Object
Dim lRet As Long

'create the File System Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
'Set the folder
Set oMainFolder = oFSO.GetFolder(sMainFolderPath)
'if it wasn't a folder, then...
If oMainFolder Is Nothing Then
MsgBox "No such folder exists." & vbCr & "Exiting...", vbCritical, "SetMainFolder"
GoTo l_exit
End If
'now go through all files in the main folder and any sub folders
lRet = fIterateAllFiles(oMainFolder)
If lRet <> 0 Then
MsgBox "FYI: there as an error processing the files.", vbInformation, "SetMainFolder"
End If

fWorkOnMainFolder = lRet
l_exit:
Exit Function
End Function
'-----------------------------------------------------------------------------------------------
'A recursive routine to cycle through all files and files in sub folders
'-----------------------------------------------------------------------------------------------
Public Function fIterateAllFiles(oFolder As Object) As Long
Dim oFile As Object
Dim oSubFolder As Object
Dim lRet As Long

'go through all the files
For Each oFile In oFolder.Files
lRet = fHandleAFile(oFile)
If lRet <> 0 Then
'pass the error on up
fIterateAllFiles = lRet
Exit Function
End If
Next
'now call myself to go through all the sub folders
For Each oSubFolder In oFolder.SubFolders
lRet = fIterateAllFiles(oSubFolder)
If lRet <> 0 Then
fIterateAllFiles = lRet
Exit Function
End If
Next
End Function
'-----------------------------------------------------------------------------------------------
' What you actually want to do with the file, now that you have it.
' Will passing back any error number
'-----------------------------------------------------------------------------------------------
Public Function fHandleAFile(oFile As Object) As Long
On Error GoTo l_err
Debug.Print oFile.Path

l_exit:
Exit Function
l_err:
fHandleAFile = Err.Number
Resume l_exit
End Function

[dcgrove]

Frosty, the code did exactly as you said it would and printed the name of each file in C:\Test\ in the immediate window I also tested this on a network drive and found no issues. As for how to handle files that fail, ideally it continue through each file and output a list of file paths that failed. I appreciate the effort you are putting into this.

Thanks!
Clayton

[Frosty]

Clayton,

Here is the next version of this code. Notice there is a new procedure ("TestAFile")

You should run that on a single file and see how it does. If that works, then you should see if the main routine works on a couple of files.

I've put in some code to allow you to escape on each error.

This is the way to build code you're about to run on 3000+ documents. No point in logging 3000 errors if you have 10 errors on your first 10 documents.

Next big questions, since this currently runs on ALL FILES in a folder,
1. do you have files in these folders which are not openable by Microsoft Word? (PDFs, Excel Spreadsheets, etc).
2. Do you have files in these folders which are openable by Word, but which you want to exclude from this process (i.e., .docms, .dots, .dotms, .rtfs, etc)
3. Alternately, do you want this to *ONLY* run on files with a particular extension (only .doc or only .docx)
You'll notice a couple of constants at the top of this code which allow you to "tweak" the processing (showing the documents as you open them, turning protection on, select a password, etc).

Note: this code does *nothing* to documents which are already protected. If you need to do something to documents which already are protected, you should decide what you want to do. And if you do elect to put password protection on these documents, make darn sure you write it down somewhere else, since you don't want it to only exist in a code module.

Option Explicit
Public Const OPTION_TRACKREVISIONS As Boolean = True
Public Const OPTION_PROTECTION As Boolean = True
Public Const OPTION_PROTECTIONPASSWORD As String = ""
Public Const OPTION_PROCESSDOCVISIBLY As Boolean = False
'-----------------------------------------------------------------------------------------------
' The macro accessible from outside the code window
'-----------------------------------------------------------------------------------------------
Public Sub UI_ProcessFilesAndSubFolders()
fWorkOnMainFolder "C:\Test"
End Sub
'-----------------------------------------------------------------------------------------------
' A test routine for handling an individual file
'-----------------------------------------------------------------------------------------------
Public Sub TestAFile()
Dim oFSO As Object
Dim oFile As Object
Dim sFilePath As String
Dim lRet As Long

sFilePath = "C:\Test\New Microsoft Word Document.docx"

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFile = oFSO.GetFile(sFilePath)

lRet = fHandleAFile(oFile)
If lRet = 0 Then
MsgBox "No error", vbInformation, "TestAFile"
Else
'set up a recreation of the error, without triggering a new one
On Error Resume Next
Err.Raise lRet
MsgBox "There was an error." & vbCr & _
Err.Number & vbCr & Err.Description, vbCritical, "Error in fHandleAFile"
End If
End Sub
'-----------------------------------------------------------------------------------------------
'Work on the main folder
'-----------------------------------------------------------------------------------------------
Public Function fWorkOnMainFolder(sMainFolderPath As String) As Long
Dim oFSO As Object
Dim oMainFolder As Object
Dim lRet As Long

'create the File System Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
'Set the folder
Set oMainFolder = oFSO.GetFolder(sMainFolderPath)
'if it wasn't a folder, then...
If oMainFolder Is Nothing Then
MsgBox "No such folder exists." & vbCr & "Exiting...", vbCritical, "SetMainFolder"
GoTo l_exit
End If
'now go through all files in the main folder and any sub folders
lRet = fIterateAllFiles(oMainFolder)
If lRet <> 0 Then
MsgBox "FYI: there as an error processing the files.", vbInformation, "SetMainFolder"
End If

fWorkOnMainFolder = lRet
l_exit:
Exit Function
End Function
'-----------------------------------------------------------------------------------------------
'A recursive routine to cycle through all files and files in sub folders
'-----------------------------------------------------------------------------------------------
Public Function fIterateAllFiles(oFolder As Object) As Long
Dim oFile As Object
Dim oSubFolder As Object
Dim lRet As Long

'go through all the files
For Each oFile In oFolder.Files
lRet = fHandleAFile(oFile)
If lRet <> 0 Then
'pass the error on up
fIterateAllFiles = lRet
Exit Function
End If
Next
'now call myself to go through all the sub folders
For Each oSubFolder In oFolder.SubFolders
lRet = fIterateAllFiles(oSubFolder)
If lRet <> 0 Then
fIterateAllFiles = lRet
Exit Function
End If
Next
End Function
'-----------------------------------------------------------------------------------------------
' What you actually want to do with the file, now that you have it.
' Will passing back any error number
'-----------------------------------------------------------------------------------------------
Public Function fHandleAFile(oFile As Object) As Long
Dim oDoc As Document
On Error GoTo l_err
Application.StatusBar = "Working on " & oFile.Path
'for testing error trapping structure
'Err.Raise 13

'now do what we want to do with the documents (open with visible false to be a little faster)
Set oDoc = Documents.Open(FileName:=oFile.Path, Visible:=OPTION_PROCESSDOCVISIBLY)

With oDoc
'if we're messing with protection, we need to check it before doing any changes
If .ProtectionType = wdNoProtection Then
.TrackRevisions = OPTION_TRACKREVISIONS
If OPTION_PROTECTION Then
.Protect Type:=wdAllowOnlyRevisions, _
NoReset:=True, _
Password:=OPTION_PROTECTIONPASSWORD
End If
'save our changes
.Save
End If
End With
l_exit:
On Error Resume Next
'in case we didn't do anything, but something else did...
oDoc.Saved = True
'and close the document
oDoc.Close
Exit Function
l_err:
fHandleAFile = Err.Number
On Error Resume Next
'this would be the "error log" -- although immediate window only handles so much
Debug.Print "Error on " & oFile.Path
If MsgBox("There was an error processing:" & vbCr & oFile.Path & vbCr & _
"Do you wish to keep processing?", _
vbYesNo + vbDefaultButton1 + vbCritical, "Error!") = vbYes Then
'clear out the return value, to keep processing
fHandleAFile = 0
End If
Resume l_exit
End Function

[dcgrove]

Frosty, here are the answers to the questions you asked.

1. Yes, there will be non-MS word files in these folders.
2. This will need to apply protection to any native MS-word files.
3. See question 2!

I ran the new block of code on a test file with no errors. I then ran the entire process on a folder of files that contained a mix of .PDF's, .XLS, .DOC, and .DOCX files. It gave me an error on the .xls files but continued processing after I clicked OK on the message box (nice touch btw). The documents all show to have changes being tracked. When I was playing around with the macro recorder to see how VBA looked in Word, I recorded a macro that changed what is displayed to "Final" becuase I don't necessarily needing the changes to be visible. Can this be set as well?


.ShowRevisionsAndComments = False
.RevisionsView = wdRevisionsViewFinal

Thank you again for all of the effort you have put into this!

Clayton Grove

[Frosty]

Clayton,

Here's the updates... Note, I'm only including the changed routines, to keep the post a bit briefer, and to highlight for you what has actually changed.

You need to
1. replace the existing routine fHandleAFile,
2. include the new function fIncludeThisFile,
3. and include the new constant at the top of your module...

The biggest note here is that once a document is protected, this code still does nothing (which obviously includes documents you've already tested successfully on). So that's probably the last question:

1. When the code encounters a document which is already protected, do you want it to try and unprotect, and then protect for track changes? (if so, there is a line of code to uncomment in the update to fHandleAFile)

This will fail (or worse, work) in some circumstances which you want it to do the opposite (for example, if you ran this on a document which is protected for forms, you would turn off the protection for inserting form field info, and then turn on protection for track changes...). It's probably better to have it not do anything, but then you have to keep running it on new test documents if you add additional code... or you need to uncomment the .Unprotect line if you want to try and unprotect first.

You'll see a new constant at the top, for use with the filtering.

Option Explicit
'this is all the file types to work on... remove some or add some, just separate with the |
Public Const OPTION_WORKONTHESEFILETYPES As String = ".doc|.docx|.docm|.dot|.dotx|.dotm"
'-----------------------------------------------------------------------------------------------
' What you actually want to do with the file, now that you have it.
' Will passing back any error number
'-----------------------------------------------------------------------------------------------
Public Function fHandleAFile(oFile As Object) As Long
Dim oDoc As Document
Dim bProcessed As Boolean
On Error GoTo l_err

'use our "filter" routine to only work certain file types
If fIncludeThisFile(oFile) = False Then
bProcessed = False
GoTo l_exit
End If

Application.StatusBar = "Working on " & oFile.Path
'for testing error trapping structure
'Err.Raise 13

'now do what we want to do with the documents (open with visible false to be a little faster)
Set oDoc = Documents.Open(FileName:=oFile.Path, Visible:=OPTION_PROCESSDOCVISIBLY)

With oDoc
'uncomment this if you want to try to unprotect first
'.Unprotect OPTION_PROTECTIONPASSWORD

'if we're messing with protection, we need to check it before doing any changes
If .ProtectionType = wdNoProtection Then
bProcessed = True
'adjust the track changes according to our constant
.TrackRevisions = OPTION_TRACKREVISIONS
'this appears to do the same thing, but onthe document object
.ShowRevisions = False
'even though it's not visible, there is still a window object
With .Windows(1).View
.ShowRevisionsAndComments = False
.RevisionsView = wdRevisionsViewFinal
End With

'if we want to be "safe" and "use protection" then...
If OPTION_PROTECTION Then
'do it...
.Protect Type:=wdAllowOnlyRevisions, _
NoReset:=True, _
Password:=OPTION_PROTECTIONPASSWORD
End If
'save our changes
.Save
End If
End With
l_exit:
On Error Resume Next
'uncomment to get a debug window "report" what was was worked on
If bProcessed Then
'Debug.Print "Processed: " & oFile.Path
Else
'Debug.Print "Didn't process: " & oFile.Path
End If
'in case we didn't do anything, but something else did...
oDoc.Saved = True
'and close the document
oDoc.Close
Exit Function
l_err:
fHandleAFile = Err.Number
On Error Resume Next
'this would be the "error log" -- although immediate window only handles so much
Debug.Print "Error on " & oFile.Path
If MsgBox("There was an error processing:" & vbCr & oFile.Path & vbCr & _
"Do you wish to keep processing?", _
vbYesNo + vbDefaultButton1 + vbCritical, "Error!") = vbYes Then
'clear out the return value, to keep processing
fHandleAFile = 0
End If
Resume l_exit
End Function
'-----------------------------------------------------------------------------------------------
' Our "filter" function-- works off the constant OPTION_WORKONTHESEFILETYPES
' only if no optional argument is passedd
'-----------------------------------------------------------------------------------------------
Public Function fIncludeThisFile(oFile As Object, _
Optional sFileTypesSeparatedByPipes As String) As Boolean
Dim sFileExtension As String

On Error GoTo l_err
'get the file extension
sFileExtension = VBA.Right(oFile.Path, VBA.Len(oFile.Path) - VBA.InStrRev(oFile.Path, "."))
'see if we used the passed parameter or the constant
If sFileTypesSeparatedByPipes = "" Then
sFileTypesSeparatedByPipes = OPTION_WORKONTHESEFILETYPES
End If
If VBA.InStr(sFileTypesSeparatedByPipes, sFileExtension) <> 0 Then
fIncludeThisFile = True
End If

l_exit:
Exit Function
l_err:
'black box this function-- if any errors, don't work on the file
'***NOTE: this is a pessimistic approach, could change to True for optimistic approach
fIncludeThisFile = False
End Function