How to eliminate empty rows in ole abap?

[Former Member]

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 !!

[Sandra_Rossi]

There are lots of threads about converting any VBA to ABAP (like this one: https://archive.sap.com/discussions/message/6982828#message-6982828).

What did you try up to now? What were your difficulties?

[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.

[Sandra_Rossi]

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!!!

[Sandra_Rossi]

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.

[Former Member]

You know of a code converter from VBA to ABAP?

[Former Member]

In the case of when the IF is:

If ActiveCell.Value = "" Then

How would the conversion process of the code be?

[Sandra_Rossi]

No sorry: I don't know any blog post or training document how to convert VBA code to ABAP code.

[Sandra_Rossi]

If you use the "Object Explorer" tool (F2) in the VBA editor (ALT+F11), you'll see that ActiveCell of the Excel application is a property of type range:

So, you use:

GET PROPERTY OF excel 'ActiveCell' = range3.

For Value of a Range, you see it's a property, and it's obvious it's of type character:

So, you do:

DATA value(20) TYPE c.
GET PROPERTY OF range3 'Value' = value.
IF value = ''.

Hope now you got how to do it for any VBA code.

[Former Member]

Good Morning, Sandra

If I have identified the properties and methods, but what confuses me is the use of WHILE and IF with methods and properties.

I appreciate your answers, it has helped me a lot.

[Sandra_Rossi]

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.