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
by webwench
This discussion is closed to new replies.
Trending Software posts
Sort by Trending