how to set wordwrap in a fix column? (R's code incl.)
Hello everyone (and R if you're reading it)
I’m have trouble with drawing a box around some cells. I follow your code (below) but I must have set something wrong. There’s no border. How do I know what values I should assign? Could you also help me with adjusting the column using the value (instead of auto fit). I also like to know how to set the column to, say 2 cm, and word wrap. Thanks.
(By the way where do you get those sameple codes?)
=============================================
Form Set_Single_Border Using pu_sheet Type Ole2_Object
pu_range Type XlRangeAddress
pu_LineStyle Type XlLineStyle
pu_Weight Type XlLineWeight
pu_Colour Type XlLineColour
pu_border Type XlBorder.
*
Data: Range type Ole2_Object,
Borders type Ole2_Object.
*
Call Method of pu_Sheet 'Range' = Range Exporting #1 = pu_range.
Call Method of Range 'Borders' = Borders
Exporting #1 = pu_border.
Set Property of Borders: 'Linestyle' = pu_Linestyle,
'Weight' = pu_Weight,
'ColorIndex' = pu_Colour.
*
============================================
You get the codes from Excel itself.
Use the Macro recorder to record what you want to do and then translate that into ABAP.
_________________
Regards
R
Abap KC
SFMDR
Here's the VBA macro that sets word wrap on in cells:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 13/08/2004 by Rard Harper
'
Range("A1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
End Sub
And here's an include that should give you further ideas:
*----------------------------------------------------------------------*
* INCLUDE Y_EXCEL *
*----------------------------------------------------------------------*
*
* Easy definition of constants.
*
Types: XlConstant Type i,
XlCellAddress(7) Type c,
XlRangeAddress(15) Type c,
XlRow Type i,
XlColumn(2) Type c,
XlFont(20) Type c,
XlFontSize Type XlConstant,
XlFontStyle(12) Type c,
XlFormat(20) Type c,
XlFormula(50) Type c,
XlAlignment Type XlConstant,
XlLineStyle Type XlConstant,
XlLineWeight Type XlConstant,
XlLineColour Type XlConstant,
XlBorder Type XlConstant.
*
Include Ole2incl.
*
Define Create_Address.
Move &1 to &3.
Condense &3.
Concatenate &2 &3 into &3.
End-Of-Definition.
*
Define Const.
Constants: &1 type XlConstant value &2.
End-Of-Definition.
*
Const: XlDiagonalDown 5, " Excel Border Values
XlDiagonalUp 6,
XlEdgeLeft 7,
XlEdgeTop 8,
XlEdgeBottom 9,
XlEdgeRight 10,
XlInsideVertical 11,
XlInsideHorizontal 12,
*
XlContinuous 1, " Line Style,
XlNone -4142,
*
XlAutomatic -4105, " Colour Index
*
XlThin 2, " Line Weights
XlMedium -4138,
*
xlHAlignCenter -4108, " Horizontal alignment
xlHAlignDistributed -4117,
xlHAlignJustify -4130,
xlHAlignLeft -4131,
xlHAlignRight -4152,
xlHAlignCenterAcrossSelection 7,
xlHAlignFill 5,
xlHAlignGeneral 1,
*
xlVAlignBottom -4107, " Vertical Alignments
xlVAlignCenter -4108,
xlVAlignDistributed -4117,
xlVAlignJustify -4130,
xlVAlignTop -4160,
*
xlUnderLineStyleNone -4142, " Underline styles
*
XlTrue -1, " Boolean
XlFalse 0.
*
Constants: XlTextFormat type XlFormat Value '@',
XlIntegerFormat type XlFormat Value '0',
XlDecimalFormat type XlFormat Value '#,##0.00',
XlDateFormat type XlFormat Value 'dd/mm/yyyy',
XlPercentFormat type XlFormat Value '0.00%'.
*Eject
**********************************************************************
*
* Procedure: Column_Autofit_Selection
*
* Purpose: Sets spreadsheet columns to fit
* Contents
*
* Entry: Work Sheet
*
* Exit:
*
* Called By: Perform Column_AutoFit_Selection using
* w_sheet.
*
* Calls:
*
* Modification History:
*
Form Column_AutoFit_Selection using pu_sheet type Ole2_Object.
*
Data: Cells type Ole2_Object,
Selection type Ole2_Object,
Columns type Ole2_Object.
*
Call Method of pu_Sheet 'Cells' = Cells.
Call Method of Cells 'Select' = Selection.
Call Method of Selection 'Columns' = Columns.
Call Method of Columns 'Autofit'.
*
Free Object Columns.
Free Object Selection.
Free Object Cells.
*
EndForm.
*Eject
**********************************************************************
*
* Procedure: Get_Single_Border
*
* Purpose: Sets single border for a range of cells
*
* Entry: Work Sheet
* Entry: Cell Range to Get border for
* Entry: Border to Get (Excel Values)
*
* Exit: Border type (Excel Values)
*
* Called By:
* '
*
* Calls:
*
* Modification History:
*
Form Get_Single_Border Using pu_sheet Type Ole2_Object
pu_range Type XlRangeAddress
pu_border Type XlBorder
Changing pc_linestyle Type XlLineStyle.
*
Data: Range type Ole2_Object,
Borders type Ole2_Object.
*
Call Method of pu_Sheet 'Range' = Range Exporting #1 = pu_range.
Call Method of Range 'Borders' = Borders
Exporting #1 = pu_border.
Get Property of Borders: 'Linestyle' = pc_Linestyle.
*
Free object Borders.
Free object Range.
EndForm.
*Eject
**********************************************************************
*
* Procedure: Merge_Cells
*
* Purpose: Merges a range of cells
*
* Entry: Work Sheet
* Entry: Cell Range to merge
*
* Exit:
*
* Called By: Perform Merge_Cells using w_sheet
* 'A4:B7'.
*
* Calls:
*
* Modification History:
*
Form Merge_Cells using pu_sheet type Ole2_Object
pu_range type XlRangeAddress.
*
Data: Range type Ole2_Object.
*
Call Method of pu_Sheet 'Range' = Range Exporting #1 = pu_range.
Set property of Range 'Mergecells' = XlTrue.
*
Free object Range.
EndForm.
*Eject
**********************************************************************
*
* Procedure: Set_Alignment
*
* Purpose: Sets the alignment for a cell
*
* Entry: Work Sheet
* Entry: Cell Range to Set alignment for
* Entry: Alignment
*
* Exit:
*
* Called By: Perform Set_Alignment using w_sheet
* 'A4:B7'
* xlAlignRight
*
* Calls:
*
* Modification History:
*
Form Set_Alignment using pu_sheet type Ole2_Object
pu_range type XlRangeAddress
pu_Alignment type XlAlignment.
*
Data: Range type Ole2_Object.
*
Call Method of pu_Sheet 'Range' = Range Exporting #1 = pu_Range.
Set Property Of Range: 'HorizontalAlignment' = pu_Alignment,
'VerticalAlignment' = XlVAlignBottom,
'WrapText' = XlFalse,
'Orientation' = 0,
'AddIndent' = XlFalse,
'ShrinkToFit' = XlFalse.
Free Object Range.
EndForm.
*Eject
**********************************************************************
*
* Procedure: Set_Single_Border
*
* Purpose: Sets single border for a range of cells
*
* Entry: Work Sheet
* Entry: Cell Range to Set border for
* Entry: Linestyle (Excel Values)
* Entry: Line weight (Excel Values)
* Entry: Line colour (Excel Values)
* Entry: Border to set (Excel Values)
*
* Exit:
*
* Called By:
* '
*
* Calls:
*
* Modification History:
*
Form Set_Single_Border Using pu_sheet Type Ole2_Object
pu_range Type XlRangeAddress
pu_LineStyle Type XlLineStyle
pu_Weight Type XlLineWeight
pu_Colour Type XlLineColour
pu_border Type XlBorder.
*
Data: Range type Ole2_Object,
Borders type Ole2_Object.
*
Call Method of pu_Sheet 'Range' = Range Exporting #1 = pu_range.
Call Method of Range 'Borders' = Borders
Exporting #1 = pu_border.
Set Property of Borders: 'Linestyle' = pu_Linestyle,
'Weight' = pu_Weight,
'ColorIndex' = pu_Colour.
*
Free object Borders.
Free object Range.
EndForm.
*Eject
**********************************************************************
*
* Procedure: Set_Border
*
* Purpose: Sets a border for a range of cells
*
* Entry: Work Sheet
* Entry: Cell Range to Set border for
* Entry: Linestyle
* Entry: Line weight
* Entry: Line colour
*
* Exit:
*
* Called By:
*
* Calls:
*
* Modification History:
*
Form Set_Border Using pu_sheet Type Ole2_Object
pu_range Type XlRangeAddress
pu_LineStyle Type XlLineStyle
pu_Weight Type XlLineWeight
pu_Colour Type XlLineColour.
*
Data: w_Border type XlLineStyle.
*
Perform Set_Single_Border using pu_sheet
pu_range
XlNone
XlNone
XlNone
XlDiagonalDown.
Perform Set_Single_Border using pu_sheet
pu_range
XlNone
XlNone
XlNone
XlDiagonalUp.
Perform Set_Single_Border using pu_sheet
pu_range
pu_linestyle
pu_weight
pu_colour
XlInsideVertical.
Perform Set_Single_Border using pu_sheet
pu_range
pu_linestyle
pu_weight
pu_colour
XlInsideHorizontal.
Perform Set_Single_Border using pu_sheet
pu_range
pu_linestyle
pu_weight
pu_colour
XlEdgeLeft.
Perform Set_Single_Border using pu_sheet
pu_range
pu_linestyle
pu_weight
pu_colour
XlEdgeRight.
Perform Set_Single_Border using pu_sheet
pu_range
pu_linestyle
pu_weight
pu_colour
XlEdgeTop.
Perform Set_Single_Border using pu_sheet
pu_range
pu_linestyle
pu_weight
pu_colour
XlEdgeBottom.
EndForm.
*Eject
**********************************************************************
*
* Procedure: Set_Font
*
* Purpose: Sets an individual cells Font
*
* Entry: Spreadsheet Object
* Entry: Cell Address
* Entry: Font name
* Entry: Font Size
* Entry: Bold ? True/False
*
* Exit:
*
* Called By: Perform Set_font using w_sheet
* 'A7'
* 'Arial'
* 7
* 'BOLD'.
* Calls:
*
* Modification History:
*
Form Set_Font using pu_Sheet type Ole2_Object
pu_Address type xlRangeAddress
pu_font type XlFont
pu_size type XlFontSize
pu_FontStyle type XlFontStyle.
*
Data: Range type Ole2_Object,
Font type Ole2_Object.
*
Call Method of pu_Sheet 'Range' = Range exporting #1 = pu_Address.
Call Method of Range 'Font' = Font.
Set Property Of Font: 'Name' = pu_font no flush,
'FontStyle' = pu_fontstyle no flush,
'Size' = pu_size no flush,
'Strikethrough' = XlFalse no flush,
'Superscript' = XlFalse no flush,
'OutlineFont' = XlFalse no flush,
'Shadow' = XlFalse no flush,
'Underline' = xlUnderlineStyleNone.
Free Object Font.
Free Object Range.
EndForm.
*Eject
**********************************************************************
*
* Procedure: Set_Format
*
* Purpose: Sets the Format for a cell
*
* Entry: Work Sheet
* Entry: Cell Range to Set Format for
* Entry: Format String
*
* Exit:
*
* Called By: Perform Set_Format using w_sheet
* 'A4:B7'
* xlTextFormat.
*
* Calls:
*
* Modification History:
*
Form Set_Format using pu_sheet type Ole2_Object
pu_range type XlRangeAddress
pu_Format type XlFormat.
*
Data: Range type Ole2_Object.
*
Call Method of pu_Sheet 'Range' = Range Exporting #1 = pu_Range.
Set Property Of Range 'NumberFormat' = pu_Format.
Free Object Range.
EndForm.
*Eject
**********************************************************************
*
* Procedure: Set_Formula
*
* Purpose: Sets an individual cells Formula
*
* Entry: Spreadsheet Object
* Entry: Cell Address
* Entry: Formula to lace in cell
*
* Exit:
*
* Called By: Perform Set_Formula using w_sheet 'A'
* '=A1+A2+A3'.
* Calls:
*
* Modification History:
*
Form Set_Formula using pu_Sheet type Ole2_Object
pu_Address type xlRangeAddress
pu_Formula type xlFormula.
*
Data Range type Ole2_Object.
*
Call Method of pu_Sheet 'Range' = Range exporting #1 = pu_Address.
Set Property of Range 'Formula' = pu_Formula.
*
Free Object Range.
EndForm.
*Eject
**********************************************************************
*
* Procedure: Set_Value
*
* Purpose: Sets an individual cells value
*
* Entry: Spreadsheet Object
* Entry: Cell Address
* Entry: Value to place in cell
*
* Exit:
*
* Called By: Perform Set_Value using w_sheet 'A' 10.
* Calls:
*
* Modification History:
*
Form Set_Value using pu_Sheet type Ole2_Object
pu_Address type xlRangeAddress
pu_value.
*
Data: Range type Ole2_Object,
w_char(70) type c.
*
* The SAP negative numbers have the minus sign in the wrong place for
* Excel. Excel requires it at the fron where as SAP sticks it at the
* Back.
*
* If this is a number then check to see where the sign is....
*
Move pu_value to w_char.
Search w_char for '-'.
If sy-subrc = 0.
If w_char co c_numbers.
Call Function 'CLOI_PUT_SIGN_IN_FRONT'
Changing
Value = w_char
Exceptions
Others = 1.
EndIf.
EndIf.
*
Call Method of pu_Sheet 'Range' = Range exporting #1 = pu_Address.
Set Property of Range 'Value' = w_char.
*
Free Object Range.
EndForm.
*Eject
**********************************************************************
*
* Procedure: Set_Width
*
* Purpose: Sets an individual column width
*
* Entry: Spreadsheet Object
* Entry: Column to set width for
* Entry: Width in Excel ss units
*
* Exit:
*
* Called By: Perform Set_Width using w_sheet 'A' 10.
* Calls:
*
* Modification History:
*
Form Set_Width using pu_sheet type Ole2_Object
pu_column type c
pu_width type p.
*
Data: Cell Type Ole2_Object.
*
Call Method of pu_Sheet 'Cells' = Cell Exporting #1 = 1
#2 = pu_column.
Set Property of Cell 'ColumnWidth' = pu_width.
Free Object Cell.
EndForm.
_________________
Regards
R
Abap KC
SFMDR
Hi R,
Thanks for your reply. Yes, I checked the macro in excel but it doesn't show what value was assigned. For example, I record macro before doing horizontal alignment on a call but the macro code only shows xlCenter, so when I code it in ABAP, it doesn't work because the value should be 3 instead of 'Center'. Where can I find these kind of values?
set property of row 'HorizontalAlignment' = 3. "H-Ceter
And my code below doesn't work. I'm trying to put background color of cells (range from A9 to J9)
CALL METHOD OF EXCEL 'Range' = Range EXPORTING #1 = 'A9:J9'.
call method of Range 'Interior' = ObjInterior.
set property of ObjInterior 'ColorIndex' = 15. "set color
Again, where can I find that list of code for colors?
Thanks
Bring up the immediate screen in the VBA editor and then type:
? XlCenter
and hit the return key.......
As for the colours, bring up the help on the interiorcolor or whatever property.
_________________
Regards
R
Abap KC
SFMDR