Excel multiple sheets read

Former Member — Thu, 06 Nov 2008 10:25:14 GMT

Hi,

I have an Excel file with multiple sheets. My code is:


  create object e_appl 'EXCEL.APPLICATION'.
  set property of e_appl 'VISIBLE' = 1.
 
  call method of e_appl 'WORKBOOKS' = e_work.
 
 
  call method of e_work 'OPEN'
          exporting
               #1 = p_file.
 
 
 get property of e_appl 'ACTIVEWORKBOOK' = e_work.

1. I can only read active sheet. How can i change active sheet ?

2. How can I quit Excel without saving and without popup "Do u want to save changes" ?

Re: Excel multiple sheets read

Former Member — Thu, 06 Nov 2008 10:34:02 GMT

Uploading multiple multitab Excel sheets or Ranges from Front end to SAP ...............

This could be a neat way to upload XLS the OO way!

The function Module zjnc_get_range reads 1 range into any Internal

table.

DATA: BEGIN OF it_test OCCURS 0,

vpd LIKE mseg-menge,

vas LIKE mkpf-budat,

vkm LIKE mseg-matnr,

END OF it_test.

CALL FUNCTION 'ZJNC_GET_RANGE'

EXPORTING

rangename = 'test'

itabname = 'IT_TEST[]'

irecname = 'it_test'

spreadsheetintf = spreadsheetintf.

=Work!$A$14:$C$16 is range "test"

Numbers & Character data are no problem BUT dates are.

In Excel default date is mm/dd/yyyy but is dependent on PC's

international setting which is normally default

To Avoid any 5-March 3-May type mix-up, I have designed the FM so that you need to

enter dates as 'dd.Mon.yyyy i.e. in Characters in "Internet Date Format"

FUNCTION zjnc_get_range.

*"----

""Local interface:

*" IMPORTING

*" REFERENCE(RANGENAME) TYPE C

*" REFERENCE(ITABNAME) TYPE C

*" REFERENCE(IRECNAME) TYPE C

*" REFERENCE(SPREADSHEETINTF) TYPE REF TO I_OI_SPREADSHEET

*"----

*" REFERENCE(SPREADSHEETINTF) TYPE REF TO I_OI_SPREADSHEET

*"----

DATA:

stru_ref TYPE REF TO cl_abap_structdescr,

comp_tab TYPE abap_compdescr_tab,

one_comp TYPE abap_compdescr,

one_name TYPE string,

type_ref TYPE REF TO cl_abap_typedescr,

is_ddic TYPE abap_bool,

lt_ddic TYPE dd_x031l_table,

wa_ddic TYPE x031l.

DATA: zjncranges TYPE soi_range_list,

zjnccontents TYPE soi_generic_table,

zjnconerange TYPE soi_range_item,

zjnconeitem TYPE soi_generic_item,

prevrow(4) TYPE n,

nrow(4) TYPE n,

ncolumn(4) TYPE n,

mystring TYPE string,

mydate LIKE sy-datum.

FIELD-SYMBOLS: <fs_type> TYPE ANY,

<fs_table> TYPE STANDARD TABLE,

<fs_line> TYPE ANY.

CONCATENATE '(' sy-cprog ')' itabname INTO mystring.

ASSIGN (mystring) TO <fs_table>.

CONCATENATE '(' sy-cprog ')' irecname INTO mystring.

ASSIGN (mystring) TO <fs_line>.

stru_ref ?= cl_abap_structdescr=>describe_by_data( <fs_line> ).

comp_tab = stru_ref->components.

REFRESH zjncranges.

MOVE rangename TO zjnconerange-name.

APPEND zjnconerange TO zjncranges.

CALL METHOD spreadsheetintf->get_ranges_data

IMPORTING

contents = zjnccontents

error = zjncerror

retcode = zjncretcode

CHANGING

ranges = zjncranges.

MOVE 0 TO prevrow.

LOOP AT zjnccontents INTO zjnconeitem.

MOVE zjnconeitem-row TO nrow.

IF nrow <> prevrow.

IF prevrow <> 0.

APPEND <fs_line> TO <fs_table>.

ENDIF.

CLEAR <fs_line>.

MOVE nrow TO prevrow.

ENDIF.

MOVE zjnconeitem-column TO ncolumn.

READ TABLE comp_tab INDEX ncolumn INTO one_comp.

CONCATENATE '(' sy-cprog ')' irecname '-' one_comp-name INTO one_name.

ASSIGN (one_name) TO <fs_type>.

IF one_comp-type_kind <> 'D'.

MOVE zjnconeitem-value TO <fs_type>.

ELSE.

TRANSLATE zjnconeitem-value TO UPPER CASE.

CALL FUNCTION 'CONVERSION_EXIT_SDATE_INPUT'

EXPORTING

input = zjnconeitem-value

IMPORTING

output = mydate.

MOVE mydate TO <fs_type>.

ENDIF.

ENDLOOP.

IF prevrow <> 0.

APPEND <fs_line> TO <fs_table>.

ENDIF.

ENDFUNCTION.

Thanks

Saurabh

Re: Excel multiple sheets read

Former Member — Thu, 06 Nov 2008 10:40:28 GMT

Hi,

If you are using MS Office 2007

Try using the FM

'TEXT_CONVERT_XLS_TO_SAP'

Regards,

Chandru

Re: Excel multiple sheets read

Former Member — Thu, 06 Nov 2008 10:43:37 GMT

I have to use OLE2. Any idea? Maybe someone coulde post me a list of methods and properties of excel object?

Re: Excel multiple sheets read

Former Member — Thu, 06 Nov 2008 10:53:18 GMT

Hi Luka.

Check the below file may give you some hint on your issue.

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/60750cf2-fab1-2910-dd8b-a5c810023310

Cheers!!

VEnk@

Re: Excel multiple sheets read

Former Member — Thu, 06 Nov 2008 11:08:32 GMT

Nice article, but still nothing about method SELECT SHEET.

Re: Excel multiple sheets read

Former Member — Thu, 06 Nov 2008 11:19:56 GMT

Ok, problem solved:



GET PROPERTY of application 'WORKSHEETS' = worksheet EXPORTING #1 = i_tabname.

Thx, for contribution.

topic Re: Excel multiple sheets read in Application Development and Automation Discussions

Excel multiple sheets read

Re: Excel multiple sheets read

Re: Excel multiple sheets read

Re: Excel multiple sheets read

Re: Excel multiple sheets read

Re: Excel multiple sheets read

Re: Excel multiple sheets read