OLE: Set PageSetup.Zoom = false

[Former Member]

Hi,

does anybody know, how i can set the Page-settings in an excel-Worksheet via an ole-object as this recorded vba-macro:

With ActiveSheet.PageSetup

.Zoom = False

.FitToPagesWide = 1

.FitToPagesTall = 5

In my abap-program I have these lines:

set property of obj_exl_page_setup 'FitToPagesWide' = 1.

set property of obj_exl_page_setup 'FitToPagesTall' = 5.

set property of obj_exl_page_setup 'Zoom' = 0.

In the Excel-document you can see, that the changes in the FitTo-attributes are set as desired but there is still the radiobutton set to Zoom.

Which value is the right one for Zoom = false.

I tried it with 0, '0', 'False', 'Falsch'.

Nothing worked.

Thanks for any hint.

Olaf

[Former Member · Accepted solution]

Hi,

Value for False is 0 in VB.

Did you try changing the order of the statements?

set property of obj_exl_page_setup 'Zoom' = 0. "OR '0'

set property of obj_exl_page_setup 'FitToPagesWide' = 1.

set property of obj_exl_page_setup 'FitToPagesTall' = 5.

Hope this helps..

Sri

[Former Member · Accepted solution]

Thanks for your quick answer,

I tried it but unfortunately the change of order didn't solve the problem ;-(

kind regards

Olaf

[Venkat_Sesha]

Hi Olaf,

Use Mustaka solution, it works very well.

CALL METHOD OF h_excel 'ExecuteExcel4Macro'

    EXPORTING #1 = 'PAGE.SETUP(,,,,,,,,,,,,{1,1})'.

What the above code does is " You are creating a Macro for Excel from SAP with the options provided"

The best way is to check. Open Excel, I am talking about MS Excel 2010

On the View Tab you will find a Macros button

Click on that button

From the Drop down you will find a option called " Record Macro" Click on it and then Record the Options on Page Layout.

Now compare the above mentioned Call method Parameters with recording. On the recording Zoom shows as FALSE dont get confuse. in the EXPORTING PARAMETER, we are passing blank to Zoom and which is mentioned below comparing to Macro Recording.

        .CenterHeader = ""

        .RightHeader = ""

        .LeftFooter = ""

        .CenterFooter = ""

        .RightFooter = ""

.PrintHeadings = False

        .PrintGridlines = False

        .CenterHorizontally = False

        .CenterVertically = False

        .Draft = False

        .BlackAndWhite = False

        .Zoom = False

        .FitToPagesWide = 1

        .FitToPagesTall = 1

Count the commas (,) from the above code, 12th one is for the Zoom. You compare it with the Macro Recording like this.

The above code should work perfectly. Just copy paste thats it. I dont have any VB belts

Hope this helps

[Former Member]

Hello,

I'm trying to implement the code in my program and not just work for me. Do I have to create the file with extension XLSX or XLSM? Does the MACRO is automatically created in excel?

Thank you. I'm going crazy with issue.

[Venkat_Sesha]

Hi Carolina,

from my comment above and as mustaka said, the CALL METHOD ExecuteExcel4Macro will do the macro creation automatically when you execute your program and part of code is executed. but this all should happen in the OLE process when the Excel is already created with some data.

Hope this is helpful.

[Former Member]

Here is the solution that I managed to solve the issue;

CALL METHOD OF h_excel 'ExecuteExcel4Macro'

    EXPORTING #1 = 'PAGE.SETUP(,,,,,,,,,,,,{1,1})'.

This makes;

zoom = 0

fittopageswide = 1

fittopagestall = 1

[Former Member]

Olaf,

Any success on this? I just encountered exactly the same problem today.

I have tried many of the options cited on this thread to no avail as well.

[Former Member]

