[OLE,Excel] Adding a Worksheet AFTER an existing one

[maschulze]

Hello dear experts,

currently I’m trying to create and modify an excel file with OLE. I want to add a new Worksheet AFTER an existing one. Therefore, I use the following Coding:

CALL METHOD OF _o_sheets 'Add'
EXPORTING
#2 = _o_sheet.

Even though I use the active Sheet as second parameter (after:=), the new sheet gets inserted before the first one.

I would be pleased if you can help me.
Thank you in advance and best regards

Here is the complete coding:

METHOD
initialize_excel.
*start excel
CREATE OBJECT _o_excel 'Excel.Application'.


*set excel visible
SET PROPERTY OF _o_excel 'Visible' = 1.

*set amount of sheets
SET PROPERTY OF _o_excel 'SheetsInNewWorkbook' =
im_amount_pages.

*get workbook
CALL METHOD OF _o_excel 'WorkBooks' = _o_workbook.


*create workbook with one sheet
CALL METHOD OF _o_workbook 'Add'.



GET PROPERTY OF _o_excel 'Worksheets' = _o_sheet
EXPORTING #1 = 1.

*activate Sheet
CALL METHOD OF _o_sheet 'Activate'.

*set Name
SET PROPERTY OF _o_sheet 'Name' = 'DummyTable'.

GET PROPERTY OF _o_excel 'Worksheets' = _o_sheets.


CALL METHOD OF _o_sheets 'Add'
EXPORTING
#2 = _o_sheet.



*save sheet
CALL METHOD OF _o_sheet 'SaveAs'
EXPORTING
#1 = im_path.*#2 = 1. " Fileformat

ENDMETHOD.

[former_member30]

Hi and welcome to the SAP Community!

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers (if you haven't already), as it provides tips for preparing questions that draw responses from our members. For example, you can outline what steps you took to find answers (and why they weren't helpful), share screenshots of what you've seen/done, make sure you've applied the appropriate tags, and use a more descriptive subject line.
The more details you provide, the more likely it is that members will be able to assist you. You should also make sure you're using all the appropriate tags, so the right experts can find your question.

Should you wish, you can revise your question by selecting Actions, then Edit.

Finally, if you're hoping to connect with readers, please consider adding a picture to your profile. Here's how you do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS. By personalizing your profile with a photo of you, you encourage readers to respond.

Cheers,

Julia
SAP Community Moderator

[mateuszadamus]

Hello maschulze

Not sure why it does not work.

You can try a workaround with the MOVE method: https://docs.microsoft.com/en-us/office/vba/api/excel.worksheets.move

Kind regards,
Mateusz

[maschulze]

Hey, thank you for your answer!

Best regards!
Marc-André

[Sandra_Rossi]

There have been cases in the forum where methods did not work if the first parameters were not passed. Indicating only #2 without #1 is a problem.

So "Add( After:= ...)" cannot be used with ABAP OLE, only "Add( Before:=...)" can be used.

Alternative: don't use OLE, instead use abap2xlsx.

PS: in your code, technically it should be:

GET PROPERTY OF _o_excel 'WorkBooks' = _o_workbook.

[maschulze]

Hey,

thank you for your answer and recommandation!

I guess it really could be the problem, I tried it in many different ways.
I think in future I will take a closer look to abap2xlsx.

Best regards!
Marc-André