Accessing Custom Doc Properties on Closed File

[Guest]

Is it possible to do this? I am trying to get data out of a team site and
I have the site mapped. We also have custom document properties for year,
month, etc. I'd like to only open those workbooks that meet a specified
selection. Can this be done, or can we only get the custom document
properties when the workbook is opened. Right now I'm opening every workbook
and looking at the properties, but I'd like to cut out the open step if
possible.

Thanks,
Barb Reinhardt

 

[RB Smissaert]

You can do this with DSOFile.dll or try this function:

Function GetFileProperty(vFolder As Variant, _
strFile As String, _
Optional lIndex As Long, _
Optional strItemName As String) As String

Dim i As Long
Dim objShell As Object
Dim objFolder As Object
Dim objFolderItem As Object
Dim arrHeaders(41)

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(vFolder)
Set objFolderItem = objFolder.ParseName(strFile)

If Len(strItemName) > 0 Then
For i = 0 To 40
arrHeaders(i) = objFolder.GetDetailsOf(objFolder.Items, i)
If arrHeaders(i) = strItemName Then
lIndex = i
Exit For
End If
Next
End If

GetFileProperty = objFolder.GetDetailsOf(objFolderItem, lIndex)

End Function


Use it like this:

Sub test()

MsgBox GetFileProperty("C:\ExcelFiles\", "MyFile.xls", , "Category")

End Sub


It works on for example text files as well.


RBS

 

[Guest]

I'm actually wanting to check the properties of a PowerPoint presentation.
I'm getting an error on this line:

Set objFolderItem = objFolder.ParseName(strFile)

Run time error '91'
Object variable or With block variable not set

Any idea what the issue might be?

Thanks,
Barb Reinhardt

 

[RB Smissaert]

Can you post the actual code you did run?
Will try it on a PP file here.

RBS

 

[RB Smissaert]

Working fine here on a .ppt file:

Sub test()

MsgBox GetFileProperty("C:\ExcelFiles\", "test.ppt", , "Category")

End Sub


RBS

 

[Guest]

I'm trying it on a team site that's mapped to a drive. Might that be the
problem?

 

[RB Smissaert]

Probably yes.
Could you post the code you run to call the function?

RBS

 

[Guest]

The code gets caught up on this line in what you sent me:

Set objFolderItem = objFolder.ParseName(strFile)

where strfile = filename.ppt

 

[RB Smissaert]

Probably if you move your mouse over it you will see that objFolder is
Nothing.
What I need though is how you call the function. So, the folder and file
etc.
Have tried it here with a mapped drive and that works fine.

RBS

 

[Guest]

Within the Function, vFolder is

S:\FolderName\

and yes, objFolder is nothing.

 

[Guest]

OK, this is what I've found

Set objFolder = objShell.Namespace(vFolder) '<~~~does not work

Set objFolder = objShell.Namespace("S:\Operations Monthly Reports")
'<~~works

vFolder = S:\Operations Monthly Reports

???

 

[RB Smissaert]

So, does it work now then?
Could you post the exact code you are running, so that is in the Sub
that calls that Function?

RBS

 

[Guest]

I think I'm going to have to revisit this tomorrow. When I manually type
in the value of vFolder, objFolder is defined, when I don't and use the value
of vFolder pulled into the function, it objFolder is not defined. I can't
get to the macro right now. I'll post it in the AM if I haven't figured it
out.

 

[Guest]

I figured out my initial problem. It was my error. Dimensions didn't
match.

Now it's not finding the Custom Document Properties of these presentations.

In this section I added a debug.print statement to see what was being
returned.

If Len(strItemName) > 0 Then
For i = 0 To 40
arrHeaders(i) = objFolder.GetDetailsOf(objFolder.Items, i)
debug.print i,arrheaders(i)
If arrHeaders(i) = strItemName Then
lIndex = i
Exit For
End If
Next
End If

I got
0 Name
1 Size
2 Type
3 Date Modified
4 Date Created
5 Date Accessed
6 Attributes
7 Status
8 Owner
9 Author
10 Title
11 Subject
12 Category
13 Pages
14 Comments
15 Copyright
16 Artist
17 Album Title
18 Year
19 Track Number
20 Genre
21 Duration
22 Bit Rate
23 Protected
24 Camera Model
25 Date Picture Taken
26 Dimensions
27
28
29 Episode Name
30 Program Description
31
32 Audio sample size
33 Audio sample rate
34 Channels
35 Company
36 Description
37 File Version
38 Product Name
39 Product Version
40 Keywords
I've never seen some of these. Am I looking at the right thing?

 

[RB Smissaert]

Am I looking at the right thing?

Yes, you are looking at the right thing and it looks it should work.
Can you read the properties now?

RBS