Issue in setting FitToPagesWide property of excel by OLE

[S0021315792]

Hi Experts , I am struggling to set the FittoPagesWide property of excel by OLE ..I tried with all possible combinations of

set property of page 'Zoom' = '51'.* set property of page 'FitToPagesWide' = '1'.* set property of page 'FitToPagesTall' = '1' but its not working.

I searched SDN and found that by using

CALL METHOD OF excel 'ExecuteExcel4Macro'
EXPORTING #1 = 'PAGE.SETUP(,,,,,,,,,,,,{1,1})' it can be achieved.

But its also not working. Moreover page set up has got more properties as displayed in 'record macro' as shown below so its not matching with the above macro. Please help me with the code that will set the 'FitToPagesWide' property of excel by OLE.

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)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 0
.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 = ""

[Sandra_Rossi]

Could you paste the relevant ABAP code so that we can have a quick look and also quickly test, please?

[S0021315792]

GET PROPERTY OF excel 'ActiveSheet' = sheet.

get property of sheet 'PageSetup' = page.

set property of page 'Zoom' = '51'. " I have tried with 'Zoom' = 0 , abap_false

set property of page 'FitToPagesWide' = '1'.*

set property of page 'FitToPagesTall' = '1'.

Also I tried.

CALL METHOD OF excel 'ExecuteExcel4Macro'
EXPORTING #1 = 'PageSetup(,,,,,,,,,,,,,,,,,,,,,,,,,{1,1})'.

Above is the code that I tried ...None of them works...I think ExecuteExcel4Macro need to form the string in a different way...

[Sandra_Rossi]

In VBA, it works well with (ref: https://www.mrexcel.com/forum/excel-questions/625144-visual-basic-applications-fit-1-page-width-only...😞

.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False

In ABAP, I can't make it work either. I think the issue is about the booleans. Usually the Excel "false" corresponds to ABAP 0 (zero). But for these 2 properties, 0 doesn't work.

So I think that the only possibility is to change the technology. If you know, do it via a DOI script (VBS language), or maybe with abap2xlsx.

[Sandra_Rossi]

I have just read the SCN thread mentioning ExecuteExcel4Macro, venkat.seshaexplained the issue and the solution very well.

But this solution works only with Excel 2007. It does not work with Excel 2010 and +.

So, if you have Excel 2007, it works well with this ABAP code:

PARAMETERS p_file TYPE string LOWER CASE DEFAULT 'C:\Users\SR0D12FN\Desktop\test.xlsx'.

DATA: excel     TYPE ole2_object,
      workbooks TYPE ole2_object,
      workbook  TYPE ole2_object,
      sheet     TYPE ole2_object,
      page      TYPE ole2_object.

CREATE OBJECT excel 'excel.application'.

SET PROPERTY OF excel 'visible' = 1.

CALL METHOD OF excel 'workbooks' = workbooks.

CALL METHOD OF workbooks 'Open' = workbook EXPORTING #1 = p_file.

IF sy-subrc = 0.
  GET PROPERTY OF excel 'ActiveSheet' = sheet.

  CALL METHOD OF excel 'ExecuteExcel4Macro' EXPORTING #1 = 'PAGE.SETUP(,,,,,,,,,,,,{1,0})'.

*Sub ExportAsFixedFormat(Type As XlFixedFormatType, [Filename], [Quality], [IncludeDocProperties],
* [IgnorePrintAreas], [From], [To], [OpenAfterPublish], [FixedFormatExtClassPtr])
*Const xlTypePDF = 0

  CALL METHOD OF sheet 'ExportAsFixedFormat' EXPORTING #1 = 0 #2 = 'C:\Users\SR0D12FN\Desktop\test.pdf' #8 = 0.

*Sub Close( [SaveChanges]...)

  CALL METHOD OF workbook 'close' EXPORTING #1 = 0.

ENDIF.

CALL METHOD OF excel 'quit'.

FREE OBJECT excel.

[S0021315792]

Hi Sandra ,

I copied the code mentioned above in a program but its not working..I have already mentioned the reason in my query..The macro that is being recorded for setting the fittopageswide property has 26th property as 'Fittopageswide' So I passed 25 commas in the string which is getting passed to page.setup but its not wokring.

Can you help what should I pass in that string for my case( I have already pasted the macro in my initial query).

Regards
Sweta

,

Hi Sandra, I have already mentioned in my query that the above code is not working as the macro that is recorded , in it zoom is the 25th property and not the 12th . I have pasted the macro in my query so the string Page.setup has to be in accordance with that .... Even I pasted the above code mentioned by you in a separate program but its not working...Please help..

Regards

Sweta

[S0021315792]

Hi All ,Any hint?

[S0021315792]

Hi All ,

I found the solution which is working.Providing the answer below for everyone's reference.
GET PROPERTY OF excel 'ActiveSheet' = sheet.

set property of excel 'PrintCommunication' = abap_false.
get property of sheet 'PageSetup' = page.
set property of page 'Zoom' = 0.

set property of page 'FitToPagesTall' = 0.

set property of page 'FitToPagesWide' = 1 .

set property of excel 'PrintCommunication' = abap_TRUE.

**Dont put 0 or 1 within quotes ie '0' otherwise it will not work and also print communication should be false at the beginning and true @ end.

Regards
Sweta

[Sandra_Rossi]

Well done! It must be a question of Excel version too, as the example I have provided works with SAP GUI 740 / Excel 2007 SPS 3.

Your solution works from Excel 2010. PrintCommunication did not exist in Excel 2007.

Note: you should not use abap_false and abap_true for setting true/false OLE properties, i.e. :

set property of excel 'PrintCommunication' = 0. " don't use abap_false
set property of excel 'PrintCommunication' = 1. " don't use abap_true