Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2012
    Location
    West Yorkshire
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA to update Document Footers in a Template during a Mailmerge

    Hi guys....
    I have a third party application which executes a Word Mailmerge, recovering details from its Database to populate the Document associated with the Word Template.
    Although moderately skilled in VBA, I'm getting bamboozled with this latest project.
    I need to pick up details from "Sales Enquiry", one of the Mailmerge fields (after its been populated), then, depending on the value in this field, optionally modify the initial "Save As" name, and insert some text into the Footer of the Document before it is saved.
    I have code which extracts the value from the populated field, but when I try to manipulate the Footers in the Document, I only succeed in manipulating the Footers in the Template.

    Can any kind soul offer assistance?

    I used Alt+F8 in the Template to create the base version of the following code (to intercept all "Save" functionality):-
    Code:
    Sub FileSave()
    ' FileSave Macro
    ' Saves the active document or template
    '*
    '** The following code has been replaced.
    '*
    '    ActiveDocument.Save
    '*
    '** Invoke our Usercode.
    '*
      Call USave
    End Sub
    Sub FileSaveAll()
    ' FileSaveAll Macro
    ' Saves all open files, macros, and building blocks, prompting for each one separately
    '*
    '** The following code has been replaced.
    '*
    '    Documents.Save
    '*
    '** Invoke our Usercode.
    '*
      Call USave
    End Sub
    Sub FileSaveAs()
    ' FileSaveAs Macro
    ' Saves a copy of the document in a separate file
    '*
    '** The following code has been replaced.
    '*
    '    Dialogs(wdDialogFileSaveAs).Show
    '*
    '** Invoke our Usercode.
    '*
      Call USave
    End Sub
    Relevant sections of the "USave" code follows.
    Code:
    Option Explicit
    Public Sub USave()
    '*
    '** Offer a likely SaveAs name.
    '*
    Dim dlgSA                   As FileDialog
    Dim intDlgSAResult          As Integer
    Dim strBigSE                As String
    Dim strNewPath              As String
    Dim strSaveAsFull           As String
    '*
    '** REMOVED CODE HERE WHICH DETERMINES IF THE
    '** FOLLOWING CODE IS INVOKED.
    '*
    
    '*
    '** Changes ARE required!
    '** Create a File Dialogue Object.
    '*
      Set dlgSA = Application.FileDialog(msoFileDialogSaveAs)
    '*
    '** Preset the name offering.
    '*
      dlgSA.InitialFileName = "Zone Chart " & _
                              MonthName(Month(Date)) & " " & _
                              Day(Date) & " " & _
                              Year(Date)
     
    '*
    '** The .Show method shows the SaveAs screen, and recovers
    '** the User's supplied details. If set to -1, means continue.
    '*
      intDlgSAResult = dlgSA.Show
      If intDlgSAResult = 0 Then Exit Sub      'Cancel
      If intDlgSAResult <> -1 Then Exit Sub    'Weird!
    
    '*
    '** User selected OK!
    '** We have the details, but haven't yet executed the SaveAs.
    '** We later use the .Execute method to do this.
    '*
      strSaveAsFull = dlgSA.InitialFileName
      Call FindSENo         'Results in gstrSENumber.
      
      If gstrSENumber = "" Then
        Call MsgBox("Unable to locate Sales Enquiry Number", vbCritical)
        Exit Sub
      End If
        
    
      Call ModifySavedFooters
      Call ReplaceFooters
      
      dlgSA.Execute
      
      Set dlgSA = Nothing
    '*
    '** Now ensure the Saved attribute is set.
    '*
      ActiveDocument.Saved = True
    End Sub 'USave
    The code used to replace the Footers from values saved in the array garrFooterText is
    Code:
    Public Sub ReplaceFooters()
    Dim intPtr                  As Integer
      With ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary)
        .Range.Delete
        For intPtr = 0 To UBound(garrFooterText)
          .Range.Font.Size = 8
          .Range.InsertAfter garrFooterText(intPtr)
        Next intPtr
     End With
    End Sub 'ReplaceFooters

  2. #2
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    First, look into the ASK field in Word.
    Second, look into the STYLEREF field in Word.

    The StyleRef field is designed for use in headers and footers and dynamically repeat information from the body of the document that is formatted using a specific style. You can create a character style that looks no different from the rest of your body text to tag information.

    If you can use this, you do not manipulate the footers through code at all.
    Charles Kyle Kenyon
    Madison, Wisconsin

  3. #3
    New Lounger
    Join Date
    Aug 2012
    Location
    West Yorkshire
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks, but...

    Charles....
    Thanks for your thoughtful reply.
    I've looked at the ASK function, and whilst it will no doubt be useful in the future, it's not appropriate to my current project.
    The STYLEREF option may be useful, but not without some significant rejigging.
    My Boss isn't too happy about duplicated data - "Why do I have to have it in the body as well as the footer?" OK, I could cheat by having thye body
    My problem really is how to direct the VBA which rewrites the Footers to act on the DOCUMENT rather than the TEMPLATE.
    I suspect that the code
    Code:
    With ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary)
    may be pointing at the wrong object, but I can't for the life of me find any indications as to how the "Document" object (as opposed to the "Template" object) may be referenced.
    I've tried
    Code:
    Dim objWdDoc As Word.Document
      With objWdDoc.Sections(1).Footers(wdHeaderFooterPrimary)
    to see if I can "force" the object to be the Document rather than the Template, but to no avail.
    Any ideas?

  4. #4
    Silver Lounger Charles Kenyon's Avatar
    Join Date
    Jan 2001
    Location
    Sun Prairie, Wisconsin, Wisconsin, USA
    Posts
    2,049
    Thanks
    124
    Thanked 119 Times in 116 Posts
    ActiveDocument is the document which has the focus. If you have the template OPEN, it may be the ActiveDocument. If you have created a new document based on the template that new document should be the ActiveDcoument.

    So, I guess, the question is: What code are you using to create the new document. If you are using and open command and then SaveAs, you have problems. That is not how a template is used.

    For instance, assuming that the string sTemplatesPath gives the path to the template, the following creates a new document based on an existing template:
    Code:
        Documents.Add Template:= _
            sTemplatesPath & "Kenyon Legal Letter.dot" _
            , NewTemplate:=False
    Please note, I know next to nothing about programming Word from outside Word.
    Last edited by Charles Kenyon; 2013-11-04 at 09:44.
    Charles Kyle Kenyon
    Madison, Wisconsin

  5. #5
    New Lounger
    Join Date
    Aug 2012
    Location
    West Yorkshire
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hmm...

    Charles....

    Unfortunately I can't access or control the "alien" code creating the Document from the Template - a "bought in" package called C4W from Mentor Business Systems.

    The package uses Word's Mailmerge functionality to populate a standard Word template with data from C4W's database fields. From within C4W you click on an Icon which opens a fairly standard Microsoft "File Open" dialogue box which just allows you to pick templates.

    C4W then issues a splash screen saying "Merging", then, when the splash screen vanishes the minimised Word entry in the Taskbar flashes so you can look at and optionally edit the Document.

    When you choose to "Save" the document, C4W decides where to save the document.

    All I'm trying to do is put the path in the footer, ('cos I can VERY accurately predict will be saved - it shows as "Document 1" during the review).
    For reasons related to my Company I also want to modify the name under which the document is saved. I thought to do this by intercepting the Save / Save As command, executing my own Save routine, and telling Word that the Save had been accomplished.

    None of this "interference" affects C4W's functionality.

    As Mentor are busy developing the new super improved version (full of functionality I don't want or need, and with an increased price tag), they are reluctant to advise or help me, telling me "everything will work perfectly in the future" (I've been in I.T. long enough to know that's slightly less likely than Hell freezing over).

    The code I have is very close, if only I can persuade it to act on the right object....

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by Sorcerer13 View Post
    All I'm trying to do is put the path in the footer, ('cos I can VERY accurately predict will be saved - it shows as "Document 1" during the review).
    For reasons related to my Company I also want to modify the name under which the document is saved. I thought to do this by intercepting the Save / Save As command, executing my own Save routine, and telling Word that the Save had been accomplished.
    In that case, why not put a FILENAME field in the template's footer, using the /p switch?

    So far, all the code you've posted (which doesn't include the 'ModifySavedFooters' sub), points to the ActiveDocument which, as Charles said, normally should be the actual document rather than its template.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •