Solved

Change headers and footers in excel when printing

Posted on 2012-03-15
13
342 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now