vba 在 Excel 中为每个页面设置不同的页面页脚
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19745363/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set a different Page footer for each Page in Excel
提问by Joe.Net
I have tested the sample code in the corresponding MSDN article:
我已经测试了相应MSDN 文章中的示例代码：
Sub WorkWithPages()
' Fill random data:
Range("A1", "R100").Formula = "=RANDBETWEEN(1, 100)"
Dim pgs As Pages
Set pgs = PageSetup.Pages
PageSetup.DifferentFirstPageHeaderFooter = True
' Look in the Immediate window for this output:
Debug.Print "The current sheet can be printed on " & _
pgs.Count & " page(s)."
Dim pg As Page
Set pg = pgs(1)
pg.CenterHeader.Text = "This is the first page's header"
Set pg = pgs(2)
pg.CenterFooter.Text = "This is the second page's footer"
Set pg = pgs(pgs.Count)
pg.CenterFooter.Text = "This is the last page's center footer."
pg.LeftHeader.Text = "This is the last page's header"
' Note that Excel supports only distinct headers/footers
' for the first page, so headers and footers on the second
' and other pages are combined--the last value set overwrites
' the header/footer.
' See the values in the Immediate window.
' Note that the code disregards errors that occur--attempting
' to retrieve a header/footer setting that doesn't exist raises an error:
On Error Resume Next
Debug.Print "First page (CenterHeader) : " & pgs(1).CenterHeader.Text
Debug.Print "Second page (CenterHeader): " & pgs(2).CenterHeader.Text
Debug.Print "Second page (CenterFooter): " & pgs(2).CenterFooter.Text
Debug.Print "Third page (CenterFooter) : " & pgs(3).CenterFooter.Text
Debug.Print "Last page (LeftHeader) : " & pgs(pgs.Count).LeftHeader.Text
Debug.Print "Last page (CenterFooter) : " & pgs(pgs.Count).CenterFooter.Text
' In conclusion, use the Page class to retrieve information about headers
' and footers for specific pages. Use the PageSetup object to set the headers
' and footers, as it's clearer to set them there.
End Sub
But the values output by the Debug.Print "Second page (CenterFooter): " & pgs(2).CenterFooter.Textline differ from what is expected:
但是该Debug.Print "Second page (CenterFooter): " & pgs(2).CenterFooter.Text行输出的值与预期不同：
Second page (CenterFooter): This is the last page's center footer
Second page (CenterFooter): This is the last page's center footer
Instead of the right: Second page (CenterFooter): This is the second page's footer.
而不是正确的：Second page (CenterFooter): This is the second page's footer。
I have tried different things but all the CenterFooterkeep always the last input value. How can I change this behaviour such that each page gets the exact footer I want?
我尝试了不同的东西，但所有的东西都CenterFooter保持最后一个输入值。如何更改此行为，以便每个页面都获得我想要的确切页脚？
采纳答案by varocarbas
There are different footers/headers configurations but none of them allows to write a different value for each page. You can write a different text for the first page and for even/uneven pages; also you can add some formattingwith certain variations page to page (e.g., page number) but this is it. In Word the rules are equivalent.
有不同的页脚/页眉配置，但它们都不允许为每个页面写入不同的值。您可以为第一页和偶数/非偶数页编写不同的文本；您也可以在页面之间添加具有某些变体的一些格式（例如，页码），但就是这样。在 Word 中，规则是等效的。
Regarding the MSDN code you provided, one of its comments says:
关于您提供的 MSDN 代码，其中一条评论说：
Note that Excel supports only distinct headers/footers for the first page, so headers and footers on the second and other pages are combined--the last value set overwrites the header/footer.
请注意，Excel 仅支持第一页的不同页眉/页脚，因此第二页和其他页面上的页眉和页脚被合并——最后设置的值会覆盖页眉/页脚。
And thus this code is actually working as expected; although it is not too clear at first sight.
因此这段代码实际上按预期工作；虽然乍一看不太清楚。
回答by ThatJDoe
I know this is resurrecting a thread from the dead, but this is actually do-able in Excel VBA. I had to figure it out as I couldn't find an acceptable solution here, so I'm leaving this for the next poor sod:
我知道这是从死里复活一个线程，但这实际上在 Excel VBA 中是可行的。我必须弄清楚，因为我在这里找不到可接受的解决方案，所以我把它留给下一个可怜的草皮：
The solution is to create a subroutine to set the header and footer, and then call that routine within a loop in a different sub, calling each worksheet one at a time and managing what you want to say by variable.
解决方案是创建一个子例程来设置页眉和页脚，然后在不同子中的循环内调用该例程，一次调用每个工作表并通过变量管理您想说的内容。
So something like this:
所以像这样：
Sub InsertQuoteHeaderAndFooter(ByVal shtHeader As Worksheet, strText$)
shtHeader.PageSetup.RightFooter = "&""Calibri"" &8 &K434643" & strTxt & " | &P of &N"
End Sub
Then call it in a For loop, modulating shtHeader and strText as needed. Hope this helps the next archaeologist.
然后在 For 循环中调用它，根据需要调整 shtHeader 和 strText。希望这对下一位考古学家有所帮助。
