Solved

Change headers and footers in excel when printing

Posted on 2012-03-15
13
345 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
  • 7
  • 6
13 Comments
 
LVL 33

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 33

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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

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 33

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 33

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 33

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 33

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 33

Accepted Solution

by:
Norie earned 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

772 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