Excel Built In Properties

[spl]

Maybe not important, but played around with a script from Tech DB to iterate Excel Built In Properties. It was specific to .xls so tried with .xlsx, [and it was also limited to 15 properties]. Below is my attempt to work with attached cats.xlsx saved as zip]. It will fail with an OLE error, but I want to display attached jpg. I think CLR/.NET would be needed to update the script... dunno

Code Select Expand

XL = "C:\temp\cats.xlsx"
;Open Excel
objXL= ObjectOpen("Excel.Application")
objXL.Visible = @FALSE

;Open XLS file
MyWorkbook = objXL.workbooks
MWO = MyWorkbook.open(XL)

;Get active workbook object
AWB = objXL.activeworkbook
Props = ""
for i = 1 to 15
   bidp = AWB.BuiltinDocumentProperties(i)
   propval = bidp.value
   len = StrLen(propval)
   Props = Props : "Prop %i% length in %docname% is %len% %propval%"
next
ObjectClose(objXL)
Message(XL:" BuiltIn Props",Props)


[td]

The following prevents the error but doesn't explain why only string values are accessible.

Code Select Expand

XL = "C:\Temp\cats.xlsx"
;Open Excel
objXL= ObjectOpen("Excel.Application")
objXL.Visible = @FALSE

;Open XLS file
MyWorkbook = objXL.workbooks
MWO = MyWorkbook.open(XL)

;Get active workbook object
AWB = objXL.activeworkbook
Props = ""
i = 0
PropEnum = AWB.BuiltinDocumentProperties
foreach bidp in PropEnum
   i += 1
   if  bidp.type == 4  ; String value.
      propval = bidp.value
      len = StrLen(propval)
      Props = Props : "Prop %i% length in %docname% is %len% %propval%":@lf
   endif
next
ObjectClose(objXL)
Message(XL:" BuiltIn Props",Props)


[spl]

Thanks for clearing the error. This seems to be more what I was after

Code Select Expand

XL = "C:\temp\cats.xlsx"
;Open Excel
objXL= ObjectOpen("Excel.Application")
objXL.Visible = @FALSE

;Open XLS file
MyWorkbook = objXL.workbooks
MWO = MyWorkbook.open(XL)

;Get active workbook object
AWB = objXL.activeworkbook
Props = ""
i = 0
PropEnum = AWB.BuiltinDocumentProperties
foreach bidp in PropEnum
   i += 1
   name = bidp.Name
   if  bidp.type == 4  ; String value.
     
      propval = bidp.value
      Props = Props : "%name%: %propval%":@lf
   else
      Props = Props : "%name%: cannot be determined":@lf
   endif
next
ObjectClose(objXL)
Message(XL:" BuiltIn Props",Props)


[td]

MSFT states that a document property will "If the container application doesn't define a value for one of the built-in document properties, reading the Value property for that document property causes an error."

It might explain why the error occurrd in the first place.

[spl]

MSFT states that a document property will "If the container application doesn't define a value for one of the built-in document properties, reading the Value property for that document property causes an error."

It might explain why the error occurrd in the first place.

Yes, while the property names are there, document types would not support certain values, hence script them as 'unable to determine'... would assume those properties would iterate in a Word or Powerpoint document. Probably easy to verify.

[spl]

Attached is script with AskFileName() added to display built in properties. Might be suitable for Tech Database.