OLE Uploading Excel file containing ole objects in worksheet

[Former Member]

Hi there,

I know that there are a few options to upload an Excel file to an internal table or even display an Excel sheet in ALV. My question is more specific: Is there any way to read or display a cell content in the Excel file if the content is an ole object link or file etc.? E.g., one of the cells in my Excel sheet has an ole file object (so when you click on it it starts the file and shows the content). How can I read this cell in ABAP? Is there any trick using, e.g. function module ALSM_EXCEL_TO_INTERNAL_TABLE?

Thanks

[Sandra_Rossi]

You may use ABAP OLE, cf [sap library|http://help.sap.com/saphelp_nw70/helpdata/EN/59/ae3c98488f11d189490000e829fbbd/frameset.htm] and [abap documentation|http://help.sap.com/abapdocu/en/ABENOLE2.htm] (and microsoft VBA for excel to know methods and properties) :

DATA: EXCEL TYPE OLE2_OBJECT,
WORKBOOK TYPE OLE2_OBJECT,
SHEET TYPE OLE2_OBJECT,
CELL_START TYPE OLE2_OBJECT,
CELL_END TYPE OLE2_OBJECT,
RANGE TYPE OLE2_OBJECT.

* Start EXCEL
CREATE OBJECT EXCEL 'excel.application'.

SET PROPERTY OF EXCEL 'visible' = 1.

* Open the file
CALL METHOD OF EXCEL 'workbooks' = WORKBOOK.
CALL METHOD OF WORKBOOK 'open' EXPORTING #1 = 'c:\...\xxx.xls'.

* Open the first sheet in the workbook
CALL METHOD OF EXCEL 'worksheets' = SHEET EXPORTING #1 = 1.
CALL METHOD OF SHEET 'activate'.
* etc.

Or use Desktop Office Integration, cf [DOI in sap library|http://help.sap.com/saphelp_nw2004s/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm]. It provides an abap object interface. There are demo programs within SAP system, named SAPRDEMO, SAPRDEMO_SPREADSHEET_INTERFACE is a good demo.

[Former Member]

Hi Sandra,

Thanks for taking time and responding to my question. This is not exactly what I meant in my question. The Excel file would contain, e.g., an ole object embedded to the Excel sheet as an icon pointing to - let say, a PDF file. In Excel application if you click on this object the PDF file would open in a separate window, e.g. I understand that using OLE automation in ABAP we can read information from Excel. But how could I read this ole embedded object? I hope that is now more clear what my question is.

Thanks,

Shahram

[Former Member]

Hi ,

It is really an intresting question , one option i would like to try is read the file first , so i get the list of all the files in my internal table and then use these file name and read it one by one , using ole2 or may be GUI_UPLOAD or any other functions.

Regards

Arun

[Former Member]

Hi Arun,

That is exactly the problem. How would you get the information about the filename that is linked to the object? I am assuming that there must be an OLE method to read the object in Excel, but I cannot find it. If that was the case, the rest would be very easy.

Shahram

[Former Member]

Hi ,

Generally what i have tried a few times is that to get the details of the method by trying to record a macro in the excel and the perform the required action . Once the macro is recoded we can go and see the macro and get the method used , this is more of a trial and error method , as getting the exact method and how we call it takes some time.

Regards

Arun

[Sandra_Rossi]

According to me, it's just a VBA Excel question! If you know how to do it in VBA Excel then you can do it with ABAP OLE.

The simplest method is to use the macro recorder in Excel so that you know which object/method corresponds to hyperlink.

You'll get for example:

    Range("A1").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Adapt it so that you may run it from an external program:

Dim v As Application
Dim w As Workbooks
Dim x As Workbook
Dim y As Worksheet
Dim z As Range
Dim a As Hyperlink
set v = Application
set w = v.Workbooks
Set x = w.Open("D:\Documents and Settings\sandra.rossi\My Documents\Book1.xls")
Set y = x.Worksheets(1)
Set y = Application.ActiveSheet
Set z = y.Range("A1")
Set a = z.Hyperlinks(1)
Call a.Follow
Call x.Close(False)
Call v.Quit

Now you just have to translate it into ABAP:

REPORT  ZZYSRO_EXCEL_RUN_HYPERLINK.
TYPE-POOLS ole2.
DATA v TYPE ole2_object.
DATA w TYPE ole2_object.
DATA x TYPE ole2_object.
DATA y TYPE ole2_object.
DATA z TYPE ole2_object.
DATA a TYPE ole2_object.
CREATE OBJECT v 'excel.application'.
CALL METHOD OF v 'Workbooks' = w.
CALL METHOD OF w 'Open' = x EXPORTING #1 = 
'D:\Documents and Settings\sandra.rossi\My Documents\Book1.xls'.
GET PROPERTY OF x 'worksheets' = y EXPORTING #1 = 1.
GET PROPERTY OF y 'range' = z EXPORTING #1 = 'A1'.
GET PROPERTY OF z 'hyperlinks' = a EXPORTING #1 = 1.
CALL METHOD OF a 'Follow'.
CALL METHOD OF x 'Close'.
CALL METHOD OF v 'Quit'.

[Former Member]

Hi Sandra,

Thanks once again for your reply. However, it is still not resolving the problem. I understand that in case of hyperlinks assigned to a cell you code would work. I have used it myself a few times in the past. Also, I am aware about the macro recording tool, even though, I have to admit that I am not a VB expert at all. But I am still not able to translate what I get from the recording to ABAP. The ole object is not attached to any cell, but linked to a file. Any ideas?

[Sandra_Rossi]

But your initial question was about an hyperlink in a cell !

Could you just be more specific, please.

How is the file attached to the excel file ?

Could you explain again what you want to do ?

Edited by: Sandra Rossi on Feb 6, 2009 6:05 PM

[Former Member]

Use the following path to insert an ole object in the excel sheet:

Insert -> Object -> from file -> <select any file> -> check as icon/symbol

This way you will attach an ole object to the Excel file. Now, how can this object be read from ABAP?

[Sandra_Rossi]

Okay, so it's a Shape object.

If you want to get all shape objects (I try to imagine what you want to do) in the first worksheet of the workbook. So you'll have to know the number of objects : workbook.worksheets(1).shapes.count property). Iterate on the Shapes collection : first shape will be workbook.worksheets(1).shapes(1), ...

Then, what do you want to do with abap ?

If you want to paste each shape to another office (or ole) document, it won't be difficult (use worksheet.shapes(1).copy, and any other application paste method).

If you want to save each object to unix, it will be a little bit more complex. I found threads which will help you:

http://www.eggheadcafe.com/software/aspnet/32568545/how-to-copy-clipboard-to.aspx

http://www.oaltd.co.uk/Excel/Default.htm, look at PastePicture.xls

I think I cannot help you more, and most part is out of SAP scope. Try first to do the visual basic. Then if you need ABAP OLE help, ask a new question.

[former_member610985]

Hi Shahram,

Could you please explain me how to insert the ole object(icon) in to an excel file. As you mentioned in the above reply i am not clear on this logic.

Regards,

Arun.

[Former Member]

I could resolve this problem on my own based on some hints provided by Sandra. Thanks to all.

[Sandra_Rossi]

how ? (please give hints for people who'd be interested in the future)

[Former Member]

Hi all,

Does anyone have an idea how the hyperlink of a cell can be obtained? Maybe Shahram Shadmani still reads in this thread.

I import data from an Excel sheet where the content of a cell is a hyperlink. I need to import the document that is linked and show it.

Thanks for your help,

Stefan