topic Re: Format column/cell date type OLE2_OBJECT in Application Development and Automation Discussions

Format column/cell date type OLE2_OBJECT

prince_isaac — Thu, 22 Mar 2012 15:09:33 GMT

Hi gurus

I am downloading a file to excel using OLE2_OBJECT and i am trying to format a column in the excel sheet such that user can only enter data in a certain format i have the below code and when no dates are maintained in SAP before download the field seems to allow any format date to be entered, how can i restrict the format and disallow all other formats from being entered.

{code}

set property of lw_columns 'Locked' = 0.
set property of lw_columns 'NumberFormat' = '[$-14409]dd.mm.yyyy;@'. "01.01.2012

{code}

regards

Prince Isaac

Re: Format column/cell date type OLE2_OBJECT

RicardoRomero_1 — Thu, 22 Mar 2012 16:23:46 GMT

Hi,

As advice, I recommend to you to record a macro in excel, and then see the VB code in order to "translate" it to Abap. You can debug it for see the values of the constants within the VB code.

Try with the following code. With this you can add a validation in the cell 1A for enter a date between 1.1.200 and 1.1.2013. And with a specific format. My excel is in spanish language maybe your date format is different. For me it-s working fine.

   REPORT zricardo_excel.

TYPE-POOLS: soi,ole2.

DATA: lo_application   TYPE ole2_object,
       lo_workbook      TYPE ole2_object,
       lo_workbooks     TYPE ole2_object,
       lo_range         TYPE ole2_object,
       lo_worksheet     TYPE ole2_object,
       lo_worksheets    TYPE ole2_object,
       lo_columns       TYPE ole2_object,
       lo_cells         TYPE ole2_object,
       lo_active_cell   TYPE ole2_object.

DATA o_cellstart      TYPE ole2_object.
DATA o_cellend        TYPE ole2_object.
DATA o_selection      TYPE ole2_object.
DATA o_validation     TYPE ole2_object.

DATA: lv_selected_folder TYPE string,
        lv_complete_path   TYPE char256,
        lv_titulo          TYPE string.

    CALL METHOD cl_gui_frontend_services=>directory_browse
      EXPORTING
        window_title    = lv_titulo
        initial_folder = 'C:\'
      CHANGING
        selected_folder = lv_selected_folder
      EXCEPTIONS
        cntl_error      = 1
        error_no_gui    = 2
        OTHERS          = 3.
CHECK NOT lv_selected_folder IS INITIAL.

CREATE OBJECT lo_application 'Excel.Application'.
CALL METHOD OF lo_application 'Workbooks' = lo_workbooks.
CALL METHOD OF lo_workbooks 'Add' = lo_workbook.
SET PROPERTY OF lo_application 'Visible' = 0.
GET PROPERTY OF lo_application 'ACTIVESHEET' = lo_worksheet.

CALL METHOD OF lo_worksheet 'Cells' = o_cellstart
    EXPORTING
    #1 = 1
    #2 = 1.
CALL METHOD OF lo_worksheet 'Cells' = o_cellend
    EXPORTING
    #1 = 1
    #2 = 1.
CALL METHOD OF lo_worksheet 'range' = lo_range
    EXPORTING
    #1 = o_cellstart
    #2 = o_cellend.

* Format of the cell
SET PROPERTY OF lo_range 'NumberFormat' = '[$-C0A]d-mmm-yy;@'.

CALL METHOD OF lo_range 'select'.
CALL METHOD OF lo_application 'selection' = o_selection.
CALL METHOD OF o_selection 'Validation' = o_validation.

CALL METHOD OF o_validation 'Add'
    EXPORTING
    #1 = 4 "xlValidateDate
    #2 = 1 "xlValidAlertStop
    #3 = 1 "xlBetween
    #4 = '1/1/2000' "Formula1
    #5 = '1/1/2013'. "Formula2

SET PROPERTY OF o_validation 'ErrorMessage' = 'Enter a valid date'.

CONCATENATE lv_selected_folder '\Test' INTO lv_complete_path.

CALL METHOD OF lo_workbook 'SaveAs'
    EXPORTING
    #1 = lv_complete_path.
IF sy-subrc EQ 0.
     MESSAGE 'File downloaded successfully' TYPE 'S'.
ELSE.
     MESSAGE 'Error downloading the file' TYPE 'E'.
ENDIF.

CALL METHOD OF lo_application 'QUIT'.
FREE OBJECT lo_active_cell.
FREE OBJECT lo_range.
FREE OBJECT lo_worksheet.
FREE OBJECT lo_workbook.

FREE OBJECT lo_application.

Re: Format column/cell date type OLE2_OBJECT

prince_isaac — Thu, 22 Mar 2012 17:15:11 GMT

Hi Ricardo

I tried your tip but i got lost smehow and now my excel sheet does not have data anymore and the column which i want to validate is highlighted. Here is how i went about it

      call method of lw_excel 'Columns' = lw_columns
      exporting
        #1 = 19.

    set property of lw_columns 'Locked' = 0.
    set property of lw_columns 'NumberFormat' = '[$-14409]dd.mm.yyyy;@'. "01.01.2012

    call method of lw_columns 'select'.
    call method of lw_excel 'selection' = lw_selection.
    call method of lw_selection 'Validation' = lw_validation.

    call method of lw_validation 'Add'
      exporting
        #1 = 4 "xlValidateDate
        #2 = 1 "xlValidAlertStop
        #3 = 1 "xlBetween
        #4 = '01.01.2005'                                   "Formula1
        #5 = '01.01.2099'.                                  "Formula2

    set property of lw_validation 'ErrorMessage' = 'Enter a valid date'.

regards

Prince Isaac

Re: Format column/cell date type OLE2_OBJECT

RicardoRomero_1 — Thu, 22 Mar 2012 17:36:55 GMT

Hi,

Try with the following code:

GET PROPERTY OF lo_application 'ACTIVESHEET' = lo_worksheet.

    CALL METHOD OF lo_worksheet 'Columns' = lo_column
    EXPORTING
    #1 = 1.

* Format of the column
CALL METHOD OF lo_column 'Select'.
CALL METHOD OF lo_application 'selection' = o_selection.
SET PROPERTY OF o_selection 'NumberFormat' = '[$-14409]dd.mm.yyyy;@'.