EXCEL to internal table

[Former Member]

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

[RichHeilman]

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

[Former Member]

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

[RichHeilman]

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

[RichHeilman]

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

Regards,

RIch Heilman

[RichHeilman]

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

[RichHeilman]

Duplicate.

Regards,

Rich Heilman

Message was edited by: Rich Heilman

[Former Member]

Rich,

It worked ! Thanks for all your effort.

I have awarded you the full points.

Best Regards,

-Saif Alam

[Former Member]

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.

[Former Member]

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

[aaruljothi]

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.