02-28-2012 8:56 AM
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.
02-28-2012 10:27 AM
Hi,
http://help.sap.com/saphelp_erp60_sp/helpdata/de/59/ae3df2488f11d189490000e829fbbd/frameset.htm
Is that what you're looking for?
BR
Marcin Cholewczuk
02-28-2012 10:50 AM
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.
02-28-2012 10:54 AM
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,
02-28-2012 11:40 AM
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.
02-28-2012 1:23 PM
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
02-28-2012 1:54 PM
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.
02-29-2012 9:54 AM
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