Skip to Content
avatar image
Former Member

Excel file with function OLE

Dearr All,

I have two excel files.

I have to create a report which copy some data of a sheet of the first excel file in a sheet of the second excel file.

My code is:

CREATE OBJECT excel 'EXCEL.APPLICATION'.

CALL METHOD OF excel 'WORKBOOKS' = workbook.

CALL METHOD OF workbook 'Open' EXPORTING #1 = 'C:\TESTPAT.XLS'.

GET PROPERTY OF excel 'ActiveWorkbook' = workbook.

SET PROPERTY OF excel 'Visible' = 1.

COMMIT WORK AND WAIT.

CALL METHOD OF excel 'WORKBOOKS' = workbook2.

CALL METHOD OF workbook2 'Open' EXPORTING #1 = 'C:\testpat3.xls'.

GET PROPERTY OF excel 'ActiveWorkbook' = workbook2.

SET PROPERTY OF excel 'Visible' = 1.

COMMIT WORK AND WAIT.

&----


CALL METHOD OF excel 'WORKSHEETS' = sheet2 EXPORTING #1 = 1.

CALL METHOD OF sheet2 'ACTIVATE'.

CALL METHOD OF sheet2 'range' = range2 EXPORTING #1 = 'A1:A32'.

CALL METHOD OF range2 'Select'.

CALL METHOD OF range2 'Copy'.

GET PROPERTY OF excel 'ActiveWorkbook' = workbook.

CALL METHOD OF workbook 'ACTIVATE'.

CALL METHOD OF excel 'WORKSHEETS' = sheet EXPORTING #1 = 1.

CALL METHOD OF sheet 'ACTIVATE'.

CALL METHOD OF sheet 'range' = range EXPORTING #1 = 'B1:B32'.

CALL METHOD OF range 'Select'.

CALL METHOD OF sheet2 'Paste'.

My problem is that the report copy/past data in the same sheet, same excel file.

Do you know how, the treatment can select the good excel file, good sheet to paste the data?

Thanks,

Best regards,

Patrice.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Sep 22, 2010 at 09:36 AM

    Hi,

    Try the below option.

    create object excel 'Excel.Application'. 
    Set property of excel 'VISIBLE' = 1. 
    call method of excel 'WORKBOOKS' = workarea. 
    * File contains a layout (.xls) 
    CALL METHOD OF workarea 'OPEN' EXPORTING #1 = FILE. 
    get property of excel 'ACTIVEWORKBOOK' = workarea. 
    
    Call Method Of Excel 'SHEETS' = work_copy 
    Exporting #1 = 'Table3'. 
    
    Call Method Of Excel 'SHEETS' = workarea 
    Exporting #1 = 'Table1'. 
    
    Call Method Of workarea 'ACTIVATE'. 
    Call Method of workarea 'COPY' = work_copy Exporting #1 = work_copy. 
    

    Regards,

    Add comment
    10|10000 characters needed characters exceeded