Checking Document Properties without opening the file

[Annelies]

Hi

I have a folder full of large Excel (3MB+) files. I want to import information from some of them, depending on some values in their custom properties. Because of the size of the files, and the subsequent delay in opening them, I would like to check the custom properties BEFORE I open the files.

Is this possible? I figure it must be, because you can display custom properties in the Windows Explorer view.

Any help would be greatly appreciated!

Thanks

Annelies

 

[PHV]

Have a look here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224351

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Annelies]

Hi

Thanks for this.

Is this the only way (or simply the easiest way) to achieve this? The reason that I ask is that I am working in a Corporate environment in which the workstations are very 'locked' down. We cannot register DLL's on our PC's, so getting this solution up and running is going to be difficult at best - distributing it to the handful of people that will want to use this Workbook will be even more difficult.

Thanks

Annelies

 

[fumei]

As far as I know there is no other way to get properties out except for opening the files - I mean besides the DLL solution suggested by PHV.

Gerry

 

[rmikesmith]

Annelies,

You can, indeed, inspect your Excel files' custom properties without opening them. The trick is to use the built-in (i.e. no external dll's or object library references) FileSearch object. Below is a function that will test a file for a particular custom property of type Text. See the online Help for more detail on referencing the various types of custom properties.

Function FileHasCustomProperty(ByVal sPath As String, ByVal sFile As String, _
         ByVal sProperty As String, ByVal sValue) As Boolean

  With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeAllFiles ' or msoFileTypeExcelWorkbooks
    .Filename = sFile
    .LookIn = sPath
    
    .PropertyTests.Add sProperty, msoConditionIncludes, sValue, , msoConnectorAnd
    
    .Execute
    FileHasCustomProperty = .FoundFiles.Count > 0
  End With

End Function

BTW - This function is modified from one given in Professional Excel Development by Bullen, Bovey & Green (their version inspected a Yes/No Custom Property Type).

You could call this function in a loop that extracts filenames and only open those that return True.

There may be efficiency gains by modifying the function to return the FoundFiles collection (instead of True/False for a single supplied filename) and looping through that instead.

Regards,
Mike

 

[fumei]

Hi Mike. Could you explain the variable sValue for me? It is not declared as a data type. So I am not sure what to pass into the function. Thanks.

Gerry

 

[combo]

The other way to access document properties is via Shell32 library. A reference to 'Microsoft Shell Controls and Automation' (or 'shell32.dll' file) should be set. For a given document:

Dim sShell As Shell32.Shell
Dim sSF As Shell32.Folder
Dim sFI As Shell32.ShellFolderItem
Set sShell = New Shell32.Shell
Set sSF = sShell.NameSpace("C:\")
Set sFI = sSF.ParseName("test.xls")
MsgBox sFI.ExtendedProperty("Author")

However, this properly returns values in only built-in document properties.

combo

 

[rmikesmith]

Gerry,

sValue should have been declared a String. That was an oversight. It corresponds to the custom property type of Text.


Mike

 

[fumei]

Function FileHasCustomProperty(ByVal sPath As String, _
ByVal sFile As String, _
ByVal sProperty As String, _
ByVal sValue As String) As Boolean

  With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeAllFiles
    .FileName = sFile
    .LookIn = sPath
    .PropertyTests.Add sProperty, msoConditionIncludes, sValue, , msoConnectorAnd
    .Execute
    FileHasCustomProperty = .FoundFiles.Count > 0
  End With
End Function

Sub CheckProperty()
MsgBox FileHasCustomProperty("c:\temp\", "mybook.xls", "Client", "Text")
End Sub

Always returns False, even though there is a file of that name, in that path, with that Custom property.

What am I doing wrong?

Gerry

 

[rmikesmith]

Gerry,

When you open mybook.xls and look at the Custom tab of the Properties dialog do you see the following in the Properties listbox?

Name Value Type
Client Text Text


Mike

 

[fumei]

Yes.

Gerry

 

[rmikesmith]

Gerry,

Your posted code/calling details look fine. I don't know what else to suggest. I re-tested by creating a second XL file with a new custom property and different value then saved it to another folder. The FileHasCustomProperty function returned True. I also checked for case sensitivity in each of the four paramters and they appear to be case-insensitive.

Mike

 

[fumei]

Hmmmmm.

Gerry

 

[Annelies]

I *think* that the reason you are having problems Gerry (which I also experienced) is that it would seem that FileSearch can only reference the properties available in the File > Open > Tools > Find dialog box.

These are the 'standard' file properties, and do not include Custom properties.

In saying that, I don't understand how Mike got his to work! I experienced exactly the same problem as you Gerry, but as soon as I changed sProperty to something standard, like 'Title' it worked fine.

Maybe I'm missing something...?

Annelies

 

[rmikesmith]

Very odd, Gentlemen. I will say that the example code from Professional Excel Development explicitly uses a Custom property since the authors' point is to identify closed workbooks as belonging to an application, prior to opening. No mention was made of Excel version dependency, but to cover all the bases, I used Excel 2003 to create & test the code I posted. I will try this out on Excel 2000, although I doubt that is the problem.


Regards,
Mike

 

[rmikesmith]

Gerry & Annelies,

I have re-tried the posted code under Excel 2000. The [tt]FileHasCustomProperty[/tt] function correctly identified a workbook containing a Custom property (Type Text), using both a previously created workbook as well as a new one created under Excel 2000. I was also able to manually locate this workbook by using File|Open|Tools|Find. In the Property combobox, type in the name of your custom property then its value in the Value textbox.


Regards,
Mike