Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Automated Footer with VB

Posted on 2004-10-15
9
Medium Priority
?
529 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
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…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

670 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