Ok, here is what I am doing.
1)
I registered the dll file by going to Start - Run and typing:
REGSVR32 "C:\DsoFile\dsofile.dll"
2)
In Excel VBE I went into References and browsed to the above path and
the following entry was selected:
DSO OLE Document Properties Reader 2.1
3)
Here is a procedure I found on
http://www.cpearson.com/excel/docprop.aspx
that I am trying to use to read from a closed file:
Function ReadPropertyFromClosedFile(FileName As String, PropertyName
As String, _
PropertySet As PropertyLocation) As Variant
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ReadPropertyFromClosedFile
' This uses the DSOFile DLL to read properties from a closed workbook.
This DLL is
' available at
http://support.microsoft.com/kb/224351/en-us. This code
requires a
' reference to "DSO OLE Document Properties Reader 2.1". The function
returns
' the value of the property if it exists, or NULL if an error occurs.
Be sure to
' check the return value with IsNull.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim DSO As DSOFile.OleDocumentProperties
Dim Prop As Office.DocumentProperty
Dim V As Variant
If FileName = vbNullString Then
ReadPropertyFromClosedFile = Null
Exit Function
End If
If PropertyName = vbNullString Then
ReadPropertyFromClosedFile = Null
Exit Function
End If
If Dir(FileName, vbNormal + vbSystem + vbHidden) = vbNullString Then
''''''''''''''''''''''''''''''''
' File doesn't exist. Get out.
''''''''''''''''''''''''''''''''
ReadPropertyFromClosedFile = Null
Exit Function
End If
Select Case PropertySet
Case PropertyLocationBOth, PropertyLocationBuiltIn,
PropertyLocationCustom
'''''''''''''''''''''''''''''
' Valid value for PropertySet
'''''''''''''''''''''''''''''
Case Else
'''''''''''''''''''''''''''''
' Invalid value. Get Out.
'''''''''''''''''''''''''''''
ReadPropertyFromClosedFile = Null
Exit Function
End Select
On Error Resume Next
Set DSO = New DSOFile.OleDocumentProperties
'''''''''''''''''''''''''''''''''''''''''''''
' Open the file.
'''''''''''''''''''''''''''''''''''''''''''''
DSO.Open sfilename:=FileName, ReadOnly:=True
'''''''''''''''''''''''''''''''''''''''''''''
' If we're working with BuiltIn or Both
' property sets, try to get the property.
'''''''''''''''''''''''''''''''''''''''''''''
If (PropertySet = PropertyLocationBOth) Or (PropertySet =
PropertyLocationBuiltIn) Then
Err.Clear
''''''''''''''''''''''''''''''''''''''
' Look first in the BuiltIn (Summary)
' properties. The SummaryProperties
' object is not a Collection whose
' members you can select. Instead,
' there is a separate property for
' each of the Summary Properties. Thus,
' use CallByName to get the values.
''''''''''''''''''''''''''''''''''''''
V = CallByName(DSO.SummaryProperties, PropertyName, VbGet)
If Err.Number <> 0 Then
If PropertySet = PropertyLocationBOth Then
'''''''''''''''''''''''''''''''''''''
' We're looking in both property sets.
' Not found in BuiltIn. Try Custom.
'''''''''''''''''''''''''''''''''''''
Err.Clear
V = DSO.CustomProperties(PropertyName)
If Err.Number <> 0 Then
'''''''''''''''''''''''''''''''''
' Not found. Return NULL.
'''''''''''''''''''''''''''''''''
DSO.Close savebeforeclose:=False
ReadPropertyFromClosedFile = Null
Exit Function
Else
'''''''''''''''''''''''''''''''''
' Found. Return value.
'''''''''''''''''''''''''''''''''
DSO.Close savebeforeclose:=False
ReadPropertyFromClosedFile = V
Exit Function
End If
Else
''''''''''''''''''''''''''''''''''''''
' Not found in BuiltIn and we're not
' looking in both sets so return NULL
' and get out.
''''''''''''''''''''''''''''''''''''''
DSO.Close savebeforeclose:=False
ReadPropertyFromClosedFile = Null
Exit Function
End If
Else
'''''''''''''''''''''''''''''''''
' Found. Return value.
'''''''''''''''''''''''''''''''''
DSO.Close savebeforeclose:=False
ReadPropertyFromClosedFile = V
Exit Function
End If
End If
If (PropertySet = PropertyLocationBOth) Or (PropertySet =
PropertyLocationCustom) Then
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' We're looking at Custom properties or both. We've already
' looked in Custom, so don't do it again.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Err.Clear
V = DSO.SummaryProperties(PropertyName)
If Err.Number <> 0 Then
Err.Clear
V = DSO.CustomProperties(PropertyName)
If Err.Number <> 0 Then
'''''''''''''''''''''''''''''''''
' Not found. Return NULL.
'''''''''''''''''''''''''''''''''
DSO.Close savebeforeclose:=False
ReadPropertyFromClosedFile = Null
Exit Function
Else
'''''''''''''''''''''''''''''''''
' Found. Return value.
'''''''''''''''''''''''''''''''''
DSO.Close savebeforeclose:=False
ReadPropertyFromClosedFile = V
Exit Function
End If
Else
'''''''''''''''''''''''''''''''''
' Found. Return value.
'''''''''''''''''''''''''''''''''
DSO.Close savebeforeclose:=False
ReadPropertyFromClosedFile = V
Exit Function
End If
End If
DSO.Close savebeforeclose:=False
End Function
4)
Here is the code I'm using to call the above function:
Private Sub CommandButton2_Click()
Dim test
test = ReadPropertyFromClosedFile("C:\04_02_10_12_53_08___RE
Opportunity Report.msg", "Author",
PropertyLocation.PropertyLocationBoth)
End Sub
5) As soon as the function is called I get the following error:
Compile Error: User-defined type not defined
When I click OK it highlights the following (2nd) declaration:
Prop As Office.DocumentProperty
6)
I see the following note on cpearson's website:
"NOTE: The DSO OLE Document Property Reader does NOT parse properties
out of Office 2007 and later files, since these are not OLE files."
This message combined with the above error is why I thought DSOfile
could not be used.
penTextFile(sName)