Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Read properties of Excel though OLE

0 Kudos

Hi all,

I have a requirement to read the properties of an excel sheet. By properties I mean the workbook properies where we can maintain the Author and Title of the workbook,

I have already implemented OLE to read the multiple sheets of the excel, but need help to find a way in which we can even read the properties.

Any inputs will be appreciated.

Thank you.

7 REPLIES 7

marcin_cholewczuk
Active Contributor
0 Kudos

Former Member
0 Kudos

Hi,

I don't think you can access the document properties (Title, author,...) with a GET PROPERTY statement...

So I don't think you can set them with OLE... Those properties can be maintained outside of Excel itself and are even not recorded with macros...

I guess you could probably create an external command (such as ATTRIB?) to do this (?)

Kr,

Manu.

0 Kudos

Yes GET/SET PROPERTY is not the right one here.

Manu,

Could you please provide more detail on creating an externam command for this scenario.

Thanks,

0 Kudos

External commands can be maintained and tested trough transaction SM69... However I cannot say if this is the correct approach... By reading your first post I tough the OS command ATTRIB would do the trick, but that one can only be used to set the file attributes (ReadOnly, Archive, ...) not the properties such as author...

I'll let you know if I have a better idea!

Kr,

Manu.

0 Kudos

Hi

http://vbadud.blogspot.com/2007/05/retrieve-file-properties-vba.html


include ole2incl.
DATA: excel type ole2_object,
      workbooks type ole2_object,
      workbook  type ole2_object,
      str TYPE ole2_object,
      str2(30) TYPE c.

  CREATE OBJECT excel 'Excel.Application'.
  CALL METHOD OF excel 'WORKBOOKS' = workbooks.
  CALL METHOD OF workbooks 'OPEN' = workbook EXPORTING #1 = 'c:\tmp\a.xls'.
  CALL METHOD OF workbook 'BuiltinDocumentProperties' = str EXPORTING #1 = 'Title'.
  GET PROPERTY OF str 'Value' = str2.
  WRITE str2.
  FREE OBJECT: str,
               workbook,
               workbooks,
               excel.

Best Regards

Marcin Cholewczuk

0 Kudos

Thanks a lot, this looks pretty close to what I wanted.

I just tried implementing this piece of code into my logic, though the sy-subrc always shows 0 I get no values into my variables.

CALL METHOD OF workbooks 'OPEN' = workbook EXPORTING #1 = p_file. -> here p_file is my paramater for file name

CALL METHOD OF workbook 'BuiltinDocumentProperties' = str EXPORTING #1 = 'Title'.

GET PROPERTY OF str 'Value' = str2.

Initially my file was a .xlsm file, I thought may be that is an issue. I converted it into a .xls file, but still str2 is not populated with the title.

0 Kudos

Hi,

I've tested code that I've pasted, so it works. File type shouldn't play any role here.

Sometimes when I don't close Excel or don't clear ole objects at the end of ABAP report I have some problems with opening excel second time (ole objects are not ceated). For example you can see in task manager that excel is still open. I guess this has something to do with how SAP handels OLE objects (or with ole concept in general).

Try rebooting your computer and in your report always check if object was properly created. Example in OLE transaction.

Best Regards

Marcin Cholewczuk