no, unfortunately, there seems to exist no solution to this issue ;-(

but if you got it solved, I'm still interested!

kind regards

[Former Member]

Olaf,

I have implemented a "less than perfect" solution to this for my needs... but the user does get a spreadsheet with the "fit to page" set appropriately.

Would you like some sample ABAP code and the extra "piece" to make my "imperfect" solution work for you?

[Former Member]

Hi John,

that sounds pretty cool!

I think, there is nothing but an imperfect solution to this case.

I'm quite interested in Your extra piece and sample code, that would be very kind!

thanks in advance

Olaf

[Former Member]

My imperfect solution entails the "staging" of a template Excel on the app server. This "template" Excel file already has the "Fit To Page" radio button selected.

In my screen, the user can pick either Zoom and set the zoom property OR Fit to Page and then set the Width and Tall properties.

As you have seen, the Width and Tall properties are set just fine through OLE automation; however, the Fit to Page will NOT set even though the sy-subrc returns a zero.

So when the user picks Fit to Page (OPT4 in my code), I download the template then forcibly open the template. If the user picks Zoom, I simply open a new workbook.

See below - I hope this helps you - You will need to add chunks of code to make my example work for you. Please reward points accordingly.

• handles for OLE objects

DATA: hExcel TYPE OLE2_OBJECT, " Excel object

hWorkBooks TYPE OLE2_OBJECT, " list of workbooks

hWorkbook TYPE OLE2_OBJECT, " workbook

hSheet TYPE OLE2_OBJECT, " worksheet object

hRange TYPE OLE2_OBJECT, " range object

hRange2 TYPE OLE2_OBJECT, " range object

hBorders TYPE OLE2_OBJECT, " Border object

hInterior TYPE OLE2_OBJECT, " interior object - for coloring

hColumn TYPE OLE2_OBJECT, "column

hCell TYPE OLE2_OBJECT, " cell

hFont TYPE OLE2_OBJECT, " font

hSelected TYPE OLE2_OBJECT. " range object

data: UnixFile like SAPB-SAPPFAD,

ClientFile like SAPB-SAPPFAD.

start-of-selection.

• Your business logic here

If Opt4 = 'X'. "Fit to Page radio button

Perform Download_Template

endif.

Perform Start_Excel.

• Your business logic here

Form Download_Template.

• If user has set Fit to Page, download an Excel template with the

• Fit to Page option preset.

UnixFile = '/sdreports/zcoop/cmd/Fit2Page.xls'.

move 'c:\temp\Fit2Page.xls' to ClientFile.

CALL FUNCTION 'ARCHIVFILE_SERVER_TO_CLIENT'

EXPORTING

PATH = UnixFile

TARGETPATH = ClientFile

EXCEPTIONS

ERROR_FILE = 1

OTHERS = 2.

FORM Start_Excel.

CREATE OBJECT hExcel 'EXCEL.APPLICATION'.

PERFORM ERR_HDL.

• get list of workbooks, initially empty

CALL METHOD OF hExcel 'Workbooks' = hWorkbooks.

PERFORM ERR_HDL.

• If Fit to Page was selected, open the local file.

if Opt4 = 'X'. "Fit to Page

call method of hWorkbooks 'Open' = hWorkbook

exporting #1 = ClientFile. " the template !!!!

PERFORM ERR_HDL.

else.

• add a new workbook

CALL METHOD OF hWorkbooks 'Add' = hWorkbook.

PERFORM ERR_HDL.

endif.

• Get Worksheet object.

get property of hWorkbook 'ActiveSheet' = hSheet.

EndForm.

FORM ERR_HDL.

IF SY-SUBRC <> 0.

message i000(zz) with 'OLE Automation error: ' SY-SUBRC.

exit.

ENDIF.

ENDFORM. " ERR_HDL

[Former Member]

Thanks John,

if I use this variant, I have to solve the copy-sheet-problem, cause I don't know how many sheets there are to be created.

But I'm on my way and thanks for Your support!!

Bye

Olaf

[Former Member]

Not sure what "copy-sheet" means... but I did actually implement some additional logic in my solution to create multiple spreadsheets based on the number of customers that a user highlights in a table control.

I didn't imclude that logic in my example to you... was not sure of your total needs.

Would you like the multiple spreadsheet creation logic as well?

[Former Member]

if You would be so kind

the copy-sheet-problem means, that you have to copy the spreadsheet with the fittopage-attribute set to true. if you just add new spreadsheets, the default is set and nothing is gained.

thanks again!

[Former Member]

Hi gurus,

I know this thread was opened long time ago. I am facing the same problem.

Do you have any other idea?

I am treating with 13 sheets , and all of them must be "fit to page".

SET PROPERTY OF H_P 'Zoom' = 'False'.

SET PROPERTY OF H_P 'FitToPagesWide' = 1.

SET PROPERTY OF H_P 'FitToPagesTall' = 1.

I also have tried the printArea setting.

Please help me.

Thanks a lot.

[Former Member]

Hi,

With the same order, did you try 'False'?

set property of obj_exl_page_setup 'Zoom' = 'False'.

set property of obj_exl_page_setup 'FitToPagesWide' = 1.

set property of obj_exl_page_setup 'FitToPagesTall' = 5.

If this also does not work, is it possible for you to paste your code here?

Sri

[Former Member]

with

set property of obj_exl_page_setup 'Zoom' = 'False'.

I get a sy-subrc = 3!

here's an extract of my coding:

• für Excel-Übergabe

data: obj_exl_appl type ole2_object,

obj_exl_workbooks type ole2_object,

obj_exl_workbook type ole2_object,

obj_exl_worksheets type ole2_object,

obj_exl_worksheet type ole2_object,

obj_exl_cell type ole2_object,

obj_exl_font type ole2_object.

data: gv_xstart_ok type xfeld.

data: obj_exl_page_setup type ole2_object.

• Excel-Objekt starten

create object obj_exl_appl 'EXCEL.APPLICATION'.

if sy-subrc = 0.

• Container für Arbeitsmappen

call method of obj_exl_appl

'WORKBOOKS' = obj_exl_workbooks.

if sy-subrc eq 0.

set property of obj_exl_appl 'VISIBLE' = 1.

• neue Arbeitsmappe hinzufügen

call method of obj_exl_workbooks

'ADD' = obj_exl_workbook.

if sy-subrc eq 0.

• Arbeitsblätter ermitteln

call method of obj_exl_workbook

'WORKSHEETS' = obj_exl_worksheets.

gv_xstart_ok = 'X'.

endif.

endif.

endif.

• neues Blatt anlegen

call method of obj_exl_worksheets

'ADD' = obj_exl_worksheet.

• Seiteneinrichtung: Fußzeile aufnehmen und auf Seitebreite formatieren

get property of obj_exl_worksheet 'PageSetup' = obj_exl_page_setup.

set property of obj_exl_page_setup 'CenterFooter' = '&P/&N'.

set property of obj_exl_page_setup 'Zoom' = 'False'.

set property of obj_exl_page_setup 'FitToPagesWide' = 1.

set property of obj_exl_page_setup 'FitToPagesTall' = 5.

Thanks in advance

Olaf

[Former Member]

The Zoom property for the PageSetup object is a variant data type, which means it inherits its data type from the variable used to set it.

Zoom won't accept your zero because it evaluates to an integer zero, which is an unacceptable value (acceptable values are 10-400 in my excel version).

However, if you can cast the right-side value as a boolean data type, Zoom will accept "FALSE"!!!

You're not going crazy. Sorry if this doesn't help all the way.

(I've got like 12 yrs of VB under my belt)

[Former Member]

there isn't any real boolean-type in abap, as far as I know. There only exists a domain 'boolean', which is a Char1-field, allowing 'X', '-' and ' '.

And in the Office-Integration-Package 'SOI' you can find a boolean-defintion in the type-pool soi like this:

TYPES: SOI_BOOLEAN TYPE C. " 'X' - TRUE, ' ' - FALSE

I tried it with a hex-Field and with the char-boolean:

constants: lc_false type x value '00'.

or

constants: lc_false type c value ' '.

set property of obj_exl_page_setup 'Zoom' = lc_false.

Both didn't work.

any other ideas?

[Former Member]

Here is a potential kludge:

Set the Zoom property to the value of any other property that would normally evalute to a boolean. I checked the

PageSetup.BlackAndWhite property, and it defaulted to false.

i.e.

set property of obj_exl_page_setup 'Zoom' = property of obj_exl_page_setup 'BlackAndWhite'

or similar. If the compiler doesn't force the boolean property BlackAndWhite back into an integer before assigning it to Zoom, this should work.

You can also try other properties of PageSetup that default to false: CenterHorizontally, CenterVertically, Draft, PrintGridLines.

Be sure to document your code well if this works. Good luck!

[Former Member]

thanks, that was a good idea but unfortunately it doesn't work ;-(

I tried every possible data-definition:

data lv_prop_false type i,

data lv_prop_false type c,

data lv_prop_false type boolean, "which is a char

data lv_prop_false type x,

data lv_prop_false type string,

data lv_prop_false type xstring,

and then:

get property of obj_exl_page_setup 'PrintGridLines' = lv_prop_false.

set property of obj_exl_page_setup 'Zoom' = lv_prop_false.

The get-command gets always a rc of 0 and the set-command a rc of 3 (which means that the set property-command wasn't successfull).

the dynamic way of setting the value like you suggested doesn't work in abap.

kind regards

olaf

[Former Member]

Try creating another procedure (sub or function) **already embedded inside** the Excel file, which sets the property for you. Call your new proc from ABAP. Presto. No more worry about the boolean issue.

[Former Member]

interesting approach!

Do You have any idea, how it looks like to create a sub from within a sub dynamically?

I just found the RUN-Method to run a sub and maybe the MacroOptions-method allows me to create a new one:

Application.MacroOptions Macro:="TestMacro", Category:="My Custom Category"

But how can I add the relevant coding?

To call an excel-file as template including the makro will not work, because it is not guaranteed, that everybody has access to the directories.

Thanks, I think we're on a good way!!

Olaf

[Former Member]

... it would be necessary to have a method/command like AddLine, 'cause from ABAP you can only transfer strings to excel.