I want to use VBA (Excel) to access and modify file properties for jpg
files (to be axact I want to record the people in my photos).

I've made a big Excel file with lots of information on my photos
(place, people's names etc) and I want to load this into the file
properties of the .jpg files.

Searching the web I found Microsoft's DSOfile and Chip Pearson's
(http://www.cpearson.com/excel/docprop.htm) helpful page on this. But
DSOfile can only read but not write properties for non MS-Office
files.

Do you know any way I can write file properties (subject, comments,
keywords etc) to a jpg file via Excel VBA.

If so, can you write me some VBA.  I don't need a finished piece of
code - I'm reasonnnably competent with VBA.  But I don't know how to
access these file properties.  I have no idea how to program in any
languages other than VBA for Excel.

Thanks for reading this.

Just a free comment:

I'd approach the task a bit differently: writing (or perhaps
felicitously, locating) a DLL written in a lower level language (eg. C
or C++) to do the file output, and then calling the "interface" such a
DLL provides from the Excel/VBA environment.

regards, mathtalk-ga

Perhaps you'd rather have a free database like the one here called MyPictureBook?
http://www.theofficeexperts.com/downloads.htm#AccessDownloads

Thanks mathtalk.

That sounds great - but what you are describing is beyond my ability. 
Are you able to help me to create a DLL?  Or can you tell me who can?

To dreamboat - thnks for the suggestion.  But I'd prefer something
more flexible - that's why I like messing around in Excel.

Thanks

From looking at the MS page on DSOFile it looks as if it should be
able to write document properties when you call the Save method. What
happens when you try this? (Sorry, I don't have the time to download
and test it myself at the moment.)

Hi Manuka

Thanks for commenting.  Th answer is that I get an error: "Permission Denied".

This is confirmed in this MS TechNet article
(http://www.microsoft.com/technet/community/columns/scripts/sg0305.mspx)
that says:

"One drawback to Dsofile is the fact that it can write to the summary
information fields only for a Microsoft Office document; a script that
tries to assign a title to, say, a text file (a .txt file) will fail.
One of the sad facts of life."

Just to confirm this, the following code:

Sub pp()
Dim FileName As String
Dim DSO As DSOFile.OleDocumentProperties
Set DSO = New DSOFile.OleDocumentProperties
'FileName = "book2.xls"
FileName = "picture 096.jpg"
DSO.Open sfilename:=FileName
Debug.Print DSO.SummaryProperties.ApplicationName
Debug.Print DSO.SummaryProperties.Author
Debug.Print DSO.SummaryProperties.Comments
Debug.Print DSO.SummaryProperties.Subject
DSO.SummaryProperties.Comments = "my new test!!!!"   '***
Debug.Print DSO.SummaryProperties.Comments
DSO.Save
DSO.Close
End Sub

works perfectly with the .xls file but gives the error with the .jpg
file at the line marked ***.


Thanks for looking and for any new ideas.  Is there not some sort of
API call that can do what I want (I am outside my comfort zone here,
I've only used an API call once, and that was cut ans pasted from
somebody else).

Regards

I wonder if setting properties for .jpg files involves a hidden
dependence on the Windows NTFS file system.  MS Office documents are
what are known as "compound files", which allows for their properties
to be stored within them.

For "simple" files, e.g. text files, NTFS presents an alternative way
of storing properties for the files in a separate structure.  See here
for the Microsoft documentation of the interface:

[IPropertySetStorage-NTFS File System Implementation]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/stg/stg/ipropertysetstorage_ntfs_file_system_implementation.asp

I believe that the .jpg format is not a compound file, like .xls or
.doc Office document files are, and that a solution will need to
exploit the "interface" API described above.

regards, mathtalk-ga

There is no room in a .jpg for comments, like mp3 or Mpeg. You can
only change the attributes. Conversely you could write a small script
to go through the names of the jpg's and create a photo1.txt for
photo1.jpg

kegtapper said:
"There is no room in a .jpg for comments, like mp3 or Mpeg. You can
only change the attributes. Conversely you could write a small script
to go through the names of the jpg's and create a photo1.txt for
photo1.jpg"

I understand your point about there being no room in the file, yet
Windows (XP) enables you to create comments, titles etc for a file
(you just click on file attributes).  I don't know where these data
get stored, but they are useful because the Search facilty can look at
them and pick up a list of photos which is just what I want (eg Auntie
Gladys; in the bath; wearing a scarf... you get the idea).

So unless I'm missing something, if I can do it by right clicking, I
should be able to do it through VBA?

Thanks for stopping by

Just to report what I've found on the Web, there's a fairly recent
(Jan. '05) blog here by a Visual FoxPro developer:

[Calvin Hsia's WebLog: How to use a COM interface when there is no COM object?]
http://blogs.msdn.com/calvin_hsia/archive/2005/01/21/358418.aspx

which raises basically the same issue in the context of VFP rather
than Excel/VBA.  The discussion, especially in comments appended by
second parties, endorses the approach of writing a C++ wrapper to
manage the interface, but no working code or even snippets are shown.

There are a couple of older C++ projects I found which tackle this
task or something pretty close.  There's this one from Ales Krajnc:

[Structured Storage Class for ATL & MFC]
http://www.codeproject.com/atl/structuredstorage.asp

and this even older one from Glenn Swonk:

[Compound File Stream and Storage Manipulation]
http://www.developer.com/net/cplus/article.php/632321

Either would probably make an excellent starting point for a Visual
C++ developer creating a new DLL.  I suspect some updating will be
required to support the newer NTFS-based API for setting properties on
"simple" files, which I think came in with Windows 2000 but is
backward compatible with NTFS under Windows NT 4 with the latest
service pack.

It's an interesting project, but I'd only be able to work at it in
fits.  So if there's a pressing need, let me suggest another approach.
 The ability to access the Windows API is something a lot of non-MS
programming languages have to address.  So it's possible that in a
script-like language like Python or Haskell this bridge had already to
be crossed.  It would mean pouring through a lot of documentation to
ascertain whether the desired IStorage calls can be effected, but it
would a learning exercise more so than a programming task.

regards, mathtalk-ga

Try this.

I would just use the Windows functionality to change the properties.
This worked for me in WinXp.



Code:

'---------------------------------------------------------------------------------------
' Module    : basMain
' DateTime  : 02/09/2004 22:23
' Author    : Ivan F Moala (of http://www.xcelfiles.com/)
' Purpose   :
'---------------------------------------------------------------------------------------
Option Explicit

'//
Public Declare Function ShellExecute _
    Lib "shell32.dll" _
        Alias "ShellExecuteA" ( _
            ByVal Hwnd As Long, _
            ByVal lpOperation As String, _
            ByVal lpFile As String, _
            ByVal lpParameters As String, _
            ByVal lpDirectory As String, _
            ByVal nShowCmd As Long) _
As Long

'// Properties API
Private Type SHELLEXECUTEINFO
    cbSize       As Long
    fMask        As Long
    Hwnd         As Long
    lpVerb       As String
    lpFile       As String
    lpParameters As String
    lpDirectory  As String
    nShow        As Long
    hInstApp     As Long
    lpIDList     As Long
    lpClass      As String
    hkeyClass    As Long
    dwHotKey     As Long
    hIcon        As Long
    hProcess     As Long
End Type

Private Declare Function ShellExecuteEx _
    Lib "shell32.dll" ( _
        Prop As SHELLEXECUTEINFO) _
As Long

Public Function fnGetPropDlg(strFilepath As String) As Long
Dim Prop As SHELLEXECUTEINFO

With Prop
    .cbSize = Len(Prop)
    .fMask = &HC
    .Hwnd = 0&
    .lpVerb = "properties"
    .lpFile = strFilepath
End With

fnGetPropDlg = ShellExecuteEx(Prop)

End Function


called with



Code:

Private Sub CommandButton1_Click()
Dim vFile As Variant

vFile = Application.GetOpenFilename
If TypeName(vFile) = "Boolean" Then Exit Sub

fnGetPropDlg CStr(vFile)

End Sub

dreamboat

Thanks very much for posting.  

If I understand your code, what it does is pop up the file properties
dialog.  This enables me to manually enter file properties.  But what
I want to do is programitcally set the properties for several thousand
files - I'm tring to avoid manually setting each one.

Thanks again for trying to help.