Making a cell as a dropdown list in excel working with OLE

[Former Member]

hi,

Can anybody tell me how to make a cell in MS excel as a Drop down list while working with OLE??

also i would like to know where exactly we can get all the parameters and methods for OLE.

[Sandra_Rossi]

A quick way to find the method you need is to use the excel macro recorder, and then start visual basic editor (ALT-F11). In your case, you'll see something like that (VBA code):

activesheet.Range("e6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=$G$5:$G$7"

You must determine the value of xlValidateList by using the Object Browser in the visual basic editor, you'll see that it is 3.

You also need to know the position of each method parameter, in the same Object Browser . For example, Type parameter is #1, Formula1 is #4 (not #3 as you may suppose)

Then just convert the VBA code above into abap OLE:

...
GET PROPERTY OF l_activesheet 'range' = l_range EXPORTING #1 = 'E6'.
CALL METHOD OF l_range 'Add' EXPORTING #1 = 3   #2 = 1   #4 = '=$G$5:$G$7'.

List of methods and properties are (for instance) in the microsoft excel visual basic reference guide

[Former Member]

Hi,

Please confirm if this is working. Because, i tried with code given but i am unable to get drop down list.

Thanks,

Vishnu

[KamalpreetSingh]

Hi 

Use below set of my code .

CALL METHOD OF workbook 'Cells' = lo_cellstart
EXPORTING
#1 = 2
#2 = 1.
CALL METHOD OF workbook 'Cells' = lo_cellend
EXPORTING
#1 = 10 "LV_CONT
#2 = 1.

CALL METHOD OF workbook 'Range' = lo_range
EXPORTING
#1 = lo_cellstart "LV_CONT
#2 = lo_cellend.

CALL METHOD OF lo_range 'select'.

CALL METHOD OF h_excel 'selection' = lo_selection.

CALL METHOD OF lo_selection 'Validation' = lo_validation.
CONCATENATE '=' lv_range_name INTO lv_range_name.
CALL METHOD OF lo_validation 'Add'
EXPORTING
#1 = 3
#2 = 1
#3 = 1
#4 = lv_range_name.

Make sure that you have two worksheet. In one worksheet you should have all that name which need to be show as dropdown. In other one when u click on cell you will see drop down.