Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
9,797
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I was spurred to explore this subject further after this recent thread

As far as I can see, there seems to be no easy way to edit file properties programmatically without needing to resort to external components such as CDDBControl.dll or CDDBControlRoxio.dll (which by the way these two dlls don't seem to work in x64 anyway, plus they are a nightmare to install & register).There may be other 3rd party libraries that could be used but it would be great if we could read and edit file properties hassle-free and with vba alone.

So, to this end, I have been experimenting with relevant interfaces (PropertyStore, IPropertyDescription, IPropertyDescriptionList, IPropertySystem) and I seem to have come up with some pretty good results when testing in x32 and x64... I just hope it works consistently accross diff systems.

File Demo:
FileMetadataProperties.xlsm





This vba project code offers esay to use functions for reading and editing properties of all kind of files (not just media MP3s)
VBA Code:
Public Function GetPropertyValue( _
    ByVal sPathFile As String, _
    ByVal Prop As Property_Name _
) As String

And
VBA Code:
Public Function SetPropertyValue( _
    ByVal sPathFile As String, _
    ByVal Prop As Property_Name, _
    ByVal Property_NewValue As Variant _
) As Boolean

VBA Code:
Public Function PropertyDump() As String()

VBA Code:
Public Function GetAllFileProperties(sPathFile As String) As String()

Useful headers for reference:
propsys.h
propKey.h

The code is quite long so I will be splitting it into two sections and post them in the next two following posts.
 
I tried to modify the attributes of a PNG file. Only the date taken attribute was set. I honestly have no idea if that's by design, but it does set the attributes when it's a JPG.

I also have no idea of where you get all of this boilerplate code to do these low level things, mind giving me a few pointers? hehe
A feature of the JPG file format is the ability to have EXIF metadata. PNG files have 'some of the same metadata fields you can change (I think the Comment field, might be one for example), but nothing near on the same scale as JPGs.
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
It's hardly boilerplate - Jaafar wrote it!
I guess my wording was shamefully wrong. My bad.

@Jaafar Tribak I didn't mean you copied the code, I know you engineered it, it's why I'm always following your posts, I just chose my words wrong.

What I meant by "boilerplate code" is: how do you determine which pieces of code are necessary for everything to function properly? As I understand it, if you want to change a property, you would first locate a built-in function that modifies that property, then analyze its parameters, and finally figure out how to set those parameters correctly by referencing other functions and do the whole thing again to set its parameters. That would be a strategy I could grasp. However, if you're moving pieces of memory and arranging them in a way that the OS understands, that seems much more complex and low-level, but also very interesting.

For instance, I randomly copied these lines:

VBA Code:
    Case ContentType°
    PropNameToPropKey = DEFINE_PROPERTYKEY(&HD5CDD502, &H2E9C, &H101B, &H93, &H97, &H8, &H0, &H2B, &H2C, &HF9, &HAE, 26)

Code:
    Select Case DisplyName
        Case AcquisitionID°: DisplayNameToCanonicalName = "System.AcquisitionID"
        Case Attributes°: DisplayNameToCanonicalName = "System.FileAttributes"

So, how do you know you have to include those arguments in the DEFINE_PROPERTYKEY function, in particular, where do you get that you need &H93, for example, in the 4th argument. Or how do you come up with "System.AcquisitionID" as the value for DisplayNameToCanonicalName when DisplyName is AcquisitionID°? Also, where do you draw a line between the things you can "name" and the things the OS demands you to use as "name".

How or where do you get that information in general?

I hope what I wrote makes sense.
 
Last edited:
Upvote 0
A feature of the JPG file format is the ability to have EXIF metadata. PNG files have 'some of the same metadata fields you can change (I think the Comment field, might be one for example), but nothing near on the same scale as JPGs.
Then I understand now that this is by design, being how the JPG file format operates. I was trying to figure out if you could enforce attributes in a file with this method.

I appreciate the clarification.
 
Upvote 0
@Edgar_
So, how do you know you have to include those arguments in the DEFINE_PROPERTYKEY function, in particular, where do you get that you need &H93, for example, in the 4th argument.
PKEY values are defined in the header file: Propkey.h.

The DEFINE_PROPERTYKEY function takes 2 arguments: (a guid and a pid) which are both required for building\returning a PROPERTYKEY structure. This structure will be later on needed by the IPropertyStore Interface in order to read and write file attributes\metadata.

So in the case of the ContentType attribute you asked about, I first built the propkey using the DEFINE_PROPERTYKEY function ... I obtained the required guid and pid from the above mentioned PropKey header file where all the propkey values are defined. This is the one for ContentType propkey\attribute:
DEFINE_PROPERTYKEY(PKEY_ContentType, 0xd5cdd502,0x2e9c,0x101b,0x93,0x97,0x08,0x00,0x2b,0x2c,0xf9,0xae,26);

Once I have the correct PropKey and the correct file pathname, I can then read and write the file extended attributes\metadata\props via the IPropertyStore and IPropertyDescription Interfaces using low level virtual table stdcalls with the help of the handy DispCallFunc api that is exported by the OLEAUT32 dll.

Similarly with the "System.AcquisitionID" and "System.FileAttributes", I used the DisplayNameToCanonicalName function to get the attributes canonical names whose definitions are documented here or here.

So, as you can probably see by now, it is not magic. It is all documented. One just needs to find & follow the relevant documentation, although it's not that easy to find and not that easy to understand when first reading it and it can sometimes be tricky when trying to put the pieces together I must admit.

I tried to modify the attributes of a PNG file. Only the date taken attribute was set. I honestly have no idea if that's by design, but it does set the attributes when it's a JPG.
Like Dan_W said, not all Windows codecs support property writing ... For example, IPropertyStore::SetValue returns the error: WINCODEC_ERR_PROPERTYNOTSUPPORTED 0x88982F41 for PNG images when trying to edit their DateCreated property ... I guess, restricted user permissions can also prevent editing file attributes\props but I haven't fully explored that bit.

I was trying to figure out if you could enforce attributes in a file with this method.
Don't know how to do that wit code. Can you try Attribute Changer and see if it can?

Regards.
 
Last edited:
Upvote 0
So, as you can probably see by now, it is not magic. It is all documented. One just needs to find & follow the relevant documentation, although it's not that easy to find and not that easy to understand when first reading it and it can sometimes be tricky when trying to put the pieces together I must admit.
I see. I will investigate further to see if there is some way to reverse engineer it, for now I'll try to understand your code to see if I can make some changes.

Don't know how to do that wit code. Can you try Attribute Changer and see if it can?
I've been using version 9 of AC for a few years, it has done wonders in the past for me. In the version I have installed, I can not set those JPG attributes in a PNG file.

Thanks for the explanations, again. It is all very interesting.
 
Upvote 0
Jaafar, your code is great and I use it a lot.
I would like to add information to my photos, where the photo was taken. I see Windows Explorer have metadata "City" and "Location".
Is it possible to add them?
 
Upvote 0
Jaafar, your code is great and I use it a lot.
I would like to add information to my photos, where the photo was taken. I see Windows Explorer have metadata "City" and "Location".
Is it possible to add them?
I may be wrong, but it depends on the image format. JPEG images have the capacity to store EXIF Metadata, which includes a huge list of potential fields - I'm not sure about City or Location, but GPS coordinates are certainly stored in JPEG files. PNG and TIF also have some capacity to store EXIF Metadata, but it definitely doesn't compare to JPEGs. The full list of available metadata is available at: EXIF Tags

I have written some routines that allow VBA users to read and write these metadata properties: JustMoreVBA/Images/modImages_EXIF_WIA.bas at main · KallunWillock/JustMoreVBA
The following is a shorter version of the above, just to demonstrate.

Hope that helps.
USAGE:
VBA Code:
             WriteEXIFData Filename, PropertyName, PropertyValue, (Opt) WriteOverOriginal = True, (Opt) CreateBackup                     
             WriteEXIFData "C:\Temp\IMG01.jpg", EXIFImageTitle, "New Image Title"                                                 
             WriteEXIFData "C:\Temp\IMG01.jpg", EXIFImageTitle, "Live Life On The Edge", True                                    
                                                                                                                                                                                                                           
             Comments = GetEXIFData("C:\Temp\IMG_20210508_170154.jpg", EXIFImageComments)

Place the following code in a module:

VBA Code:
  Option Explicit
   Public Enum PropertyNameEnum
       EXIFImageDateTimeOriginal = 36867
       EXIFImageTitle = 40091
       EXIFImageComments = 40092
       EXIFImageAuthor = 40093
       EXIFImageKeywords = 40094
       EXIFImageSubject = 40095
       GPSVer = 0                                        ' Version of the Global Positioning Systems (GPS) IFD, given as 2.0.0.0. This tag is mandatory when the GpsIFD tag is present. When the version is 2.0.0.0, the tag value is 0x02000000.
       GPSLatitudeRef = 1                                ' Null-terminated character string that specifies whether the latitude is north or south.: N: specifies north latitude, and: S: specifies south latitude.
       GPSLatitude = 2                                   ' Latitude. Latitude is expressed as three rational values giving the degrees, minutes, and seconds respectively. When degrees, minutes, and seconds are expressed, the format is dd/1, mm/1, ss/1. When degrees and minutes are used and, for example, fractions of minutes are given up to two decimal places, the format is dd/1, mmmm/100, 0/1.
       GPSLongitudeRef = 3                               ' Null-terminated character string that specifies whether the longitude is east or west longitude.: E: specifies east longitude, and: W: specifies west longitude.
       GPSLongitude = 4                                  ' Longitude. Longitude is expressed as three rational values giving the degrees, minutes, and seconds respectively. When degrees, minutes and seconds are expressed, the format is ddd/1, mm/1, ss/1. When degrees and minutes are used and, for example, fractions of minutes are given up to two decimal places, the format is ddd/1, mmmm/100, 0/1.
       GPSAltitudeRef = 5                                ' Reference altitude, in meters.
       GPSAltitude = 6                                   ' Altitude, in meters, based on the reference altitude specified by GpsAltitudeRef.
       GPSGPSTime = 7                                    ' Time as Coordinated Universal Time (UTC). The value is expressed as three rational numbers that give the hour, minute, and second.
       GPSGPSSatellites = 8                              ' Null-terminated character string that specifies the GPS satellites used for measurements. This tag can be used to specify the ID number, angle of elevation, azimuth, SNR, and other information about each satellite. The format is not specified. If the GPS receiver is incapable of taking measurements, the value of the tag must be set to: NULL.
       GPSGPSStatus = 9                                  ' Null-terminated character string that specifies the status of the GPS receiver when the image is recorded.: A: means measurement is in progress, and: V: means the measurement is Interoperability.
       GPSGPSMeasureMode = 10                            ' Null-terminated character string that specifies the GPS measurement mode.: 2: specifies 2-D measurement, and: 3: specifies 3-D measurement.
       GPSGPSDop = 11                                    ' GPS DOP (data degree of precision). An HDOP value is written during 2-D measurement, and a PDOP value is written during 3-D measurement.
       GPSSpeedRef = 12                                  ' Null-terminated character string that specifies the unit used to express the GPS receiver speed of movement.: K,: M, and: N: represent kilometers per hour, miles per hour, and knots respectively.
       GPSSpeed = 13                                     ' Speed of the GPS receiver movement.
       GPSTrackRef = 14                                  ' Null-terminated character string that specifies the reference for giving the direction of GPS receiver movement.: T: specifies true direction, and: M: specifies magnetic direction.
       GPSTrack = 15                                     ' Direction of GPS receiver movement. The range of values is from 0.00 to 359.99.
       GPSImgDirRef = 16                                 ' Null-terminated character string that specifies the reference for the direction of the image when it is captured.: T: specifies true direction, and: M: specifies magnetic direction.
       GPSImgDir = 17                                    ' Direction of the image when it was captured. The range of values is from 0.00 to 359.99.
       GPSMapDatum = 18                                  ' Null-terminated character string that specifies geodetic survey data used by the GPS receiver. If the survey data is restricted to Japan, the value of this tag is: TOKYO: or: WGS-84.
       GPSDestLatRef = 19                                ' Null-terminated character string that specifies whether the latitude of the destination point is north or south latitude.: N: specifies north latitude, and: S: specifies south latitude.
       GPSDestLat = 20                                   ' Latitude of the destination point. The latitude is expressed as three rational values giving the degrees, minutes, and seconds respectively. When degrees, minutes, and seconds are expressed, the format is dd/1, mm/1, ss/1. When degrees and minutes are used and, for example, fractions of minutes are given up to two decimal places, the format is dd/1, mmmm/100, 0/1.
       GPSDestLongRef = 21                               ' Null-terminated character string that specifies whether the longitude of the destination point is east or west longitude.: E: specifies east longitude, and: W: specifies west longitude.
       GPSDestLong = 22                                  ' Longitude of the destination point. The longitude is expressed as three rational values giving the degrees, minutes, and seconds respectively. When degrees, minutes, and seconds are expressed, the format is ddd/1, mm/1, ss/1. When degrees and minutes are used and, for example, fractions of minutes are given up to two decimal places, the format is ddd/1, mmmm/100, 0/1.
       GPSDestBearRef = 23                               ' Null-terminated character string that specifies the reference used for giving the bearing to the destination point.: T: specifies true direction, and: M: specifies magnetic direction.
       GPSDestBear = 24                                  ' Bearing to the destination point. The range of values is from 0.00 to 359.99.
       GPSDestDistRef = 25                               ' Null-terminated character string that specifies the unit used to express the distance to the destination point. K, M, and N represent kilometers, miles, and knots respectively.
       GPSDestDist = 26                                  ' Distance to the destination point.
       DocumentName = 269                                ' Null-terminated character string that specifies the name of the document from which the image was scanned.
       ImageDescription = 270                            ' Null-terminated character string that specifies the title of the image.
       EquipMake = 271                                   ' Null-terminated character string that specifies the manufacturer of the equipment used to record the image.
       EquipModel = 272                                  ' Null-terminated character string that specifies the model name or model number of the equipment used to record the image.
       StripOffsets = 273                                ' For each strip, the byte offset of that strip. See also: RowsPerStrip: and: StripBytesCount.
       Orientation = 274                                 ' Image orientation viewed in terms of rows and columns.
   End Enum
                                                         
   Private Enum WIAImagePropertyType
       UndefinedImagePropertyType = 1000
       ByteImagePropertyType = 1001
       StringImagePropertyType = 1002
       UnsignedIntegerImagePropertyType = 1003
       LongImagePropertyType = 1004
       UnsignedLongImagePropertyType = 1005
       RationalImagePropertyType = 1006
       UnsignedRationalImagePropertyType = 1007
       VectorOfUndefinedImagePropertyType = 1100
       VectorOfBytesImagePropertyType = 1101
       VectorOfUnsignedIntegersImagePropertyType = 1102
       VectorOfLongsImagePropertyType = 1103
       VectorOfUnsignedLongsImagePropertyType = 1104
       VectorOfRationalsImagePropertyType = 1105
       VectorOfUnsignedRationalsImagePropertyType = 1106
   End Enum
   
   Const TargetFileName = "C:\UseYourFilePath\Filename.jpg"
                                                         
   Sub Test_WriteProperties()
                                                         
       Dim NewFileName As String
       NewFileName = WriteEXIFData(TargetFileName, EXIFImageTitle, "Something Somewhere", True, True)
       WriteEXIFData NewFileName, EXIFImageAuthor, "Who took the picture?"
       WriteEXIFData NewFileName, EXIFImageSubject, "The Photo by Whoever"
       WriteEXIFData NewFileName, EXIFImageComments, "The Source: https://mrexcel.com/"
                                                         
       Debug.Print NewFileName
                                                         
   End Sub
                                                                                                  
   Public Function GetEXIFData(ByVal filename As String, ByVal PropertyName As PropertyNameEnum) As String
                                                         
       Dim Image               As Object
       Dim ImageProperty       As Object
       Dim Result              As String
                                                         
       Set Image = CreateObject("WIA.ImageFile")
       Image.LoadFile filename
                                                         
       For Each ImageProperty In Image.Properties
           If ImageProperty.PropertyID = PropertyName Then
               If TypeName(ImageProperty.Value) = "String" Then
                   Result = ImageProperty.Value
               Else
                   Result = Replace(StrConv(ImageProperty.Value.BinaryData, vbUnicode), Chr(0), "")
               End If
               Exit For
           End If
       Next
                                                         
       GetEXIFData = Result
                                                         
       Set Image = Nothing
       Set ImageProperty = Nothing
                                                         
   End Function
                                                         
   Public Function WriteEXIFData(ByVal filename As String, ByVal PropertyName As PropertyNameEnum, ByVal PropertyValue As Variant, Optional ByVal OverWriteOriginal As Boolean = True, Optional ByVal CreateBackup As Boolean)
                                                         
       Dim Image               As Object
       Dim ImageProcess        As Object
       Dim ImageVector         As Object
       Dim NewFileName         As String
                                                         
       If CreateBackup = True Then
           Dim BackUpFilename  As String
           BackUpFilename = Replace(filename, ".jpg", "_BACKUP(" & format(Now, "ddmmyyyy-hhnn") & ").jpg")
           FileCopy filename, BackUpFilename
       End If
                                                         
       Set Image = CreateObject("WIA.ImageFile")
       Set ImageProcess = CreateObject("WIA.ImageProcess")
       Set ImageVector = CreateObject("WIA.Vector")
                                                         
       Image.LoadFile filename
                                                         
       ImageProcess.Filters.Add ImageProcess.FilterInfos("Exif").FilterID
       ImageProcess.Filters(1).Properties("ID") = PropertyName
                                                         
       Select Case PropertyName
                                                         
           Case PropertyNameEnum.EXIFImageDateTimeOriginal
               Dim StringValue As String
               StringValue = format(PropertyValue, "YYYY:MM:DD HH:MM:SS")
               ImageProcess.Filters(1).Properties("Type") = StringImagePropertyType
               ImageProcess.Filters(1).Properties("Value") = StringValue
                                                         
           Case Else
               ImageProcess.Filters(1).Properties("Type") = VectorOfBytesImagePropertyType
               ImageVector.SetFromString PropertyValue
               ImageProcess.Filters(1).Properties("Value") = ImageVector
                                                         
       End Select
                                                         
       Set Image = ImageProcess.Apply(Image)
                                                         
       If OverWriteOriginal = True Then
           NewFileName = filename
           Kill filename
       Else
           NewFileName = Replace(filename, ".jpg", "_metadata.jpg")
           If Len(Dir(NewFileName)) > 0 Then Kill NewFileName
       End If
                                                         
       Image.SaveFile NewFileName
                                                         
       WriteEXIFData = NewFileName
                                                         
       Set Image = Nothing
       Set ImageProcess = Nothing
       Set ImageVector = Nothing
                                                         
   End Function
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top