topic Re: EXCEL to internal table in Application Development and Automation Discussions

EXCEL to internal table

Former Member — Wed, 21 Sep 2005 21:38:59 GMT

Hello fellow ABAPers,

I have a requirement to upload data from an EXCEL file to an internal table in SAP.. I have seen previous posts where people have suggested using FM ALSM_EXCEL_TO_INTERNAL_TABLE..

However the excel file I will need to read has several worksheets and I need to read a specific worksheet.

FM ALSM_EXCEL_TO_INTERNAL_TABLE reads data from the active worksheet.

Does there exist any other function similar to this that allows you to specify the worksheet? If not has anyone else run into this issue?

Thanks

Best Regards

-Saif Alam

Re: EXCEL to internal table

RichHeilman — Wed, 21 Sep 2005 23:47:30 GMT

In this case, I would suggest using OLE automation.

Check out this weblog, and check out the link to the reference guide.

/people/rich.heilman2/blog/2005/09/12/manipulate-excel-with-ole-abap

Regards,

Rich Heilman

Re: EXCEL to internal table

Former Member — Thu, 22 Sep 2005 03:40:55 GMT

Hi Saiful,

you can also use teh FM GUI_DOWNLOAD.

Ofcourse you shd convert the excel to a tab delimited ( or for that matter delimited by some special character) file.

Check the documentaiton on this..

Rgds,

Prashanth.

Re: EXCEL to internal table

Former Member — Thu, 22 Sep 2005 06:59:46 GMT

Hi,

If you have experience in OO ABAP programming,you can use Desktop Office Integration, spreadsheet interface(i_oi_spreadsheet).

http://help.sap.com/saphelp_nw04/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm

Svetlin

Re: EXCEL to internal table

aaruljothi — Thu, 22 Sep 2005 07:20:20 GMT

hi,

see this code.

&----

*& Report ZEXCELUP *

*& *

&----

*& *

&----

REPORT zexcelup

LINE-SIZE 1023

LINE-COUNT 250.

TABLES: alsmex_tabline.

DATA: BEGIN OF iexcel OCCURS 0.

INCLUDE STRUCTURE alsmex_tabline.

DATA: END OF iexcel.

No of columns

DATA: BEGIN OF data_tab OCCURS 0,

value_0001(25),

value_0002(25),

value_0003(25),

value_0004(25),

value_0005(25),

value_0006(25),

value_0007(25),

value_0008(25),

value_0009(25),

value_0010(25),

value_0011(25),

value_0012(25),

value_0013(25),

value_0014(25),

value_0015(25),

value_0016(25),

value_0017(25),

value_0018(25),

value_0019(25),

value_0020(25),

value_0021(25),

value_0022(25),

value_0023(25),

value_0024(25),

value_0025(25),

value_0026(25),

value_0027(25),

value_0028(25),

value_0029(25),

value_0030(25),

value_0031(25),

value_0032(25),

value_0033(25),

value_0034(25),

value_0035(25),

value_0036(25),

value_0037(25),

value_0038(25),

value_0039(25),

value_0040(25),

value_0041(25),

value_0042(25),

value_0043(25),

value_0044(25),

value_0045(25),

value_0046(25),

value_0047(25),

value_0048(25),

value_0049(25),

value_0050(25),

value_0051(25).

DATA: END OF data_tab.

DATA: tind(4) TYPE n.

DATA: zwfeld(19).

FIELD-SYMBOLS: <fs1>.

PARAMETERS: filename LIKE rlgrap-filename MEMORY ID m01,

noheader AS CHECKBOX.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = filename

i_begin_col = 1

i_begin_row = 1

i_end_col = 100

i_end_row = 30000

TABLES

intern = iexcel

EXCEPTIONS

inconsistent_parameters = 1

upload_ole = 2

OTHERS = 3.

IF sy-subrc <> 0.

WRITE: / 'EXCEL UPLOAD FAILED ', filename, sy-subrc.

ELSE.

SORT iexcel BY row col.

LOOP AT iexcel.

IF noheader = 'X'

AND iexcel-row = 1.

CONTINUE.

ENDIF.

tind = iexcel-col.

CONCATENATE 'DATA_TAB-VALUE_' tind INTO zwfeld.

ASSIGN (zwfeld) TO <fs1>.

<fs1> = iexcel-value.

AT END OF row.

APPEND data_tab.

CLEAR data_tab.

ENDAT.

ENDLOOP.

ENDIF.

data_tab will contain the records uploaded from excel.

you may have the header or may not.

Re: EXCEL to internal table

Former Member — Thu, 22 Sep 2005 17:49:48 GMT

Hello Rich and everyone,

Thanks for all your responses..

I think OLE automation would work best for my requirement.

Rich the reference guide and tutorial was very helpful.

My only question is how do I find the complete list of all the methods and properties for say the EXCEL.APPLICATION OLE object... The tutorial and guide does not show a property name or method to set the active sheet in Excel. I tried several combination but not able to set the active sheet.

Thanks again appreciate all the help from everyone.

Best regards,

-Saif Alam

Re: EXCEL to internal table

RichHeilman — Thu, 22 Sep 2005 17:54:50 GMT

That is the trickest part. Sometimes you have to record a macro in excel and then look at the VB code to figure it out. Try doing that first.

Please remember to award points for helpful answers.

Thanks.

Regards,

Rich Heilman

Re: EXCEL to internal table

RichHeilman — Thu, 22 Sep 2005 17:56:09 GMT

Also, the table OLELOAD has given me some hints in the past.

Regards,

RIch Heilman

Re: EXCEL to internal table

RichHeilman — Thu, 22 Sep 2005 18:00:47 GMT

In the reference guide, it does something like this....



*--Locate the chart onto the current worksheet 
*--Activate current sheet 

CALL METHOD OF gs_excel 'WorkSheets' = gs_activesheet   
      EXPORTING #1 = gv_sheet_name. 

CALL METHOD OF gs_activesheet 'Activate' . 

CALL METHOD OF gs_chart 'Location'
    EXPORTING #1 = 2 #2 = gv_sheet_name.

Does this help you?

Regards,

Rich Heilman

Re: EXCEL to internal table

RichHeilman — Thu, 22 Sep 2005 18:00:57 GMT

Duplicate.

Regards,

Rich Heilman

Message was edited by: Rich Heilman

Re: EXCEL to internal table

Former Member — Thu, 22 Sep 2005 18:11:19 GMT

Rich,

It worked ! Thanks for all your effort.

I have awarded you the full points.

Best Regards,

-Saif Alam