Excel页眉/页脚不会通过VBA更改，除非空白 [英] Excel headers/footers won't change via VBA unless blank

问题描述

免责声明：自从我（VBA）工作以来已经有几年了，所以这可能是一个问题，是因为我本来就是与我通常所处理的本质上是非常不同的语言。

Disclaimer: It's been a few years since I worked (a lot) with VBA, so this might be an issue caused by confusing myself with what is essentially a very different language from what I usually deal with.

所以;我有一本工作簿（Excel 2010），有多页（20多个），其中大部分是多页。为了使打印所有内容变得更简单，我想添加一些表格特定的标题，其中包括工作表的名称，页数等。

So; I've got a workbook (Excel 2010) with multiple sheets (20+), most of whom are multi-page. To make things easier when printing everything, I want to add some sheet-specific headers with amongst others the name of the sheet, number of pages and so on.

我已经写了一个很小的功能，应该（在理论上）通过迭代所有设置标题的工作表为我做。但是，由于某种原因，它仅在标题为空时才起作用;如果它已经有一个值，它拒绝覆盖一些未知的原因。

I've written a tiny function that should (in theory) do this for me by iterating over all the sheets setting the header. However, for some reason it only works if the header is empty; if it already has a value it refuses to overwrite for some unknown reason.

Dim sheetIndex, numsheets As Integer
sheetIndex = 1
numsheets = Sheets.Count

' Loop through each sheet, but don't set any of them to active
While sheetIndex <= numsheets
    Dim sheetname, role, labeltext As String
    sheetname = Sheets(sheetIndex).name
    role = GetRole(mode) 
    labeltext = "Some text - " & sheetname & " - " & role

    With Sheets(sheetIndex).PageSetup
        .LeftHeader = labeltext
        .CenterHeader = ""
        .RightHeader = "Page &[Page] / &[Pages]"
        .LeftFooter = "&[Date] - &[Time]"
        .CenterFooter = ""
        .RightFooter = "Page &P / &N"
    End With

    sheetIndex = sheetIndex + 1
Wend

推荐答案

我发现一个似乎可以替换文本的解决方案。无论什么原因，在宏中，您需要包含标题/ footer格式字符代码，以使其正常工作。

I found a solution that seems to work for replacing text. For whatever reason, in the macro, you need to include the header/footer format character codes in order for it to work properly.

此代码用于替换现有标题文本与新信息：

This code worked to replace existing header text with new information:

Sub test()
    Dim sht As Worksheet
    Set sht = Worksheets(1)
    sht.PageSetup.LeftHeader = "&L left text"
    sht.PageSetup.CenterHeader = "&C center Text"
    sht.PageSetup.RightHeader = "&R right text"
End Sub

没有& L ，和& R 在文本之前的代码，我无法让它工作。

Without the &L, &C, and &R codes before the text, I could not get it to work.

我发现一些有趣的行为是，如果您使用以下代码：

Some interesting behavior I found is that if you use the following code:

.CenterHeader = "&L some text"

它实际上会把一些文本在 LeftHeader 位置。这使我相信格式代码非常重要。

it will actually put the some text in the LeftHeader position. This led me to believe that the formatting codes were very important.

这篇关于Excel页眉/页脚不会通过VBA更改，除非空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！