OLE Namensvergabe und Gültigkeit

OLE Namensvergabe und Gültigkeit

Hallo zusammen,

ich habe da gleich zwei Probleme. Ich möchte gerne per OLE aus SAP in einem Excelsheet einem Bereich von Zellen einen Namen zuweisen und dann einer Zelle nur die Einträge aus dem Bereich als Gültigkeit zulassen. Einen Namen muss ich deswegen vergeben, weil die gültigen Daten später in einem anderen sheet sein werden, dann braucht Excel einen Namen. Ich habe da mal mein coding inkl. excel-makro(als kommentar am Ende) beigefügt. Ich hoffe dass mir einer von Euch meine Erleuchtung zurück gibt.

REPORT zzexcel.
TYPE-POOLS ole2.

DATA: excel TYPE ole2_object,
mapl TYPE ole2_object, " list of workbooks
workbook TYPE ole2_object, " Workbook
map TYPE ole2_object, " Mappe
cell TYPE ole2_object.

CREATE OBJECT excel 'EXCEL.APPLICATION'.

IF sy-subrc NE 0.
WRITE : / 'Fehler CREATE OBJECT'.
ELSE.
SET PROPERTY OF excel 'Visible' = 1.
CALL METHOD OF excel 'Workbooks' = workbook.
CALL METHOD OF workbook 'Add' = map.

PERFORM fill_cell USING 1 1 'ONE'.
PERFORM fill_cell USING 2 1 'TWO'.
PERFORM fill_cell USING 3 1 'THREE'.

PERFORM format_cells USING 1 1 3 1.
* PERFORM get_validate USING 1 1 3 1 5 1.

CALL METHOD OF map 'SaveAs' EXPORTING #1 = 'Z:/test_ole2.xls'.
CALL METHOD OF workbook 'CLOSE'.
CALL METHOD OF excel 'QUIT'.

FREE OBJECT: cell,
map,
workbook,
mapl,
excel.
ENDIF.

*---------------------------------------------------------------------*
* FORM fill_cell *
*---------------------------------------------------------------------*
FORM fill_cell USING i j val.

CALL METHOD OF excel 'Cells' = cell
EXPORTING #1 = i #2 = j.
SET PROPERTY OF cell 'Value' = val .

ENDFORM.


*---------------------------------------------------------------------*
* FORM format_cells *
*---------------------------------------------------------------------*
FORM format_cells USING a
b
c
d.

DATA:
o_start TYPE ole2_object,
o_end TYPE ole2_object,
o_selection TYPE ole2_object,
o_range TYPE ole2_object.

CALL METHOD OF excel 'Cells' = cell.
CALL METHOD OF cell 'Select'.
CALL METHOD OF excel 'ActiveSheet' = map.

*** Range definieren: Start
CALL METHOD OF map 'Cells' = o_start
EXPORTING
#1 = a
#2 = b.
*** Range definieren: Ende
CALL METHOD OF map 'Cells' = o_end
EXPORTING
#1 = c
#2 = d.
*** Range aktivieren
CALL METHOD OF map 'range' = o_range
EXPORTING
#1 = o_start
#2 = o_end.
*** Range selektieren
CALL METHOD OF o_range 'Select'.

*** Aktuelle Selektion zuweisen
CALL METHOD OF o_range 'Selection' = o_selection.
SET PROPERTY OF o_selection 'Names' = 'TEST'.

FREE OBJECT: o_start,
o_end,
o_selection,
o_range.

ENDFORM. " format_cells

*&---------------------------------------------------------------------*
*& Form get_validate
*&---------------------------------------------------------------------*
* Gültigkeit hinterlegen
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
FORM get_validate USING a b c d e f.

DATA:
o_selection TYPE ole2_object.

CALL METHOD OF excel 'Cells' = cell
EXPORTING #1 = e #2 = f.

CALL METHOD OF cell 'Select' = o_selection.
CALL METHOD OF o_selection 'Validation'.

SET PROPERTY OF o_selection: 'Add Type' = '3'.
SET PROPERTY OF o_selection: 'AlertStyle' = '1'.
SET PROPERTY OF o_selection: 'Operator' = '1'.
SET PROPERTY OF o_selection: 'Formula1' = '$A$1:$A$3'.
* SET PROPERTY OF o_selection: 'Formula1' = '=TEST'.
SET PROPERTY OF o_selection: 'IgnoreBlank' = 'X'.
SET PROPERTY OF o_selection: 'InCellDropdown' = 'X'.
SET PROPERTY OF o_selection: 'ShowInput' = 'X'.
SET PROPERTY OF o_selection: 'ShowError' = 'X'.

FREE OBJECT: o_selection.

ENDFORM. " get_validate


***************************************Excel Makro
*Name zuweisen
* Range("A1:A3").Select
* ActiveWorkbook.Names.Add Name:="Numbers", RefersToR1C1:= _
* "=Tabelle1!R1C1:R3C1"

*als Gültigkeit hinterlegen
* Sheets("Tabelle2").Select
* With Selection.Validation
* .Delete
* .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
*Operator:= _
* xlBetween, Formula1:="=Numbers"
* .IgnoreBlank = True
* .InCellDropdown = True
* .InputTitle = ""
* .ErrorTitle = ""
* .InputMessage = ""
* .ErrorMessage = ""
* .ShowInput = True
* .ShowError = True