Error trying to enter custom footer in Word from Excel

[sharky12345]

I'm trying to enter a custom left footer in a Word document from excel but I'm getting an error 438 - 'Object doesn't support this property or method'.

Here's the code;

Dim objWord As Object
Dim objDoc As Object

'Hide background file open
Application.ScreenUpdating = True

'Open Word if not already open
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.documents.Add(ThisWorkbook.path & "\Handover Pack.dotx") 'objWord.Documents.Add

objWord.Visible = True

'Front Page
objDoc.Tables(1).Cell(1, 1).Range.Text = "Operation " & Sheets("SETUP").Range("B1").Value
objDoc.Tables(1).Cell(2, 1).Range.Text = Sheets("SETUP").Range("D3").Value & " RTC"
objDoc.Tables(1).Cell(3, 1).Range.Text = Sheets("SETUP").Range("B6").Value
objDoc.Tables(1).Cell(4, 1).Range.Text = Sheets("SETUP").Range("B5").Text
objDoc.PageSetup.LeftFooter = "Op " & Sheets("SETUP").Range("B1").Value & " Handover"

and it fails on this line;

objDoc.PageSetup.LeftFooter = "Op " & Sheets("SETUP").Range("B1").Value & " Handover"

Anyone help?

 

[Macropod]

Word doesn't have a LeftFooter - that's an Excel property. If you want to simulate the Excel left-footer, you can do that either via paragraph formatting or the insertion of a table into the header. What Word does have, which Excel doesn't, is three header & footer objects per Section (primary, first page & even page). Accordingly, your code needs to tell Word which footer in which Section you want to work with. The appropriate approach depends on what it is you're trying to achieve. To that end, you might try something along the lines of:

Dim objWord As Object, objDoc As Object, xlWkSht As worksheet
Const wdHeaderFooterPrimary As Long = 1
Const wdHeaderFooterFirstPage As Long = 2
Const wdHeaderFooterEvenPages As Long = 3
Set xlWkSht = Sheets("SETUP")
'Open Word if not already open
Set objWord = CreateObject("Word.Application")
With objWord
  Set objDoc = .Documents.Add(ThisWorkbook.Path & "\Handover Pack.dotx")
  With objDoc
  'Front Page
    With .Sections(1)
      'Configure the first Section so that header/footer content only appears on the first page
      .PageSetup.DifferentFirstPageHeaderFooter = True
      'Add content to the first page footer
      .Footers(wdHeaderFooterFirstPage).Range.Text = "Op " & xlWkSht.Range("B1").Value & " Handover"
    End With
    With .Tables(1)
      .Cell(1, 1).Range.Text = "Operation " & xlWkSht.Range("B1").Value
      .Cell(2, 1).Range.Text = xlWkSht.Range("D3").Value & " RTC"
      .Cell(3, 1).Range.Text = xlWkSht.Range("B6").Value
      .Cell(4, 1).Range.Text = xlWkSht.Range("B5").Text
    End With
  End With
  .Visible = True
End With
Set objDoc = Nothing: Set objWord = Nothing: Set xlWkSht = Nothing

 

[sharky12345]

Thanks Paul - I was trying to set the left of the bottom 3 footer sections so what you've explained makes sense.

I'll try your suggestion and report back.

 

[sharky12345]

Paul - that works a treat, thanks!

Just out of interest, if I wanted to apply a custom footer to each of the 3 sections how would that look?

 

[Macropod]

To each of three Sections, or to each of the three footers in the first Section? Or even one or more footers in multiple Sections?

 

[sharky12345]

Sorry to be a pain, but how about an idea of how to do each of 3 sections on all pages, (i.e. same footer on every page), and each of three footers in first section only?

Also, not sure if it's possible, but can the font size and bold be adjusted by this method?

 

[Macropod]

To apply the same footer to all pages in the first Section, you could replace;

    With .Sections(1)
      'Configure the first Section so that header/footer content only appears on the first page
      .PageSetup.DifferentFirstPageHeaderFooter = True
      'Add content to the first page footer
      .Footers(wdHeaderFooterFirstPage).Range.Text = "Op " & xlWkSht.Range("B1").Value & " Handover"
    End With

with:

    With .Sections(1)
      .PageSetup.DifferentFirstPageHeaderFooter = False
      'Add content to the first page footer
      .Footers(wdHeaderFooterPrimary).Range.Text = "Op " & xlWkSht.Range("B1").Value & " Handover"
    End With

In deed, if you're working with a template that doesn't already have a 'different first page' footer defined, you could omit:

      .PageSetup.DifferentFirstPageHeaderFooter = False

 

[Macropod]

how about an idea of how to do each of 3 sections on all pages, (i.e. same footer on every page), and each of three footers in first section only?

You seem not to understand the Word terminology, which makes it difficult to address your requirements. A Word document can contain multiple Sections - pretty much as many as you care to give it. Good document design, though, dictates not adding them unnecessarily. Except for the first Section in a document, each Section is defined via a Section break - and these come in various flavours, the most common being 'continuous' and 'next page'. A given Section could span anything from part of a page (if using continuous breaks) to tens or even thousands of pages.

Each Section in a document has three header and footer objects - primary, first page & even page. Unless you configure the document's page layout to use a different 'first page' and/or 'even page' setup, you'd work exclusively with the primary header or footer. And, just to make things more flexible, any given header or footer (except for the first Section) can be linked to its counterpart in the previous Section.

can the font size and bold be adjusted by this method?

Yes, you can apply whatever font attributes you want, to any or all of the text. You can also change paragraph alignments, line spacing, etc. Ideally, all of this would be done by applying the appropriate Style definitions to whatever you want to affect.

 

[sharky12345]

Paul, thank you, and you're right - I didn't understand!

When I referred to 'sections' I meant the 3 parts of the footer, (left, centre and right), but appreciate that's not correct terminology so thanks.

 

[Macropod]

To activate and populate all three footers in the first Section, you could use code like:

    With .Sections(1)
      'Configure the first Section for a header/footer on the first page
      .PageSetup.DifferentFirstPageHeaderFooter = True
      'Configure the first Section for a header/footer content on even pages
      .PageSetup.OddAndEvenPagesHeaderFooter = True
      'Add content to the primary footer
      With .Footers(wdHeaderFooterPrimary).Range
        .Text = "Primary Footer"
        With .Font
          .Size = 16
          .Name = "Arial"
        End With
        With .Words.First.Font
          .Bold = True
          .Italic = True
        End With
      End With
      'Add content to the first page footer
      .Footers(wdHeaderFooterFirstPage).Range.Text = "First Page Footer"
      'Add content to the even pages footer
      .Footers(wdHeaderFooterEvenPages).Range.Text = "Even Pages Footer"
    End With

For the primary footer, I've added some code to play with the text formatting.