Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Change headers and footers in excel when printing

Posted on 2012-03-15
13
Medium Priority
?
361 Views
Last Modified: 2012-03-25
Hello,

I have an excel file with headers and footers, i need to print the same excel in two ways, one with the headers anf footers and the other without them.

I have done a macro that takes out the headers and put only de left and right footer, but when I user it, it changes all de print properties of all de pages that i have to print, some of them are landscape and the macro change them.

I need a way to do it, do you know if it is possible?

Thanks, and sorry for may english is not very good

Joana
0
Comment
Question by:joanaplicacions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 37727581
Joana

Can you post the code of this macro?

Is it meant to change anything apart from the header and footer?

What worksheet(s) are you printing and which ones should have the header/footer?
0
 

Author Comment

by:joanaplicacions
ID: 37729436
Hello Imnorie,

The problem is that i only want to change the header and the footer and it changes other properties, as if it is landscape or not.

This is my code:
Sub Macro1()
'
' Macro1 Macro
'
'Here I select the sheets to print
    Sheets(Array("CARATULA", "index", "PiG", "Detall despeses", "Punt mort", "BalançC", _
        "EOAF", "Anàlisi financera", "Anàlisi rendibilitat", "Anàlisi gestió", _
        "Detall Immobilitzat", "Detall Entitats Públiques", "Càlcul Impost S.", _
        "Informació complementaria", "Resultat-socis")).Select

'here I erase the header (right) and the footer (center)
   
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&D"
        .CenterFooter = ""
        .RightFooter = "&P/&N"
    End With
    Application.PrintCommunication = True

' Here I Print the file pdf

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Nuevo_nombre, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

'Here I try to put again the header and footer on all the pages

     Sheets(Array("CARATULA", "index", "PiG", "Detall despeses", "Punt mort", "BalançC", _
        "EOAF", "Anàlisi financera", "Anàlisi rendibilitat", "Anàlisi gestió", _
        "Detall Immobilitzat", "Detall Entitats Públiques", "Càlcul Impost S.", _
        "Informació complementaria", "Resultat-socis")).Select
    ActiveSheet.PageSetup.RightHeaderPicture.Filename = _
        "C:\projecte\comput\Logo_2_x_2.jpg"
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
       .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = "&G"
        .LeftFooter = "&D"
        .CenterFooter = "@pppp"
        .RightFooter = "&P/&N"
    End With
    Application.PrintCommunication = True

End Sub
0
 
LVL 34

Expert Comment

by:Norie
ID: 37729497
Instead of just changing/setting the header and footer change all the settings.

I know there's a lot of them and I'm pretty sure you don't need them all but sometimes with this sort of thing you just have to explicitly give all the arguments
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:joanaplicacions
ID: 37730438
I have tried this solution but is not valid for me for 2 reasons:

1.- In the macro I will have to go sheet by sheet to fix the parameters, as i have some pages that are landscape and others are portrait, and the margins and properties are diferent.

2.- If I chage a print parameter in a sheet I will be forced to go to the macro and change it, also, manually each time I make changes.

I was looking for another solution, that let me take out the headers and footers without changing the rest of parameters.
0
 
LVL 34

Expert Comment

by:Norie
ID: 37730526
Well I can't properly test, but the code you posted appears to work for me when I check in Print Preview.

So my suggestion of using all the parameters was obviously wrong and I apologize.
0
 

Author Comment

by:joanaplicacions
ID: 37730539
No problem,

Any other idea would be greatly appreciated.

Thanks for looking my problem
0
 
LVL 34

Expert Comment

by:Norie
ID: 37730630
What happens if you don't turn use PrintCommunication?

When I tried the code at first I didn't use that and the code worked, but now when I do use it all the pages do change their page setup.

I know it's meant to speed up things up when you are using page setup, but I didn't notice a difference.
0
 

Author Comment

by:joanaplicacions
ID: 37730852
Hello Imnorie,

I have tested taking out Application.PrintCommunication = False/true, in that case the landscape sheets remain as they are but it doesn't take out the logo and the center footer. It remains as it is.
0
 
LVL 34

Expert Comment

by:Norie
ID: 37731064
I can see where your code adds a logo but not where it removes it
0
 

Author Comment

by:joanaplicacions
ID: 37731096
Imnorie,

At the end I have solved the problem, in fact the two problems:

1.- I try to do the changes selecting all the sheets at the same time, and this didn't work properly
2.- As you said I have to take out Application.PrintCommunication = False/true becouse it makes the function ActiveSheet.PageSetup not work properly

Now I repeat the same code sheet by sheet and it respect the other properties.

    Sheets("index").Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&D"
        .CenterFooter = ""
        .RightFooter = "&P/&N"
    End With
    Sheets("PiG").Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&D"
        .CenterFooter = ""
        .RightFooter = "&P/&N"
    End With
........

Thankyou very much, having someone working with me has helped me a lot.
0
 
LVL 34

Expert Comment

by:Norie
ID: 37731177
I was going to suggest going through the sheets with a loop to set the page setup for each but got caught up in my original idea of setting all the parameters and how to implement it in the loop.

Then I went back to the original code and it worked but I only tried it on a few sheets with simple formatting and no logo.

Anyway, you got it to work.:)
0
 
LVL 34

Accepted Solution

by:
Norie earned 2000 total points
ID: 37762502
With my help.:)
0
 

Author Closing Comment

by:joanaplicacions
ID: 37762623
Yes , without you I wouldn't get the solution
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question