Home Header/footer unable to overwrite when plotting
Reply: 1

Header/footer unable to overwrite when plotting

Tefalpan
1#
Tefalpan Published in 2018-01-12 14:21:16Z

I created a excel vba script, mostly by using the record button and manually adding the preset headers and footers.

The problem that's occuring is that it seems to be unable to overwrite existing headers and footers. The code works fine if the header/footers are manually deleted at first before plotting using the macro (included below) little bit anoying and have not really got a clue how to solve it, are there any functions available to just delete all the existing printsettings before executing this script?

Application.PrintCommunication = False
    Application.Dialogs(xlDialogPrinterSetup).Show
    With ActiveSheet.PageSetup
        ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.PageSetup.PrintArea = "$A:$N"
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With

    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "Project X"
        .RightHeader = ""
        .LeftFooter = Sheets("instellingen").Cells(20, 2).Value
        .CenterFooter = ActiveSheet.Name & Chr(10) & Format(Sheets("instellingen").Cells(22, 2).Value, "dd-MM-yyyy")
        .RightFooter = "Pagina &P van de &N"
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub
Tefalpan
2#
Tefalpan Reply to 2018-01-12 16:50:45Z

Solved by changing both lines containing

Application.PrintCommunication = False

to

Application.PrintCommunication = True

Source:

Excel headers/footers won't change via VBA unless blank

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.300037 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO