Changing File "Summary" Properties Programmatically
 

Hey,

I am needing to write to the Summary properties of a file.
The properties are the ones in the Summary tab of a file (When you
right click on a file, then click on the properties tab, then click on
the Summary tab - you will see Title, Subject, Author, Category,
Keywords, Comments). These are the properties I am trying to WRITE.

I cannot use DSO, so DSO is not an option.

I have found that these properties are actually stored in a seperate
hidden file that is attached to the main file I am trying to change
the properties for. These are called NTFS Alternate Data Streams.
http://users.telenet.be/ws36637/properties.html

Problem is I cant figure out how to write to these files(i.e.
properties)
How would I be able to do this in VBA?

Thanks
Todd

Changing File "Summary" Properties Programmatically
 

You can use the "BuiltInDocumentProperties" property of the workbook.
From the help file...
ActiveWorkbook.BuiltinDocumentProperties.Item("Tit le") _
.Value = "Year-End Sales Results"
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
(30 different ways to sort in excel)





"hutteto"
wrote in message
...
Hey,

I am needing to write to the Summary properties of a file.
The properties are the ones in the Summary tab of a file (When you
right click on a file, then click on the properties tab, then click on
the Summary tab - you will see Title, Subject, Author, Category,
Keywords, Comments). These are the properties I am trying to WRITE.

I cannot use DSO, so DSO is not an option.

I have found that these properties are actually stored in a seperate
hidden file that is attached to the main file I am trying to change
the properties for. These are called NTFS Alternate Data Streams.
http://users.telenet.be/ws36637/properties.html

Problem is I cant figure out how to write to these files(i.e.
properties)
How would I be able to do this in VBA?

Thanks
Todd

Changing File "Summary" Properties Programmatically
 

Jim, the files that i will be setting properties for will be .OFT
files and they will be closed.

Changing File "Summary" Properties Programmatically
 

hutteto explained :
Jim, the files that i will be setting properties for will be .OFT
files and they will be closed.

There's a couple of things you should know about NTFS
SummaryProperties.

1. They only exist if the volume they are stored on is formatted NTFS.
Otherwise, moving/copying them to a non-NTFS volume scrubs these.

2. Although Vista/Win7 are NTFS, they don't support these properties
any longer.

3. Jim's suggestion is good for app files of compound structure. What
is an '.OFT' file anyway? Is it a compound structure?

4. DSOfile.dll is the only MS supported lib that allows direct
read/write of NTFS SummaryProperties without opening files. Desaware
makes a lib that does similar to DSOfile, but this won't read/write
'Category' as a SummaryProperty because it's considered a
DocumentSummaryProperty. DSOfile reads/writes all regardless of their
classification.

5. There's no VB/VBA class source code available that reads/writes the
NTFS Alternate Data Streams. It requires advanced use of several
complex interfaces and so everyone opts for the easier DSO lib as a
solution.

History:
I have used these SummaryProperties with CNC program files, which are
plain text files, so people working from a PC could see what the files
were. Typically, they have filenames like "O0001, O0010, O0011", which
are pretty non-descript. Using NTFS SummaryProperties allows people to
read descriptive info about the file via Windows Explorer's optional
columns.

Because CNC prog files are typically downloaded to machining centers
having non-NTFS drives, these same props are imbedded inside the file
and my CNC file manager app is used to read/write these along with the
SummaryProperties IF the filepath is a NTFS volume.

Thus, why I know the ins & outs of using these props on non-structured
files.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Changing File "Summary" Properties Programmatically
 

Garry,

..oft files are MS outlook email template files. We are running
Windows XP here with Office 2007. The file format I am running the
code from is .xlsm. I was having problems when I tried to use
DSOFile.dll, eventhough I regestered the file and set a reference to
it in my project. It was giving me the runtime error like it didnt
know what the dsofile objects were like I didnt have a reference set
for it. From what I read DSOfile is not supported in the .xlsm
format, so this is why I was getting errors. There has got to be a
way to do this using the the NTFS data streams... The file system is
NTFS.

Changing File "Summary" Properties Programmatically
 

hutteto explained on 1/3/2011 :
Garry,

.oft files are MS outlook email template files. We are running
Windows XP here with Office 2007. The file format I am running the
code from is .xlsm. I was having problems when I tried to use
DSOFile.dll, eventhough I regestered the file and set a reference to
it in my project. It was giving me the runtime error like it didnt
know what the dsofile objects were like I didnt have a reference set
for it. From what I read DSOfile is not supported in the .xlsm
format, so this is why I was getting errors. There has got to be a
way to do this using the the NTFS data streams... The file system is
NTFS.

DSOfile.dll does work in Excel 2007/2010 with VBA. I'm not sure how
you're using it that it's giving you an error. It must be installed and
registered on each machine that uses your xlsm. Is this the case?

