Excel Page Set up

Former Member — Thu, 31 Jul 2008 07:08:09 GMT

Hi,

We have a custom program in which we are down loading data to an excel using OLE. The requirement is when the excel is downloaded to the desktop and when we go to File-->Page Setup (in the excel) on the Page tab by default the Fit to radio button should be clicked instead of the Adjust to radio button.

This, we think can be achieved by setting the property SET PROPERTY OF PAGESETUP 'ZOOM' = FALSE in ABAP code (We have already tried this in Visual Basic, and it works)

But SAP does not allow the value FALSE for this property. Its expects a numeric value from 10 onwards. We have also tried by giving 0, 1 etc. But the code dumps.

Please help so that in in excel-->pagesetup fit to radio button is selected.

Any help would be greatly appreciated.

Thanks in advance.

Mick

Re: Excel Page Set up

Former Member — Fri, 10 Oct 2008 09:43:07 GMT

Hi ,

Try this before saving your excel..

******First step to create VBScript program in the file path as like below..

DATA : BEGIN OF IT_VBSCRIPT OCCURS 0,

COM(1000),

END OF IT_VBSCRIPT.

PARAMETERS: P_FEXL TYPE RLGRAP-FILENAME NO-DISPLAY.

CONCATENATE TEXT-E01 P_F4EXL TEXT-E01 INTO P_F4EXL.

CONCATENATE TEXT-E02 P_F4EXL INTO P_F4EXL SEPARATED BY SPACE.

FIND LD_FILENAME IN P_FEXL.

IF SY-SUBRC = 0.

REPLACE LD_FILENAME WITH 'SB.VBS' INTO P_FEXL.

ENDIF.

where TEXT-E01 = "

TEXT-E02 = excelPath =

**********Create VB script code into internal table and ***********create the file

PERFORM VBSSPT.

***********Page setup

PERFORM PAGE_SETUP.

PERFORM SAVE_EXCEL.

PERFORM OPEN_FILE.

&----

*& Form VBSSPT

&----

text

----

--> p1 text
<-- p2 text

----

FORM VBSSPT .

IT_VBSCRIPT-COM = 'Main'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'Sub Main()'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'rem Option Explicit'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'Dim objExcel'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'Dim excelPath'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'Dim worksheetCount'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'Dim counter'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'Dim currentWorkSheet'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = P_F4EXL.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'Set objExcel = CreateObject("Excel.Application")'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'objExcel.DisplayAlerts = 0'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'objExcel.Workbooks.open excelPath'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'workSheetCount = objExcel.Worksheets.Count'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'For counter = 1 to workSheetCount'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(counter)'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'currentWorkSheet.pagesetup.zoom=false'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'currentWorkSheet.pagesetup.Fittopageswide = 1'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'currentWorkSheet.pagesetup.FittopagesTAll = 13'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'Set currentWorkSheet = Nothing'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'Next'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'objExcel.Workbooks(1).Save'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'objExcel.Workbooks(1).Close'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'objExcel.Quit'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'Set currentWorkSheet = Nothing'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'REM We are done with the Excel object, release it from memory'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'Set objExcel = Nothing'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

IT_VBSCRIPT-COM = 'end sub'.

APPEND IT_VBSCRIPT.

CLEAR IT_VBSCRIPT.

CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

FILENAME = P_FEXL

FILETYPE = 'ASC'

TABLES

DATA_TAB = IT_VBSCRIPT

EXCEPTIONS

FILE_OPEN_ERROR = 1

FILE_WRITE_ERROR = 2

INVALID_FILESIZE = 3

INVALID_TYPE = 4

NO_BATCH = 5

UNKNOWN_ERROR = 6

INVALID_TABLE_WIDTH = 7

GUI_REFUSE_FILETRANSFER = 8

CUSTOMER_ERROR = 9

NO_AUTHORITY = 10

OTHERS = 11

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
write : / SY-MSGID , SY-MSGTY , SY-MSGNO ,
SY-MSGV1 , SY-MSGV2 , SY-MSGV3 , SY-MSGV4.
ELSE.
CLEAR WV_TEXT .
CONCATENATE 'File Created in the Path :' P_FEXL INTO WV_TEXT1 SEPARATED BY SPACE.
WRITE : WV_TEXT1.

ENDIF.

ENDFORM. " VBSSPT

&----

*& Form PAGE_SETUP

&----

text

----

--> p1 text
<-- p2 text

----

FORM PAGE_SETUP .

