Skip to Content
avatar image
-1
Former Member

How to eliminate empty rows in ole abap?

Hello, I would like to know how to delete empty rows from an excel document, from OLE ABAP but without MACROS from VBA.

I already did it with an Excel MACRO and it works but I want to do it with ABAP code, this is the code I use:

finrgo = Range("B65536").End(xlUp).Row

Range("B9").Select

While ActiveCell.Row <= finrgo

If ActiveCell.Value = "" Then

ActiveCell.EntireRow.Delete

finrgo = finrgo - 1

Else

ActiveCell.Offset(1, 0).Select

End If

Wend

I would appreciate your help !!

Add comment
10|10000 characters needed characters exceeded

  • Former Member

    Hello Sandra.

    Check the URL that you passed me and if it's okay to do that, but the function I want to do is a bit complicated.

    I would like to know if you happen to have more information than you can give me.

    Thank you very much.

  • No sorry: I don't know any blog post or training document how to convert VBA code to ABAP code. Isn't it more easy to provide the ABAP code that you have tried, so that people can just tell you where the wrong part is?

  • Former Member

    GOOD MORNING

    By chance know of a platform that helps me to pass the VBA code to ABAP code. THANK YOU!!!
  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 24, 2017 at 07:13 AM

    What will help you a lot is to use the "Object Explorer" tool (F2) in the VBA editor (ALT+F11). It provides everything you need. For example, how to know the actual values of the constants (xlUp), how to know whether names are methods or properties, positions of formal parameters of methods, etc.

    To help you starting with something, here are the first lines of your program (prerequisite is to create an Excel file for the test). I have omitted the exception hanling for sake of simplicity:

    REPORT.
    TYPE-POOLS ole2.
    DATA excel TYPE ole2_object.
    DATA workbooks TYPE ole2_object.
    DATA workbook TYPE ole2_object.
    DATA worksheet TYPE ole2_object.
    DATA range1 TYPE ole2_object.
    DATA range2 TYPE ole2_object.
    DATA finrgo TYPE i.
    CREATE OBJECT excel 'excel.application'.
    " set workbooks = excel.workbooks
    CALL METHOD OF excel 'Workbooks' = Workbooks.
    " set workbook = workbooks.open("C:\Users\sandra\Documents\Classeur1.xlsm")
    CALL METHOD OF Workbooks 'Open' = Workbook EXPORTING #1 = 'C:\Users\sandra\Documents\Classeur1.xlsm'.
    " set worksheet = workbook.worksheets(1)
    GET PROPERTY OF Workbook 'worksheets' = worksheet EXPORTING #1 = 1.
    " set range1 = worksheet.range("B65536")
    GET PROPERTY OF worksheet 'range' = range1 EXPORTING #1 = 'B65536'.
    " set range2 = range1.end(xlUp)
    GET PROPERTY OF range1 'end' = range2 EXPORTING #1 = -4162.
    " finrgo = range2.row
    GET PROPERTY OF range2 'row' = finrgo.
    CALL METHOD OF workbook 'Close' EXPORTING #1 = 0. " SaveChanges = false
    CALL METHOD OF excel 'Quit'.
    WRITE / finrgo.
    
    Add comment
    10|10000 characters needed characters exceeded

    • I don't understand your problem. WHILE and IF in ABAP are very straight forward:

      GET PROPERTY OF excel 'ActiveCell' = ActiveCell.
      GET PROPERTY OF ActiveCell 'Row' = ActiveCell_Row.
      WHILE ActiveCell_Row <= finrgo.
      	GET PROPERTY OF ActiveCell 'Value' = ActiveCell_Value.
      	If ActiveCell_Value = ''.
      		GET PROPERTY OF ActiveCell 'EntireRow' = ActiveCell_EntireRow.
      		CALL METHOD OF ActiveCell_EntireRow 'Delete'.
      		finrgo = finrgo - 1.
      	Else.
      		GET PROPERTY OF ActiveCell 'Offset' = ActiveCell_Offset_1_0 EXPORTING #1 = 1 #2 = 0.
      		CALL METHOD OF ActiveCell_Offset_1_0 'Select'.
      	EndIf.
      	GET PROPERTY OF ActiveCell 'Row' = ActiveCell_Row.
      ENDWHILE.
      

      Next time, please paste your ABAP code, and ask precise questions. The forum is answered by volunteers, it's not their job of doing the job of other people.