CustomDocumentProperties

[Robert Hind]

I'm trying to use some code I've found on Chip Pearson's web site to access
CustomDocumentProperties in Closed Files but without success. The code is as
follows. Can anyone help?

Dim FileName As String
Dim DSO As DSOleFile.PropertyReader
Set DSO = New DSOleFile.PropertyReader
FileName = "C:\Book1.xls"
With DSO.GetDocumentProperties(sfilename:=FileName)
Debug.Print .AppName
Debug.Print .Author
Debug.Print .ByteCount
Debug.Print .Company
'
' lots more properties
'
End With


__________________________________________________________________

 

[Bob Phillips]

Robert,

Did you set a reference to DS: OLE Document Properties 1.2 Object Library as
Chip suggested?

If you don't have the library, you can get it at
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224351

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

Robert,

Specifically what problems are you having? Does the code cause
an error, or do you simply not get property values? Do you have
the proper reference set?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Robert Hind]

Chip

Thanks for your reply.

Yes. I've set the references exactly as you suggested in your article (in
fact the reference is to the later 1.4 Object Library).

There are no problems when refering to any of the inbuilt document
properties but when I try to refer to properties that are
CustomDocumentProperties my code gives an error..."Compile error: Method or
Data Member Not Found".

The specific code is as follows:-

Public Sub GetDocumentProperties()
ListFiles 'Runs another procedure which populates some of the variables
'referred to


Dim FileName As String
Dim DSO As DSOleFile.PropertyReader
Set DSO = New DSOleFile.PropertyReader

vba_FileListArea() = Range("xlVar_FileListArea").Value

'Stop
For vba_FileNo = 1 To Range("xlVar_FileCount").Value

'Open the file
FileName = vba_FileListArea(vba_FileNo, 1)
' FileName = "C:\Book1.xls"
With DSO.GetDocumentProperties(sfilename:=FileName)
Debug.Print .Author 'inbuilt DocumentProperty (no problem)
Debug.Print .Doc_MfestNo 'CustomDocumentProperty
Debug.Print .Doc_WkNo 'CustomDocumentProperty
Debug.Print .Route 'CustomDocumentProperty
Debug.Print .DespDate 'CustomDocumentProperty
'
' lots more properties
'
End With
Next vba_FileNo

End Sub

Any help appreciated.

Regards

 

[Rob van Gelder]

Try:
Debug.Print .CustomProperties("Doc_MfestNo").Value

You'll need to use On Error Resume Next or similar if the CustomProperty
doesn't exist.

 

[Andy Wiggins]

I can't comment on Chip's code, but here is an alternative you might want to
consider.

http://www.bygsoftware.com/examples/zipfiles/CustomDocumentProperties.zip

The workbook has a family of VBA routines to help you create, amend, and
delete Custom Document Properties

It's in the "Administration Utilities" section on page:
http://www.bygsoftware.com/examples/examples.htm

The code is open and commented.

--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Chip Pearson]

Robert,

To access a custom document property, you need to go through the
CustomProperties collection. For example,

With DSO.GetDocumentProperties(sfilename:=FileName)
Debug.Print .CustomProperties("Cust Prop").Value
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Robert Hind]

Chip
Thanks very much for your help. Worked a treat.

Regards
Robert Hind