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
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..