Otherwise, my Excel addin version of the cnc program files manager
works just fine writing to SummaryProperties. Are you saying it doesn't
work for you on any machine? Show me your code and I'll see if I can
spot why!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Changing File "Summary" Properties Programmatically
 

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.

Changing File "Summary" Properties Programmatically
 

Oh I just realized I have been saying I am saving .oft files. I
actually meant .msg files. lol Sorry about that.
That still shouldnt be causing the error though.

Changing File "Summary" Properties Programmatically
 

hutteto formulated on Tuesday :
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"

This must be done on each machine that uses your project.


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

So far so good! Given the amount of code in Chip's wrapper function,
it's not surprising that your getting the UDT error. Do you have a UDT
named PropertyLocation defined somewhere?

I use much simpler routines for read/write which get/put data directly
from/to a spreadsheet that lists all files in a specified folder with
the specified file extensions listed in Filetypes. Filetypes is a place
where users can specify what file extensions their cnc program files
use other than the ones I listed earlier, which have no file extension.
These are Fanuc files and is the default for a list.

Basically, your '.msg' files are plain text files. If I copy a set of
Fanuc files and add the '.msg' extension to them, it still works in
Excel12. That suggests to me that the problem lies with the way you're
working with Chip's code. It's good code as is, but maybe you need
something less complex. Here's an example of what you could do using
FileSystemObject in place of DSO:

======================================
Function szSetGetCategoryProp(ByVal szFileName As String, _
Optional szStreamOut As Variant) As String
' This reads/writes a stream to a file for the 'Category' property
' This property cannot be viewed in Explorer.
'
' Arguments: szFilename [In] The fullname of the subject file
' szStreamOut [In] Optional. The value being written to
the stream object
'
' Returns: The StreamIn value. This will be the current contents when
reading the file.
'
' ctlSource:
'
Const sSource As String = "szSetGetCategoryProp()"

Const szStream As String = ":Category"

Dim fso As FileSystemObject
Dim f As Object 'The current file being processed
Dim sName As String 'The FileSystemObject we're working with.
'Consists of szStream appended to szFilename
Dim sStreamIn As String 'The current value of szFilename's stream

On Error GoTo CleanUp
sName = szFileName & szStream

If IsMissing(szStreamOut) Then
'Read the property value
Set fso = New FileSystemObject
Set f = fso.OpenTextFile(sName)
sStreamIn = f.ReadAll()
f.Close
'Pass it back to the caller
szSetGetCategoryProp = sStreamIn
' GoTo CleanUp
Else
'Write the property value
Set fso = New FileSystemObject
Set f = fso.CreateTextFile(sName)
f.Write szStreamOut
End If

CleanUp:
Set f = Nothing
Set fso = Nothing

End Function
======================================

Otherwise, I could also provide code for doing similar with DSO.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Changing File "Summary" Properties Programmatically
 

Thanks Gary,

Actually I will need to be able to bring in these column names
(Author, Title, Subject, Comments, etc...) into the column header of
an Explorer window.
All the files I will be dealing with here are saved emails and I will
need to update these properties. The purpose is to then go to that
saved directory where all the emails are and be able to sort/filter
the emails based on these properties.
So they will have to be able to be seen in Explorer. Is this what you
mean when you say "viewed in Explorer"?

Thanks

Changing File "Summary" Properties Programmatically
 

hutteto wrote on 1/4/2011 :
Thanks Gary,

Actually I will need to be able to bring in these column names
(Author, Title, Subject, Comments, etc...) into the column header of
an Explorer window.
All the files I will be dealing with here are saved emails and I will
need to update these properties. The purpose is to then go to that
saved directory where all the emails are and be able to sort/filter
the emails based on these properties.
So they will have to be able to be seen in Explorer. Is this what you
mean when you say "viewed in Explorer"?

Thanks

Yes. If you right-click a column header in Explorer you'll see a
dropdown list of optional columns to display. Windows remembers these
for each folder. Optionally, you can set up one folder how you want the
columns to display and then set all folders the same via the option in
the FolderOptions dialog.

What did you think about the FSO solution I posted?

I did some more review of Chip's code and conclude that it was created
for DSO1.4 and not DSO2.0, and so is why it doesn't work. You do know
that when you installed DSO2.0 it put sample code in the Source
subfolder. Unfortunately, you need VB6 to use the demo but you can
still view the code using a text editor. To do this, right-click the
file 'Source\Vb6Demo\FileProp.frm' and open it with Notepad. This demos
how to use DSO2.0 in VB/VBA. Just so you know.., DSO2.0 was released
for use by .Net apps and this is why the DSO1.4 code won't work with
DSO2.0.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Changing File "Summary" Properties Programmatically
 

