New worksheet for existing excel

[Former Member]

Hi Gurus

Excel sheet will be there with 3 or more worksheets.

We need to insert one more worksheet at the beginning and populate the data.

But the rest of the worksheets should not be altered ie they should be the same.

I have seen two links which shows how to upload the data from multiple worksheets

and how to download the data into excel with more worksheets.

My requirement is to add a new worksheet to the existing excel.

Please guide how to do that.

Can i ask user to load the excel into the program and in the program i add new worksheet and

and promt him the save as option? here the problem is we dont know how the columns in the

different worksheets so we cant predict it and read it.

So anyone suggest me what need to be done for this.

[Former Member]

I think below link will help you out.

http://wiki.sdn.sap.com/wiki/display/Snippets/OpenExcelusingOLEAutomation

[Former Member]

Thanks for the reply , but it wont help.

Friends could any thorugh some light on this.

[guillaume-hrc]

Hi,

I can't see how it is not helpful, isn't what you wanted to do : open Excel and add an new sheet?

Check this for opening exisitng Excel file :

DATA gs_excel TYPE ole2_object .
DATA gs_wbooks TYPE ole2_object .
DATA gs_wbook TYPE ole2_object .
DATA gs_application TYPE ole2_object .
 
CREATE OBJECT gs_excel 'EXCEL.APPLICATION' .
SET PROPERTY OF gs_excel 'Visible' = 1 .
GET PROPERTY OF gs_excel 'Workbooks' = gs_wbooks .
GET PROPERTY OF gs_wbooks 'Application' = gs_application .
 
*--Opening the existing document
CALL METHOD OF gs_wbooks 'Open' = gs_wbook
     EXPORTING #1 = <file_path_var> .

As I see it, lt the user fill the path to the existing Excel file and the snippet will open it and fill data in a new sheet.

What about the columns you are taking about?

Best regards,

Guillaume

Edited by: Guillaume Garcia on Sep 24, 2010 12:08 PM

[Former Member]

Hi Guillaume Garcia ,

Thanks for ur reply.

User will be having an excel sheet and it may contain any number of worksheets in that with data.

And the user wants one new worksheet to be added to already existing excel which is in desktop.

Now pls tell suppose if user has an excel with 5 worksheets with differnet types of data ie maybe pie diagrams in one worksheet, tables in another worksheet etc. Now how can i tell to SAP program that we should not disturb other data and simply add the new worksheet.

To add the worksheet we need to first take the excel into our program ie upload it , right? and need to read all the data in all the 5 worksheets and at the end download all the read data along with the new worksheet.

This is what I understood from the blogs.

Now tell me is there any other way that we simply mention the path of exiting excel and the system will add another worksheet with disturbing the data in other worksheets.

Thank you for all the replies

[guillaume-hrc]

Hi,

What you describe means uploading the Excel file to the server, let the server enrich it with new data and download the new file on the frontend.

This is not the way OLE work. OLE is about sending command to an application (Microsoft Excel here) so that it executes actions the same way a user would. The Excel is not send to the SAP server, it stays where it is.

If done correctly, there is no risk of tamperring with the other worksheets of the workbook.

Best regards,

Guillaume

[Former Member]

Hi Guillaume Garcia ,

That is what I am exactly looking for.

So liittle bit confusion here,..

do i need to upload the excel file first ie in selection screen? if we upload then there is no need for us to define the structure to read the data ie gui_upload.

or at the time of saving the excel from program we need to give the same name as the existing excel and overwrite it?

What is the way I need to follow , pls guide.

[guillaume-hrc]

Hi,

No, you don't need to "upload" the file in the selection-screen, but the filename must be defined on the selection-screen so that OLE knows which file to open.

Hence, you do not call 'GUI_UPLOAD' on the file, just OLE commands.

Simply perform a mere Save to keep the name of the file as before.

Best regards,

Guillaume

[Former Member]

Hi Guillaume Garcia ,

I have written the code like this.

When i run the report , the existing excel is opening and when i close it another new excel is opening and the data is added to it.

But i want the data to be added to the existing excel automatically by the program.

Pls suggest me how to add the data to the open excel..

REPORT  ZEXCELWORKSHEET3                        .

INCLUDE ole2incl.
DATA: application TYPE ole2_object,
       workbook TYPE ole2_object,
       sheet TYPE ole2_object,
       cells TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.

DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.
DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.
  DATA gs_wbook TYPE ole2_object .

* File Selection
SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_file   LIKE rlgrap-filename.
SELECTION-SCREEN END OF BLOCK block1.

* F4 Help for File name
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      program_name = sy-repid
      field_name   = 'P_FILE'
    IMPORTING
      file_name    = p_file.

START-OF-SELECTION.

  APPEND: 'Peter' TO itab1, 'Ivanov' TO itab2,
                              '=Sheet1!A1 & " " & Sheet2!A1' TO itab3,
            'John' TO itab1, 'Smith' TO itab2,
                              '=Sheet1!A2 & " " & Sheet2!A2' TO itab3.

  CREATE OBJECT application 'excel.application'.
  SET PROPERTY OF application 'visible' = 1.
  CALL METHOD OF application 'Workbooks' = workbook.

*--Opening the existing document
  CALL METHOD OF workbook 'Open' = gs_wbook
    EXPORTING #1 = p_file .
    CALL METHOD OF workbook 'Add'.
