How to obtain EXIF info from picture in an excel worksheet using VBA

Question

How do I obtain the EXIF info like Latitude, Longitude, etc... from a shape picture in an excel worksheet using VBA? And can it be obtained without extracting or copying the picture out of the worksheet or out of the zipped Excel workbook?

I am able to iterate through the zipped folder contents with the following code.

Public Sub ReadZip()
    Dim vZipFileName As Variant
    vZipFileName = "C:\Users\erogrou\Desktop\Test.zip"

    Dim objShell, objFolder
    Set objShell = CreateObject("shell.application")
    Set objFolder = objShell.Namespace(vZipFileName)

    Dim vFilename As Variant

    If (Not objFolder Is Nothing) Then
        Debug.Print objFolder.self.Path

        For Each vFilename In objFolder.items
            Debug.Print vFilename
        Next

    End If

    Set objFolder = Nothing
    Set objShell = Nothing

End Sub

Answer 1

Fortunately for you there is a beautiful piece of coding by Andrew Phillips from Australia's North Central Catchment Management Authority that does exactly this.

It is provided as a class module so I haven't looked to re-list this here

The current code can be imported from Access to Excel, in my case I needed to change the functions from 32 to 64 bit as so:

Private Declare Function VirtualAlloc Lib "kernel32" (ByVal Address As Long, ByVal Size As Long, ByVal AllocationType As Long, ByVal Protect As Long) As Long

to this using PntSafe

Private Declare PtrSafe Function VirtualAlloc Lib "kernel32" (ByVal Address As Long, ByVal Size As Long, ByVal AllocationType As Long, ByVal Protect As Long) As Long

Answer 2

The rendered pictures in Excel are typically embedded inside an enhanced metafile (EMF). You'd need to copy the metafile to the clipboard as an EMF, then enumerate the records of the EMF and extract the JPG, then parse the EXIF data from the JPG information.

Alternatively, if the Excel file is saved and is in XLSX/XLSM format, you can extract the JPG from the compressed XLSX/XLSM container and then parse that image for the EXIF data.

Neither approach is trivial to implement, and in both cases, it would be easier with .NET.