Printable View
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
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.
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 http://vbaexpress.com/forum/images/smilies/001.gifCode: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
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?
It always amuses me when people ask to be able to access file content without opening the file. By definition you can't.Quote:
Originally Posted by MWE
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 :devil:Quote:
Originally Posted by TonyJollans
What a wonderful phrase :)Quote:
Originally Posted by MWE
I have converted the MS code to an Addin here
http://www.xcelfiles.com/DocProp.html
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?
Might help to see the code, but it may just be a problem with DSO, it does seem to have changed a bit.
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
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.
Thanks
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.Quote:
Originally Posted by xld
Thanks
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