Solved

Automated Footer with VB

Posted on 2004-10-15
9
527 Views
Last Modified: 2007-11-27
We have a problem with lots of different people working the same file, printing of hard copies and then they end up getting mixed up and no-one knows which one is the latest without careful and painstaking studing and collaboration. So I want to add a VB script to the document that will automatically update a footer each time the document is saved, hence no more confusion.  I did a search and picked up the following VB script for Excel from ture (Ture Magnusson) in a post to another person's question and it helps with what I want but has some shortcomings that I would like to try and change.

It only does the Date in the footer. I want my footer to read "FileName Date Vers #"   FileName would be whatever the file is called, Date would be the file was last saved, and Vers # I would like to count upwards each time the file is saved.

And as if that wasn't enough I would like to also do this with Word and PowerPoint if at all possible.

VB script:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.PageSetup.RightFooter = Date
Next ws
End Sub


Can anyone help me rewrite this script to do what I want, or at least get closer.  I do understand that I will probably need different scripts for each type of document--that is not a problem.

Thanks,
David Eggleston
0
Comment
Question by:studeggle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 12321718
Hi David,
Here is a modification of Ture's sub that adds the filename, date and version number to the footer. The version number is stored in a named formula associated with the file. This sub goes in the ThisWorkbook code pane.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Dim str As String
Dim VersionNumber As Variant
On Error Resume Next
VersionNumber = Mid(ThisWorkbook.Names("VersionNumber").Value, 2) + 1
If Err <> 0 Then
    ThisWorkbook.Names.Add Name:="VersionNumber", RefersTo:="=1", Visible:=False
    VersionNumber = 1
    Err.Clear
End If
On Error GoTo 0

ThisWorkbook.Names("VersionNumber").RefersTo = "=" & VersionNumber
str = ThisWorkbook.Name & "   " & Format(Date, "mm/dd/yyyy") & "    Vers #" & VersionNumber
For Each ws In ThisWorkbook.Worksheets
    ws.PageSetup.RightFooter = str
Next ws
End Sub


Cheers!

Brad
0
 
LVL 1

Author Comment

by:studeggle
ID: 12321985
That works wonderfully  :D Thank you for the quick responce.  I don't supose you know how to tackle the other part of the question  "And as if that wasn't enough I would like to also do this with Word and PowerPoint if at all possible."?
0
 
LVL 81

Expert Comment

by:byundt
ID: 12322046
David,
I'd like to tackle the problem of making the code work in Word & PowerPoint. I'll have time either tonight or tomorrow.
Brad
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 1

Author Comment

by:studeggle
ID: 12322113
That will work :D
0
 
LVL 81

Expert Comment

by:byundt
ID: 12322243
David,
Which version(s) of Office are people using?
Brad
0
 
LVL 1

Author Comment

by:studeggle
ID: 12322556
Office XP
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 12330418
David,
For Word, you will need to create a class module to create the BeforeSave event. To do this, ALT + F11 to open the VBA Editor, then CTRL + R to open the Project Explorer. In the Project Explorer pane on the left, click on the name of your document (or template), then use the Insert...Class Module menu item to create a blank class module sheet.

I changed the name of the class module from the default value Class1 to EventClassModule. To do this, click on Class1 in the Project Explorer, then open the View...Properties menu item. Click on Class1 on the right side of the resulting dialog window--to the right of (Name). Type in the new name.

'Code for class module:
Public WithEvents appWord As Word.Application      'This statement must go before any subs or functions

Private Sub appWord_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
    Dim str As String
    Dim VersionNumber As Variant
    VersionNumber = ActiveDocument.BuiltInDocumentProperties("Revision number")
    str = ThisDocument.Name & "   " & Format(Date, "mm/dd/yyyy") & "    Vers #" & VersionNumber
    ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = str
End Sub


Next, you must initialize the class module when you open the document or create a new document. To do this, enter the following code in the ThisDocument code pane:

'Code for ThisDocument code pane
Dim X As New EventClassModule   'This statement must go above any subs or functions

Private Sub Document_New()
    RegisterEventClass
End Sub

Private Sub Document_Open()
    RegisterEventClass
End Sub

Sub RegisterEventClass()
    Set X.appWord = Word.Application
End Sub

To test the code, run the RegisterEventClass sub to register the class module, then click the Save button.
Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 12330527
David,
For PowerPoint, you will also need to create a class module, using the same approach as for Word. I named it EventClassModule once again. One big difference is that I didn't find a Presentation_Open event that would run when the presentation was opened. Instead, it only ran when you opened a second presentation. As a result, the event class must be registered manually by running the RegisterEventClass macro after the presentation has opened.

Another difference is the fact that PowerPoint produces both slides and notes. I didn't know whether you wanted the footer in the slides or notes, so the code includes both.

'Code for class module
Public WithEvents appPP As PowerPoint.Application   'This statement must go above any subs or functions

Private Sub appPP_PresentationSave(ByVal Pres As Presentation)

    Dim str As String
    Dim VersionNumber As Variant
   
    VersionNumber = ActivePresentation.BuiltInDocumentProperties("Revision number")
    str = ActivePresentation.Name & "   " & Format(Date, "mm/dd/yyyy") & "    Vers #" & VersionNumber
    If ActivePresentation.HasTitleMaster Then
        With ActivePresentation.TitleMaster.HeadersFooters
            With .Footer
                .Text = str
                .Visible = msoTrue
            End With
        '.SlideNumber.Visible = msoFalse
        End With
    End If
    With ActivePresentation.SlideMaster.HeadersFooters
        With .Footer
            .Text = str
            .Visible = msoTrue
        End With
        '.SlideNumber.Visible = msoFalse
    End With
    With ActivePresentation.Slides.Range.HeadersFooters
        With .Footer
            .Text = str
            .Visible = msoTrue
        End With
        '.SlideNumber.Visible = msoFalse
    End With
    With ActivePresentation.NotesMaster.HeadersFooters
        .Footer.Text = str
    End With
End Sub

'Code for regular module sheet:
Dim X As New EventClassModule   'This statement must go above any subs or functions

Sub RegisterEventClass()
    Set X.appPP = PowerPoint.Application
End Sub

To test the code, run the RegisterEventClass sub to register the class module (only need to do this once), then click the Save button.
Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 12330769
David,
It turns out that PowerPoint will support automatic events when you open a presentation file provided you install an add-in that registers the class module. Here's a link to the required code, developed by PowerPoint MVP Shyam Pillai http://www.mvps.org/skp/autoevents.htm
Brad
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question