Skip to Content
avatar image
Former Member

Issue in setting FitToPagesWide property of excel by OLE

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 = ""

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Feb 14, 2017 at 07:11 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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
  • Feb 13, 2017 at 11:45 AM

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

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

    Add comment
    10|10000 characters needed characters exceeded

    • I have just read the SCN thread mentioning ExecuteExcel4Macro, V S BHARGAV MYLAVARAPU explained 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.
      
  • avatar image
    Former Member
    Feb 13, 2017 at 06:53 AM

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


    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 13, 2017 at 02:46 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 14, 2017 at 09:11 AM

    Hi All ,Any hint?

    Add comment
    10|10000 characters needed characters exceeded