Discussion Locked

Add footers to all files in a folder

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Is there anyway to add footer information to every file in a folder other than to open each file separately? I have an individual involved in a large law suit and he needs to add "Created On" info to the footer of each Word doc within a folder. This also needs to be done with all the Excel files. Excel does not provide a "Created On" option on its footer utility. How do I bring in that data in Excel? (&what?) Right now they are cutting and pasting the data manually from the statistics tab on the properties box. Any way to add this data to all files in a folder? There are hundreds of documents which require the updated footer information.

7 replies

by peyison

This can be done with a VBA macro pretty easily. At least in Word. The information is sort of available in Excel, via VBA, but the feature is pretty buggy.

Let me know if you'd like info on this type of solution.

by webwench

I would appreciate more info on the VBA macro to achieve my goals.

My VBA background is next to nil so speak newbie to me please!

thank you for your response.

H. Hamilton
Ormond Beach, FL

by peyison

Hi,

The following VBA macros should do what you're looking for.

Here are the routines to do this in Word:

'this is the starting point. Enter the directory that the files are in, and additional text to be displayed in the footer (if any - use "" for no text).
'--------------
Sub Start()
ProcessDir "c:\test\test 2", "Test Msg"
End Sub
'---------

These next two routines do all the work:

'-----------------
Sub ProcessDir(asDirectory As String, asMsg As String)
Dim oFileSys As Scripting.FileSystemObject
Dim oFiles As Scripting.Files
Dim oFile As Scripting.File
Dim oFolder As Scripting.Folder
Dim sDateCreated As String
Dim sPath As String

'loop through the files in the folder and process .doc files only
Set oFileSys = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFileSys.GetFolder(asDirectory)
Set oFiles = oFolder.Files

For Each oFile In oFiles
sPath = LCase$(oFile.Path)
If Right(sPath, 4) = ".doc" Then
sDateCreated = "Date Created: " & oFile.DateCreated
AddFooter sPath, sDateCreated, asMsg
End If
Set oFile = Nothing
Next

Set oFileSys = Nothing

End Sub

Sub AddFooter(asFileName As String, asDateCreated As String, asMsg AsString)

Documents.Open FileName:=asFileName

'add the text to the footer, if there are multiple sections, this only adds to the first section.
With ActiveDocument.Sections(1)
.Footers(wdHeaderFooterPrimary).Range.Text = asMsg & " " & asDateCreated
End With

ActiveDocument.Save
ActiveDocument.Close
End Sub

'-------------

If a doc has multiple sections, this code will only add a footer to the first one. If the section already has a footer, it will be replaced. Both of these can be modified if desired (add footer to all sections, and append date created to existing footer).

More to follow....

by peyison

Here are the routines for Excel:

'----------
Sub Start()
ProcessDir "c:\test\test 2", "Test Msg"
End Sub

Sub ProcessDir(asDirectory As String, asMsg As String)
Dim oFileSys As Scripting.FileSystemObject
Dim oFiles As Scripting.Files
Dim oFile As Scripting.File
Dim oFolder As Scripting.Folder
Dim sDateCreated As String
Dim sPath As String

Set oFileSys = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFileSys.GetFolder(asDirectory)
Set oFiles = oFolder.Files

For Each oFile In oFiles
sPath = LCase$(oFile.Path)
If Right(sPath, 4) = ".xls" Then
sDateCreated = "Date Created: " & oFile.DateCreated
AddFooter sPath, sDateCreated, asMsg
End If
Set oFile = Nothing
Next

Set oFileSys = Nothing

End Sub

Sub AddFooter(asFileName As String, asDateCreated As String, asMsg As String)

Workbooks.Open Filename:=asFileName

With ActiveWorkbook.ActiveSheet.PageSetup
.LeftFooter = asMsg
.CenterFooter = asDateCreated
.RightFooter = ""
End With

ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub
'----------------

Same issues as Word as far as replacing any existing footer.

If you want help on where to paste these routines in the VBA editor, let me know.

If you want, I can email the Word and Excel files that contain this code.

Hope this helps.

by peyison

Forgot to mention - you have to have a reference to the Microsoft Scripting Runtime in both Word and Excel for this code to work. Let me know if you need details.

by webwench

Thank you for all your help. I have copied the code into a standard word doc and sent it to my web based e-mail so I can cut and paste it for loading and testing when I go to that office later today.

I am unfamiliar with Microsoft Scripting Runtime but I can load the code into the editor OK (I think....*smile*). Please e-mail me with any other thoughts or explanations (webwench@hotmail.com) That way I can let you know how this worked out. I am accepting the answer whether it works or notso you can be awarded the points....I really appreciate your efforts to help.

Sincerely,

H. Hamilton
Ormond Beach, FL

This discussion is closed to new replies.