Skip to Content

ABAP OLE - How to copy format from one line to given range.

HI ,

I am doing project, which involve populating data in Excel file.

For the same, I am using SAP OLE .

Now, I struck when I am trying to copy format of a row to the given range.

Details of problem is as follows :-

  1. I have a row no. say 15, which contains the required format I am looking in other lines. So I copy the line.
  2. I have selected range as rows 13 and 14 , where I need to paste format. I use "paste special " feature to paste the format in row 13 and 14.

In macro , the VBScript for the above operation is :-

Rows("15:15").Select

Selection.Copy

Rows("13:14").Select

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

So I tried to translate the same in ABAP as below :-

*Rows("15:15").Select

CALL METHOD OF

g_active_sheet

'ROWS' = v_source_obj

EXPORTING

#1 = p_source

#2 = p_source.

CALL METHOD OF

v_source_obj

'SELECT'.

* selection.copy

CALL METHOD OF

g_excel_application

'SELECTION' = v_selection_obj.

CALL METHOD OF

v_selection_obj

'COPY'.

* rows("13:14").Select

CALL METHOD OF

g_active_sheet

'ROWS' = v_range_obj

EXPORTING

#1 = p_from

#2 = p_to.

CALL METHOD OF

v_range_obj

'SELECT'.

CALL METHOD OF

v_selection_obj

'PASTESPECIAL'

EXPORTING

#1 = 'paste:=xlpasteformats'

#2 = -4142

#3 = 0

#4 = 0.


Please suggest what wrong I am doing here.

Regards,

Ekam


Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Dec 22, 2015 at 08:44 AM

    Hi.

    Try to use "Insert" instead of "PasteSpecial".

    My suggest is to create a macro and call it from ABAP. Macro should be as below:

    Sub COPY_ROW(FnumRows As Long)

    '

    ' COPY_ROW Macro

    '

    Dim Ftimes As Integer

    Dim Fws As Worksheet

    Dim Frng As Range

    '

    '~~> Set this to the relevant worksheet

    Set Fws = ThisWorkbook.Sheets("SheetName")

    With Fws

    '~~> Set your range

    For Ftimes = 1 To FnumRows

    Set Frng = Range("template_row") '<=!! This is the name you give in excel to your row 'template. In your case give a name to your 15 row which 'contains the format you need

    '~~> Copy the range

    Frng.Copy

    'Here you should select the point where you want to insert your row

    'if you don't define anything, excel will just put your new row exactly under "template_row"

    Frng.Offset(Ftimes).Insert Shift:=x1Down

    '~~> Clear the clipboard.

    Application.CutCopyMode = False '<= This clear the clipboard

    Next Ftimes 'Next row to be copied

    End With

    End Sub

    Hope to help

    Bye

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 23, 2016 at 03:30 PM

    Hi Ekam,

    Did you manage to find any solution for this, I do have the same requirement. The selection copy is able to copy the selection. But the paste selection operation is not getting executed.

    If you had managed to find a way to achieve this please help to share your thoughts on this.

    Thanks in advance...

    Regards

    Ravi

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.