excelvbaoffice365worksheet

Why is the page header not saved when I save and close a workbook?


Using a macro, I have a workbook that I want to open if not already open, add/modify the page header, then save when closing it.

Everything appears to work until the save and close part. The page header is there when I check right before saving the workbook. After closing and saving changes, I open the workbook tgo find the header is missing.

I use a network drive so I tried using the C: drive instead just in case it was a network issue (an extreme long shot but I'm searching for any cause). I tried saving the workbook, re-opening it, changing the page header and then re-saving it. I've tried renaming the workbook when saving. I do need to use a variable for the header since it will be changing when I run it. I set it to "Saturday" here in order to keep the code as simple as necessary.

Each time the header would disappear when I re-open the workbook. This all works if I do it manually, but not when using a macro to do it.

    Option Explicit
    Sub Header_Test()

    Dim WB As Workbook
    Dim wPathName, wBookName, wExt, wFullPathName As String
    Dim LHeader As String
    
    wPathName = "C:\Temp\"
    wBookName = "Header Test"
    wExt = ".xlsx"
    wFullPathName = wPathName & wBookName & wExt
    LHeader = "Saturday"
    
    'Open workbook (if not open) and make it active.
    For Each WB In Workbooks
        If WB.Name = wBookName Then 'If Header Test workbook is open then make it active.
            WB.Activate
        Else
            Workbooks.Open (wFullPathName) 'Workbook isn't open so open it.
        End If
    Next WB
    
    'Add left header.
    Application.PrintCommunication = False
    With Sheets("Sheet1").PageSetup
        .LeftHeader = "&24 " & LHeader
        .HeaderMargin = Application.InchesToPoints(0.3)
        .Orientation = xlPortrait
        .PaperSize = xlPaperLetter
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    
    'Save and close
    Workbooks(wBookName).Close savechanges:=True
    
    End Sub

Solution

  • Not sure if the header is the correct way you wanted but here's the result on my end with the edited code:

    Sub Header_Test()
        Dim WB As Workbook, LHeader As String
        Dim wPathName, wBookName, wExt, wFullPathName As String
        
        wPathName = "Q:\Aankoop\Werkfiles Excel\Steven\Test\"
        wBookName = "Header Test"
        wExt = ".xlsx"
        wFullPathName = wPathName & wBookName & wExt
        LHeader = "Saturday"
        
        'Open workbook (if not open) and make it active.
        Set WB = Workbooks.Open(wFullPathName)
        'WB.Activate
        'Add left header.
        Application.PrintCommunication = False
        With WB.Sheets("Sheet1").PageSetup
            .LeftHeader = "&24 " & LHeader
            .HeaderMargin = Application.InchesToPoints(0.3)
            .Orientation = xlPortrait
            .PaperSize = xlPaperLetter
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .PrintErrors = xlPrintErrorsDisplayed
        End With
        Application.PrintCommunication = True 'See (Edit:) below code
        'Save and close
        WB.Save
        WB.Close
    
    End Sub
    

    (Didn't see BigBen's last comment on your question so this is a bit redundant) I didn't understand why you didn't just use Workbooks.Open(wFullPathName) to be honest, it's the easiest way to make sure that when the filepath exists, that it's opened because it doesn't care that it's already opened.
    The WB.Activate I felt was unnecessary as well as long as you fully declare the sheet, i.e. WB.Sheets("Sheet1")
    Last pointer, why use Workbooks(wBookName).Close when you already have a workbook variable WB?

    (Edit:) Can't really call this an edit since I haven't posted my answer yet but I started testing a bit more and stumbled upon your same issue @OP. The code worked when going step by step through the code, running it with F5 however, didn't change the file even when I tried changing a range and changing it back to original value after a save and then saving again. Nor did Application.Wait help. Then I looked at the documentation and noticed them using Application.PrintCommunication = True for the following
    Set the PrintCommunication property to True after setting properties to commit all cached PageSetup commands.
    Before you ask, I have no idea why going step by step does this automatically and when running a code you need the extra line..

    enter image description here