Yes I looked at that FSO code but since it wont allow the properties I
set programmatically to b e seen in Explorer, that would not be an
option, since being able to see the properties is the desired result.

I have DSO OLE Document Properties Reader 2.1, so I assume 2.1 is
compatable with 2.0?

Also I checked the files and opened them in text editor, however I
could find no code for reading or writing properties to/from a closed
file.
Can you provide any code?

Thanks

Changing File "Summary" Properties Programmatically
 

hutteto wrote on 1/4/2011 :
Yes I looked at that FSO code but since it wont allow the properties I
set programmatically to b e seen in Explorer, that would not be an
option, since being able to see the properties is the desired result.

The Explorer doesn't provide columns for all of the props. I believe
'Keyword' is one of those.

My point about the FSO code is that it reads/writes the streams as
alternate data streams, do demonstrate that this approach does not give
you what you want. (Initially, this is what you asked for)

I don't use FSO much so the code sample may need to be tweaked to work
properly. For example,

Dim fso As Object 'not FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject") 'not New
FileSystemObject

Otherwise, it reads/writes the ADSs just fine.


I have DSO OLE Document Properties Reader 2.1, so I assume 2.1 is
compatable with 2.0?

Sorry, -my bad! The current version IS DSO2.1!

Also I checked the files and opened them in text editor, however I
could find no code for reading or writing properties to/from a closed
file.
Can you provide any code?

That code doesn't physically 'open' any files per se. It just makes
them available to DSO for read/write purposes. All this takes place
under the hood (so to speak). So.., if you send values to a file while
Explorer is open you will see the values appear when Windows updates
Explorer. Same goes when removing/editing. The files, effectively, are
closed during the process in the sense that they can be open[ed] in
another process.

Any method that reads/writes from/to files effectively has to 'open'
them to perform its task. In this sense, the files are 'open' (in
memory) to the DSO process for its intented task. (As opposed to
physically opening the files in an app to read/write their
SummaryProperties)

You only need the code that reads/writes the SummaryProperties.
Examples of that are all there to use in your own procedure. Just sort
through it and pull what you need. My cnc file manager apps don't use
DSO to read because they read from the values embeded in the file
contents. They do, however, write back to the file as well as the
SummaryProperties if the file is stored on a NTFS volume. Essentially,
read/write is left/right oriented so...

gsMyTitle = oSummProps.Title
oSummProps.Title = gsMyTitle
..where gsMyTitle is a global variable

How I use DSO is my apps read/write from/to global variables and use a
function (code follows) to read/write from/to the files. The function
is constructed to write by default since, as I mentioned, I don't use
DSO to read these values.

Code: (watch the word wrap)
=====================================
Function bGetSet_SummaryProps(ByVal Filename As String, Optional
bReadMode As Boolean = False) As Boolean
' The property values used here are global variables set by another
procedure.
' This reads to these variables and writes from them.

Dim m_oDocumentProps As DSOFile.OleDocumentProperties
Dim oSummProps As DSOFile.SummaryProperties
Dim fOpenReadOnly As Boolean

On Error Resume Next
Set m_oDocumentProps = Nothing: Set oSummProps = Nothing
Err.Clear: On Error GoTo ErrExit

Set m_oDocumentProps = New DSOFile.OleDocumentProperties
m_oDocumentProps.Open Filename, fOpenReadOnly,
dsoOptionOpenReadOnlyIfNoWriteAccess

Set oSummProps = m_oDocumentProps.SummaryProperties
With oSummProps
If bReadMode Then 'read from
gsSP_Title = .Title: gsSP_Subject = .Subject: gsSP_Author =
..Author: gsSP_Category = .Category: gsSP_Keywords = .Keywords:
gsSP_Comment = .Comment
Else 'write to
.Title = gsSP_Title: .Subject = gsSP_Subject: .Author =
gsSP_Author: .Category = gsSP_Category: .Keywords = gsSP_Keywords:
..Comment = gsSP_Comment
End If
End With

ErrExit:
bGetSet_SummaryProps = (Err = 0)
'Cleanup...
m_oDocumentProps.Save: m_oDocumentProps.Close
Set m_oDocumentProps = Nothing: Set oSummProps = Nothing
End Function 'bGetSet_SummaryProps()
=====================================

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Changing File "Summary" Properties Programmatically
 

Oops!
The correct syntax for accessing the Comments prop should be plural
(oSummProps.Comments)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Changing File "Summary" Properties Programmatically
 

Gary,

Works perfect! Love the function.
Thank you VERY much.

Changing File "Summary" Properties Programmatically
 

hutteto submitted this idea :
Gary,

Works perfect! Love the function.
Thank you VERY much.

You're welcome! Glad to help...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc