VBA to Add 3 Aligned Footers with Custom Formatting and Text

[NoIdea]

Hey all,

I'm completely new to VBA, and have been assigned an unusually difficult (at least to me) task.

I have an excel file that already has a Macro written to create a formatted word document (this was given to me). I need to create a complex footer for this created word document. The footer must show the File Path (left justified), as well as "Page 1 of X" (right justified) and some text such as "***TEXT HERE*** that will be center justified, one line above the other two footers (so the file path can be long, and not run into this text).

This is pretty far over my head, the only VBA/Macro's I've done before have been simple. I don't the code for creating the doc, just to add and edit this footer.

This is all I could figure out, and (obviously) it does essentially none of what I need it to do...

[VBA]

With ActiveDocument.Sections(1)
.Footers(wdHeaderFooterPrimary).Range.Text = "File Path"
.Footers(wdHeaderFooterPrimary).PageNumbers.Add _
PageNumberAlignment:=wdAlignPageNumberRight, _
End With
[/VBA]

Thanks in advance!


FooterExample.jpg

[mdmackillop]

Hi
Simplest way is to create a Word template containing the Path and Page Numbering footers. For the text footer, use a document field, such as title and pass this to the template, something like the following (extracted from code I use for similar)

wdApp.Visible = True    'Make the application visible to the user (if wanted)
    Set MyDoc = wdApp.Documents.Add(Template:=P2W)
    Application.ScreenUpdating = False
    MyDoc.SaveAs Pth & TextBox4 & ".doc"
    wdApp.Selection.Paste
    Application.CutCopyMode = False
    wdApp.Run "FixLayout"   'This runs a Word Template macro
    With MyDoc
        .BuiltinDocumentProperties(1) = TextBox3
        .BuiltinDocumentProperties(2) = "BILL NO. " & TextBox1 & " - " & UCase(TextBox2)
        .BuiltinDocumentProperties(3) = TextBox1
    End With

[NoIdea]

I understand the context of what you are describing, but I have two questions.

1. When I enter this text into the Macro that is already created (the one in Excel which displays the word doc); nothing happens
2. I tried creating a word template from the suggested 3 footer template already, but when i went to run the macro, I got an error (It said something about not being able to access the template).

Also, what would I need to change in your example to display what I had referenced? I'm very clearly new to this.

Thanks for your help, I look forward to your response

[mdmackillop]

Here is a working example, Add files to C:\VBAX or change code to suit your location

Sub Test()
'Requires reference to Word Object Library
    Dim wdapp, mydoc
    Dim Pth
    
    Pth = "C:\VBAX\"
    
'Copy some data
Range("C4:C17").Copy   

'See if Word is already running
    On Error Resume Next
    Set wdapp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
        'Launch a new instance of Word
        Err.Clear
        On Error GoTo Error_Handler
        Set wdapp = CreateObject("Word.Application")
    End If
    wdapp.Visible = True    'Make the application visible to the user (if wanted)
'Create new doc based on template and save    
Set mydoc = wdapp.Documents.Add(Template:=Pth & "Sample.dotx")
    Application.ScreenUpdating = False
    mydoc.SaveAs Pth & "Sample1.doc"
    
'Paste data into Word
wdapp.Selection.Paste
    
Application.CutCopyMode = False
    With mydoc
        .BuiltinDocumentProperties(1) = Cells(1, 1)
    End With
Error_Handler:
End Sub

[NoIdea]

Is this supposed to create a word document with that text? When I run the Macro, the Word window will open, but there is no document open/created. Also, I'm curious after just looking at the code (because I couldn't get an output), does this Macro only copy/paste info from those cells into Word, or does it post it into a formatted footer? I don't have any info in the excel file that I need to paste; I just need a footer made as a standalone added text in the word file made by the macro.

Sorry if this is confusing (or if I'm downright incorrect, which is entirely possible), it's very specific and hard to explain. Especially when you haven't learned VBA

Thanks again

[mdmackillop]

The zip folder contains 2 files, Save both of these to a folder called C:\VBAX
Open the excel file and go to the code page. Make sure you have a reference to Word Object Library and run the code.

[NoIdea]

This is the process I followed, and the Word window will open, but a document will not. I also tried changing the path (code, too) and saving it elsewhere, to no avail

[mdmackillop]

What version of Word are you using? Have you tried stepping through the code?

[NoIdea]

I have Version 14.0.

The good news for both of us, is that it looks like I'm slowly figuring out my stupidity. I entered the code into the Macro I have already, but I am still having some problems. The code I already have will generate a word doc; however after adding your code it will only add 17 blank lines to the beginning of the Doc. There is unfortunately no footer added.

With that being said, I did open the Sample word doc you provided, and i saw it worked there. (however, I think the left footer was file name instead of file path. This may be because the Doc wasn't saved.)

I'm not sure how to tweak the code to get the footer to work in the doc I have; any help is appreciated!

[mdmackillop]

The path and page number are part of the Word template: you do not need to provide this information. The centre footer is a field which is based upon the Word document title. The Macro passes this information only in this line.
.BuiltinDocumentProperties(1) = Cells(1, 1)
This could probably be written to the footer, but I was just using some old code which works for me.

Save your word document as a template with the footer as you want it including the title field.

[NoIdea]

Ah I see, it's starting to make more sense.

Is there any way to get the center footer to just be some entered text? As I believe I just need it to display something along the lines of "Confidential"

Also, any idea why the footer isn't showing up at all in the document I'm using? That's the main issue I'm trying to address at the moment.

[mdmackillop]

Can you attach your Word Document?

[mdmackillop]

Sub Test()
'Requires reference to Word Object Library
    Dim wdapp, mydoc
    Dim Pth
    
    
    Pth = "C:\VBAX\"
    
    'See if Word is already running
    On Error Resume Next
    Set wdapp = GetObject(, "Word.Application")


    If Err.Number <> 0 Then
        'Launch a new instance of Word
        Err.Clear
        Set wdapp = CreateObject("Word.Application")
    End If
    
    wdapp.Visible = True    'Make the application visible to the user (if wanted)
    Set mydoc = wdapp.Documents.Open(Pth & "temp.docx")
    Application.ScreenUpdating = False
    With mydoc
         .Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore "Confidential"
    End With
End Sub

[NoIdea]

I inserted this code into the current Excel Macro, and again, nothing happened. There is no footer in the created Word doc and the document looks unchanged with this added code. Is there anything special that has to be done to get the footer to work? I'm working with an excel file in my downloads, so I changed the path to that. However, I can't think of anything else I would be doing incorrectly.

Do I have to have a word doc already created / saved to a file for a footer to be added? Because the excel file creates a word doc that does not yet exist, and it's unsaved. Not sure if this is relevant, but I remember I could only get your sample to work with the attached word Doc you already had.

Thanks again

[mdmackillop]

I believe I've addressed all these points previously. Have you added the footer fields to your Word Document? see Post #12

[NoIdea]

I'm afraid I don't know what you mean when you say "have you added the footer fields to your Word Document"; is that not the purpose of the Macro - to add the footer to the Document?

And I would have uploaded the word Document long ago, but unfortunately the Document is confidential (hence the tag in the footer). Also, the document is essentially blank besides the text my current Macro is adding. If you can inform me what you would be looking for if I were able to pass it on to you, I would be more than willing to inform you.

[mdmackillop]

Why would you add information from Excel which is a property of the Word Document. With regard to your document contents you can delete all the text. Post it blank with the document name and path.

[NoIdea]

The whole Word document is the result of adding information from Excel; the Macro in Excel creates the whole word document and formats it accordingly, so why wouldn't you add information from excel that's a property of the Word Doc? It seems that's the same way you suggested to do it from the beginning. (A genuine question, my intention isn't to sound snarky. I could be misinterpreting your comment)

The word document is created with the default name of "Document1" (as it hasn't been saved anywhere). When saved, the name suggested is the 1st line of the document "Correspondence".

The path I'm working with is C:\Users\XXX\Downloads

Document1.docx

[mdmackillop]

Excel does not know the number of pages in the word document, so how do you provide it? You use a word document or template with an appropriate footer field. You can add the path field also in the appropriate location. You then use this code

With mydoc
         .Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore "Confidential" 
    End With

to add the custom text into the preformatted footer.

[NoIdea]

I see your point, I was unaware this was a functionality limitation between the two programs. With that being said, it's also not possible for me to use a pre-formatted word doc, as the macro creates a completely new document every time I run a report (unless there is a way to attach a template including a formatted footer to the Macro). But, again, I may be misunderstanding; I just haven't had any luck with this so far.