Sleeper: Fetching Object Properties

[MWE]

In most MS applications, you can view file properties for the appl file by navigating to File | Properties. If a particular file is open, you can extract that data programatically. Is there a way to programmatically extract file properties without opening the file?

Thanks

[Bob Phillips]

Yes you can, but you have to install DSO. You can get it at http://support.microsoft.com/?id=224351.

I used to have a nice routine that could get the properties, checking whether the book was open and using the builtindocumentproperties collection if so, going to DSO if not. But ... the *?$$$#!s changed the interface, so I knocked up this code to show how it works.

Option Explicit

Sub DSO()
Dim FileName As String
Dim fOpenReadOnly As Boolean
Dim DSO As DSOFile.OleDocumentProperties
Dim oSummProps As DSOFile.SummaryProperties
Dim oCustProp As DSOFile.CustomProperty
Set DSO = New DSOFile.OleDocumentProperties
    FileName = "C:\Documents and Settings\Bob\My Documents\My Spreadsheets\Oz CF with Text.xls"
    DSO.Open FileName, fOpenReadOnly, dsoOptionOpenReadOnlyIfNoWriteAccess
'Get the SummaryProperties (these are built-in set)...
    Set oSummProps = DSO.SummaryProperties
    Debug.Print "Application:  " & oSummProps.ApplicationName
    Debug.Print "Application:  " & oSummProps.Author
    Debug.Print "Version:      " & oSummProps.Version
    Debug.Print "Subject:      " & oSummProps.Subject
    Debug.Print "Category:     " & oSummProps.Category
    Debug.Print "Company:      " & oSummProps.Company
    Debug.Print "Keywords:     " & oSummProps.Keywords
    Debug.Print "Manager:      " & oSummProps.Manager
    Debug.Print "LastSaved by: " & oSummProps.LastSavedBy
    Debug.Print "WordCount:    " & oSummProps.WordCount
    Debug.Print "PageCount:    " & oSummProps.PageCount
    Debug.Print "ParagraphCount: " & oSummProps.ParagraphCount
    Debug.Print "LineCount:    " & oSummProps.LineCount
    Debug.Print "CharacterCount: " & oSummProps.CharacterCount
    Debug.Print "CharacterCount (w/spaces): " & oSummProps.CharacterCountWithSpaces
    Debug.Print "ByteCount:    " & oSummProps.ByteCount
    Debug.Print "PresFormat:   " & oSummProps.PresentationFormat
    Debug.Print "SlideCount:   " & oSummProps.SlideCount
    Debug.Print "NoteCount:    " & oSummProps.NoteCount
    Debug.Print "HiddenSlides: " & oSummProps.HiddenSlideCount
    Debug.Print "MultimediaClips: " & oSummProps.MultimediaClipCount
    Debug.Print "DateCreated:  " & oSummProps.DateCreated
    Debug.Print "DateLastPrinted: " & oSummProps.DateLastPrinted
    Debug.Print "DateLastSaved: " & oSummProps.DateLastSaved
    Debug.Print "TotalEditingTime (mins): " & oSummProps.TotalEditTime
    Debug.Print "Template:    " & oSummProps.Template
    Debug.Print "Revision:    " & oSummProps.RevisionNumber
    Debug.Print "IsShared:    " & oSummProps.SharedDocument
'Add a few other items that pertain to OLE files only...
    If DSO.IsOleFile Then
        Debug.Print "CLSID:      " & DSO.CLSID
        Debug.Print "ProgID:     " & DSO.ProgId
        Debug.Print "OleFormat:  " & DSO.OleDocumentFormat
        Debug.Print "OleType:    " & DSO.OleDocumentType
    End If
'Now the custom properties
    For Each oCustProp In DSO.CustomProperties
        Debug.Print oCustProp.Name & ": " & CStr(oCustProp.Value)
    Next oCustProp
Set oCustProp = Nothing
    Set oSummProps = Nothing
    Set DSO = Nothing
End Sub

Amusingly, on the download page, I noticed that they had a 'customers who downloaded this file also downloaded ...' section, just like on the books and CD sites

[MWE]

Bob: thanks for your very useful reply (as is typical). I will install DSO and give this a try. It appears to be appl independent and, thus, more valuable.

However, it requires that you open the target file. For my need, something that did not require the file to be opened would be preferred (should run faster). Any thoughts along those lines?

[TonyJollans]

However, it requires that you open the target file.

It always amuses me when people ask to be able to access file content without opening the file. By definition you can't.

What people mean is without opening it in (insert application here). Bob's code, of course, opens the file but it doesn't open it in Excel and it's the best you're going to get.

[MWE]

It always amuses me when people ask to be able to access file content without opening the file. By definition you can't.

What people mean is without opening it in (insert application here). Bob's code, of course, opens the file but it doesn't open it in Excel and it's the best you're going to get.

Good point, and I should have thought through my request better. But then you would not have something about which to be amused and your day would be incrementally less interesting

[TonyJollans]

incrementally less interesting

What a wonderful phrase

[Ivan F Moala]

I have converted the MS code to an Addin here

http://www.xcelfiles.com/DocProp.html

[MWE]

Bob/Tony/Ivan: I was able to get Bob's approach to work quite well. There is little of his original code left, but he provided all the pieces and mechanics. However, I am encountering two problems:
1. if there is a failure for any reason any where in the application (all the bugs are not worked out yet); the DSO stuff no longer works at all. I have to exit Excel and start the application again. Thoughts?

2. the application operates on a series of files; opening, collecting properties, and closing each one in a loop. If I run the application 5 times (same set of target files), the results are the same for, say, 4 runs, but different for one of the five. The difference is that one or more of the files is not available -- the actual error is "access denied". It is never the same file(s) and the files are pretty standard stuff, e.g., Word docs, Excel workbooks, etc. And, any time that happened and I check the file(s), I seem to have complete ability to open it. Thoughts?

[Bob Phillips]

Might help to see the code, but it may just be a problem with DSO, it does seem to have changed a bit.

[Emily]

Ivan,

Your Add-in seems not work properly for DSO OLE Document Properties Reader 2.0.
Missing reference error "DS: OLE Document Properties 1.4 Object Library"

Pearson: Document Properties
http://www.cpearson.com/excel/docprop.htm

Regards
Emily

[Ivan F Moala]

When I first did this one some time ago the DOSFile was un supported. They have since brought out an updated version, the one you see.

The original was from here.

http://support.microsoft.com/default...;EN-US;q253338

The addin actually works with this version.

[Emily]

Thanks

[MWE]

Might help to see the code, but it may just be a problem with DSO, it does seem to have changed a bit.

I examined the problem more carefully and resolved 90% of it. Most of the "access denied" was really just telling me that the particular file did not have the file properties setup consistent with the DSO access. The other 10% is denied access to MS files and is quite sporadic and random -- I need to look at this more carefully.

Thanks

[brettdj]

You may want to check out my KB article on recursive extraction of file properties, http://vbaexpress.com/kb/getarticle.php?kb_id=405

This code grabs

"Path"
"File Name"
"Last Accessed"
"Last Modified"
"Created"
"Type"
"Size"
"Owner"
"Author"
"Title"
"Comments"

Cheers

Dave