Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to eliminate empty rows in ole abap?

Former Member
0 Kudos
1,796

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

1 ACCEPTED SOLUTION

Sandra_Rossi
Active Contributor
0 Kudos
648

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.
11 REPLIES 11

Sandra_Rossi
Active Contributor
0 Kudos
648

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
0 Kudos
648

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
Active Contributor
0 Kudos
648

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
0 Kudos
648

GOOD MORNING

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

Sandra_Rossi
Active Contributor
0 Kudos
649

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.

0 Kudos
648
You know of a code converter from VBA to ABAP?

0 Kudos
648

In the case of when the IF is:

If ActiveCell.Value = "" Then

How would the conversion process of the code be?

0 Kudos
648

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

0 Kudos
648

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.


0 Kudos
648

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.

0 Kudos
648

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.