Skip to Content
avatar image
-1
Former Member

Unable to delete header / footer in excel 2010

Hi Experts ,

Hi Experts ,

I need to delete header / footer in excel 2010 through OLE .
I have provided the macro recording and the corresponding OLE code but it is not working for me .

I need your urgent help on this. Please help.

OLE code:

GET PROPERTY OF excel 'ActiveSheet' = sheet.
GET PROPERTY OF sheet 'PageSetup' = page.
* GET PROPERTY OF excel 'ActiveSheet' = sheet.
SET PROPERTY OF excel 'PrintCommunication' = 0.


set property of page 'LeftHeader' = l_left.
*
* GET PROPERTY OF sheet 'PageSetup' = page.
set property of page 'CenterHeader' = l_left.
set property of page 'RightHeader' = l_left.

* set property of page 'LeftFooter' = '""'.
set property of page 'LeftFooter' = 0.

set property of page 'CenterFooter' = 0.
* set property of w_centre 'Text' = 'footer4'.
set property of page 'RightFooter' = 0.

set property of page 'LeftMargin' = 50.
set property of page 'RightMargin' = 50.
set property of page 'TopMargin' = 54.
set property of page 'BottomMargin' = 54.
set property of page 'HeaderMargin' = 21.
set property of page 'FooterMargin' = 21.
set property of page 'Zoom' = 100.
set property of page 'OddAndEvenPagesHeaderFooter' = 0.
set property of page 'DifferentFirstPageHeaderFooter' = 0.

SET PROPERTY OF excel 'ScaleWithDocHeaderFooter' = 1.
set property of excel 'AlignMarginsHeaderFooter' = 1.

get property of page 'EvenPage' = EVEN.
GET PROPERTY OF PAGE 'FirstPage' = first.
get property of even 'LeftHeader' = evenleft.
get property of even 'CenterHeader' = evencenter.
get property of even 'RightHeader' = evenright.
get property of first 'LeftHeader' = firstleft.
get property of first 'CenterHeader' = firstcenter.
get property of first 'RightHeader' = firstright.
set property of evenleft 'Text' = l_left.
set property of evencenter 'Text' = l_left.
set property of evenright 'Text' = l_left.
set property of firstleft 'Text' = l_left.
set property of firstcenter 'Text' = l_left.
set property of firstright 'Text' = l_left.

SET PROPERTY OF excel 'PrintCommunication' = 1.

Here l_left is a blank string which corresponds to "" in the macro recording.


Macro recording

Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.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)
.Zoom = 100
.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
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = "header1"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.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)
.Zoom = 100
.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
Range("H56").Select

Regards
Sweta

Add comment
10|10000 characters needed characters exceeded

  • Former Member

    Please ignore the above code as I was trying with different options.
    Here is the final code which is not working for me. Please let me know how can I make it work.

    OLE code:

    GET PROPERTY OF excel 'ActiveSheet' = sheet.
    GET PROPERTY OF sheet 'PageSetup' = page.
    SET PROPERTY OF excel 'PrintCommunication' = 0.


    set property of page 'LeftHeader' = l_left.

    set property of page 'CenterHeader' = l_left.
    set property of page 'RightHeader' = l_left.

    set property of page 'LeftFooter' = l_left

    set property of page 'CenterFooter' = l_left
    set property of page 'RightFooter' = l_left.

    set property of page 'LeftMargin' = 50.
    set property of page 'RightMargin' = 50.
    set property of page 'TopMargin' = 54.
    set property of page 'BottomMargin' = 54.
    set property of page 'HeaderMargin' = 21.
    set property of page 'FooterMargin' = 21.
    set property of page 'Zoom' = 100.
    set property of page 'OddAndEvenPagesHeaderFooter' = 0.
    set property of page 'DifferentFirstPageHeaderFooter' = 0.

    SET PROPERTY OF excel 'ScaleWithDocHeaderFooter' = 1.
    set property of excel 'AlignMarginsHeaderFooter' = 1.

    get property of page 'EvenPage' = EVEN.
    GET PROPERTY OF PAGE 'FirstPage' = first.
    get property of even 'LeftHeader' = evenleft.
    get property of even 'CenterHeader' = evencenter.
    get property of even 'RightHeader' = evenright.
    get property of first 'LeftHeader' = firstleft.
    get property of first 'CenterHeader' = firstcenter.
    get property of first 'RightHeader' = firstright.
    set property of evenleft 'Text' = l_left.
    set property of evencenter 'Text' = l_left.
    set property of evenright 'Text' = l_left.
    set property of firstleft 'Text' = l_left.
    set property of firstcenter 'Text' = l_left.
    set property of firstright 'Text' = l_left.

    SET PROPERTY OF excel 'PrintCommunication' = 1.

    l_left is a blank string..

  • Is that a comment for the answer I have given? I said that PrintCommunication is buggy, and you must use ExecuteExcel4Macro (cf link provided).

  • Get RSS Feed

3 Answers

  • Mar 30, 2017 at 04:57 AM

    In Excel, macro recording for PrintCommunication is buggy. It must be rewritten manually, and executed through ExecuteExcel4Macro (from Excel 2010). For more info, see SCN thread mentioning ExecuteExcel4Macro, or search the web also (not related to SAP).

    By the way, always prefer using the xlsx format, not OLE (slow, only in dialog), for instance via abap2xlsx.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 30, 2017 at 03:59 PM

    Hi Sandra ,

    As per your suggestion I altered the code and used the below code to delete the footer.

    CALL METHOD OF excel 'ExecuteExcel4Macro'
    EXPORTING #1 = 'PAGE.SETUP(,,,,,,)'. 6 commas for 3 headers / footers . But its not working.

    As I mentioned I am using excel 2010. Is it working for you ?

    Kindly help

    Regards

    Sweta

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 31, 2017 at 05:49 AM

    any clue??

    Add comment
    10|10000 characters needed characters exceeded