GET PROPERTY OF H_EXCEL 'ACTIVESHEET' = WORKSHEET.

PERFORM ERR_HDL.

GET PROPERTY OF WORKSHEET 'PAGESETUP' = PAGESETUP.

PERFORM ERR_HDL.

SET PROPERTY OF PAGESETUP 'ORIENTATION' = 2.

PERFORM ERR_HDL.

SET PROPERTY OF PAGESETUP 'ZOOM' = 51.

PERFORM ERR_HDL.

SET PROPERTY OF PAGESETUP 'FITTOPAGESWIDE' = 1.

PERFORM ERR_HDL.

SET PROPERTY OF PAGESETUP 'FITTOPAGESTALL' = 13.

PERFORM ERR_HDL.

SET PROPERTY OF PAGESETUP 'PRINTTITLEROWS' = '$1:$7'.

CALL METHOD OF H_EXCEL 'INCHESTOPOINTS' = MARGIN

EXPORTING

#1 = '0' .

PERFORM ERR_HDL.

SET PROPERTY OF PAGESETUP 'LEFTMARGIN' = MARGIN.

PERFORM ERR_HDL.

SET PROPERTY OF PAGESETUP 'RIGHTMARGIN' = MARGIN.

PERFORM ERR_HDL.

CALL METHOD OF H_EXCEL 'INCHESTOPOINTS' = MARGIN

EXPORTING

#1 = '0.5' .

PERFORM ERR_HDL.

SET PROPERTY OF PAGESETUP 'HEADERMARGIN' = MARGIN.

PERFORM ERR_HDL.

SET PROPERTY OF PAGESETUP 'FOOTERMARGIN' = MARGIN.

PERFORM ERR_HDL.

CALL METHOD OF H_EXCEL 'INCHESTOPOINTS' = MARGIN

EXPORTING

#1 = '0.25'.

PERFORM ERR_HDL.

SET PROPERTY OF PAGESETUP 'TOPMARGIN' = MARGIN.

PERFORM ERR_HDL.

SET PROPERTY OF PAGESETUP 'BOTTOMMARGIN' = MARGIN.

PERFORM ERR_HDL.

SET PROPERTY OF PAGESETUP 'CENTERHORIZONTALLY' = '1'.

PERFORM ERR_HDL.

ENDFORM. " PAGE_SETUP

&----

*& Form SAVE_EXCEL

&----

text

----

--> p1 text
<-- p2 text

----

FORM SAVE_EXCEL .

get property of h_excel 'ActiveSheet' = sheet.

free object sheet.

free object workbook.

GET PROPERTY OF H_EXCEL 'ActiveWorkbook' = WORKBOOK.

call method of workbook 'SAVEAS' exporting #1 = p_file #2 = 1.

call method of workbook 'CLOSE'.

call method of h_excel 'QUIT'.

free object sheet.

free object workbook.

free object h_excel.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

TEXT = 'Formatting Complete Please Open File Manually'

EXCEPTIONS

OTHERS = 1.

ENDFORM. " SAVE_EXCEL

&----

*& Form OPEN_FILE

&----

text

----

--> p1 text
<-- p2 text

----

FORM OPEN_FILE .

*CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING
FILENAME = P_FEXL
FILETYPE = 'ASC'
TABLES
DATA_TAB = IT_VBSCRIPT.

************this function module is called to set the page *************to fit to page

CALL FUNCTION 'WS_EXECUTE'

EXPORTING

cd = 'C:\'

program = P_FEXL

inform = 'X'

EXCEPTIONS

frontend_error = 1

no_batch = 2

prog_not_found = 3

illegal_option = 4

gui_refuse_execute = 5

OTHERS = 6.

wait up to 10 seconds.

call function 'WS_FILE_DELETE'

exporting

file = P_FEXL.

*wait up to 10 seconds.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

TEXT = 'Please Open The File Manually'

EXCEPTIONS

OTHERS = 1.

ENDFORM. " OPEN_FILE

All the best ..If you still need any help please write to me.

Regrads,

Uma.

Re: Excel Page Set up

Former Member — Fri, 17 Apr 2009 14:35:58 GMT

Hi,

My english is bad....

Have you a solution to resolve the problem of Mick James described below ?

I have the same problem...

Thanks a lot in advance.

Audrey

topic Re: Excel Page Set up in Application Development and Automation Discussions

Excel Page Set up

Re: Excel Page Set up

Re: Excel Page Set up

Re: Excel Page Set up