Unable to delete header / footer in excel 2010

[S0021315792]

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

[Sandra_Rossi]

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.

[S0021315792]

not working by using

ExecuteExcel4Macro

[Sandra_Rossi]

Sorry. What I said here was just the opposite of what the thread said.

Anyway, I tried on Excel 2010 (no need to test with ABAP), and it doesn't work. This code works (you should let PrintCommunication = True) (source here:

http://vba.relief.jp/excel-macro-delete-headers-footers-active-sheet/😞

Sub remove_header_footer()
  With ActiveSheet.PageSetup
  .LeftHeader = ""
  .CenterHeader = ""
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = ""
  .RightFooter = ""
  End With
End Sub

[S0021315792]

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..

[Sandra_Rossi]

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

[S0021315792]

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

[S0021315792]

any clue??