* Create first Excel Sheet
  CALL METHOD OF application 'Worksheets' = sheet
                               EXPORTING #1 = p_file."1.
  CALL METHOD OF sheet 'Activate'.
  SET PROPERTY OF sheet 'Name' = 'Sheet1'.

  LOOP AT itab1.
    index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name
    CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
    SET PROPERTY OF cells 'Value' = itab1-first_name..
  ENDLOOP.
* Save excel speadsheet to particular filename
  CALL METHOD OF sheet 'SaveAs'
                  EXPORTING #1 = 'c:\temp\exceldoc1.xls'     "filename
                            #2 = 1.                          "fileFormat

[guillaume-hrc]

Hi,

Remove the :

CALL METHOD OF workbook 'Add'.

Best regars,

Guillaume

[Former Member]

Hi Guillaume Garcia ,

I have changed the program now its working, now only issue is that I need to maually need to leave the first sheet blank then only the program insert it in first palce. My requirement is that system should itself create first worksheet even though in my excel first sheet is there. Program should move the already existing first sheet in my excel and insert new one .

REPORT ZEXCELWORKSHEET3 .

INCLUDE ole2incl.

DATA: application TYPE ole2_object,

workbook TYPE ole2_object,

sheet TYPE ole2_object,

cells TYPE ole2_object.

CONSTANTS: row_max TYPE i VALUE 256.

DATA index TYPE i.

DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.

DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.

DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.

DATA gs_wbook TYPE ole2_object .

• File Selection

SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.

PARAMETERS: p_file LIKE rlgrap-filename.

SELECTION-SCREEN END OF BLOCK block1.

• F4 Help for File name

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file..

CALL FUNCTION 'F4_FILENAME'

EXPORTING

program_name = sy-repid

field_name = 'P_FILE'

IMPORTING

file_name = p_file.

START-OF-SELECTION.

APPEND: 'Peter' TO itab1, 'Ivanov' TO itab2,

'=Sheet1!A1 & " " & Sheet2!A1' TO itab3,

'John' TO itab1, 'Smith' TO itab2,

'=Sheet1!A2 & " " & Sheet2!A2' TO itab3.

CREATE OBJECT application 'excel.application'.

SET PROPERTY OF application 'visible' = 1.

CALL METHOD OF application 'Workbooks' = workbook.

*--Opening the existing document

CALL METHOD OF workbook 'Add' = gs_wbook

EXPORTING #1 = p_file .

• Create first Excel Sheet

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING #1 = 1.

CALL METHOD OF sheet 'Activate'.

SET PROPERTY OF sheet 'Name' = 'Whirlpool'.

LOOP AT itab1.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'Value' = itab1-first_name.

ENDLOOP.

• Save excel speadsheet to particular filename

CALL METHOD OF sheet 'SaveAs'

EXPORTING #1 = 'c:\temp\exceldoc1.xls' "filename

#2 = 1.

[guillaume-hrc]

Hi,

Try this, this should completely fit your needs:

REPORT  zggar_test_ole.

INCLUDE ole2incl.

DATA: application TYPE ole2_object,
       workbook TYPE ole2_object,
       sheets TYPE ole2_object,
       sheet  TYPE ole2_object,
       first_sheet  TYPE ole2_object,
       cells TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.

DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.
DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.
DATA gs_wbook TYPE ole2_object .

* File Selection
SELECTION-SCREEN BEGIN OF BLOCK block1 WITH FRAME TITLE text-001.
PARAMETERS: p_file   LIKE rlgrap-filename  DEFAULT 'C:\Documents and Settings\my_Excel.xls'.
SELECTION-SCREEN END OF BLOCK block1.

* F4 Help for File name
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      program_name = sy-repid
      field_name   = 'P_FILE'
    IMPORTING
      file_name    = p_file.

START-OF-SELECTION.

  APPEND: 'Peter' TO itab1, 'Ivanov' TO itab2,
                              '=Sheet1!A1 & " " & Sheet2!A1' TO itab3,
          'John'  TO itab1, 'Smith' TO itab2,
                              '=Sheet1!A2 & " " & Sheet2!A2' TO itab3.

  CREATE OBJECT application 'Excel.Application'.
  SET PROPERTY OF application 'Visible' = 1.
  CALL METHOD OF application 'Workbooks' = workbook.

*--Opening the existing document
  CALL METHOD OF workbook 'Open' = gs_wbook
    EXPORTING #1 = p_file .

*    CALL METHOD OF workbook 'Add'.

* Create first Excel Sheet
  CALL METHOD OF application 'Worksheets' = first_sheet
    EXPORTING #1 = 1.

  CALL METHOD OF application 'Worksheets' = sheets.
  CALL METHOD OF sheets 'Add'
    EXPORTING #1 = first_sheet.

  GET PROPERTY OF application 'ActiveSheet' = sheet.
  SET PROPERTY OF sheet 'Name' = 'Guillaume'.

  LOOP AT itab1.
    index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name
    CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.
    SET PROPERTY OF cells 'Value' = itab1-first_name..
  ENDLOOP.
* Save excel speadsheet to particular filename
  CALL METHOD OF sheets 'SaveAs'
                  EXPORTING #1 = 'c:\temp\exceldoc1.xls'     "filename
                            #2 = 1.                          "fileFormat

Best regards,

Guillaume

[Former Member]

hI Guillaume Garcia ,

Thank you so much for the help.

[_IvanFemia_]

Hi,

take a look to this blog

[xlsx2abap - Read and edit your Excel files from ABAP|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/20791] [original link is broken] [original link is broken] [original link is broken];

Regards,

Ivan