Excel VBA: Special/non-printing Characters in String

Question

Hi Folks.....Working with an Excel VBA project to extract information from the EXIF metadata for a series of photos.  I thought all was working correctly until I found one of three columns containing dates was not being interpreted as a date/number. 

• ActiveCell.Offset(0, 2).Value = GetFileProperties(Files(I), 1, "Date modified") where GetFileProperties function was found here http://www.eggheadcafe.com/microsoft/Access-Modules-DAO/32655843/photo-metadata.aspx

Upon investigating I found that MSGBOX output for that value contained periodic ? characters.

• 

However, in the Excel sheet that cell displays without the ?'s, but if one checks the length it returns 21 which is correct for including the ?'s.  And, it refuses to treat the cell as a DATE.

I've parsed the string in VBA looking at all 21 characters and it returns  63 which is ascii for a ?.  However when I try to use the replace function to change them to something else or remove them, it seems to have no effect.

I've also tried the CLEAN function without success.

Any ideas as to what is going on and how I can fix it?

Thanks...RDK2

Answer 1

The following strips out the characters. strTemp is the string returned by the function and strFinal is the finished data. It allows for both date and time, both of which should be in the Meta Data.

  For i = 1 To Len(strTemp)
    If Mid(strTemp, i, 1) Like "[A,P,M,/, ,0-9,:]" Then 'Note Includes a space between 2 commas following the slash.
      strFinal = strFinal & Mid(strTemp, i, 1)
    End If
  Next i

 

 

---

Regards, OssieMac

Answer 2

Thanks OssieMac...Your code worked. 

The following code also works

 

 temp = GetFileProperties(Files(I), 1, "Date taken")
 temp2 = ""
 For J = 1 To Len(temp)
  If Asc(Mid(temp, J, 1)) <> 63 Then temp2 = temp2 & Mid(temp, J, 1)
  Next J

Interestingly, it only works with the ascii codes, if you replace the if with Mid(temp,j,1) <> "?" it makes no changes.

 

Anyway, thanks.....Rob

ps...would you know where i could find the specs for the EXIF metadata?

Answer 3

Interestingly, it only works with the ascii codes, if you replace the if with Mid(temp,j,1) <> "$" it makes no changes.

You have used a dollar sign in the above but ascii 63 is a question mark but maybe a typo. The reason you can't use a question mark is because it is a wild card that represents a single character so all characters match. Changing it to the ascii code is simply matching numbers and that works.

ps...would you know where i could find the specs for the EXIF metadata? Not sure what you want here. The code below between the asterisk lines will list all of the meta data. Use a new workbook and output will be on Sheet1.

You will need to set the reference in VBA for it to work. In the VBA editor, Select Menu Item Tools -> References and scroll down and check the box against Microsoft Shell Controls and Automation (Ensure you check the box; not just highlight the line)

'******************************************************************
Sub ListMetadata()
               
    Dim fileFolder As String
    Dim fileName As String
    Dim objShell As Shell32.Shell
    Dim objFolder As Shell32.Folder
    Dim objItem As Shell32.FolderItem
    Dim i As Long
   
    'Edit following line to your path. (Note NO back slash on end.)
    fileFolder = ThisWorkbook.Path & "\Picture Test"
   
    'Can insert path like following if you prefer.
    'fileFolder = "C:\Users\OssieMac\Documents\Excel\Test Macros\Picture Test"
   
    'Edit following line to your file name
    fileName = "DSC00093.JPG"

    Set objShell = New Shell
    Set objFolder = objShell.Namespace(fileFolder)
    Set objItem = objFolder.ParseName(fileName)
   
    With objFolder
      For i = 1 To 1000
        Sheets("Sheet1").Cells(i, "A") = .GetDetailsOf(objItem.Name, i)
        Sheets("Sheet1").Cells(i, "B") = .GetDetailsOf(objItem, i)
      Next i
    End With
   
    Set objItem = Nothing
    Set objFolder = Nothing
    Set objShell = Nothing

End Sub
'**********************************************************

 

The following code between the hash lines is a function to extract value of specific metadata.

'################################################################
Function GetMetaProp(fileFolder, fileName, lngItem)

    Dim objShell As Shell32.Shell
    Dim objFolder As Shell32.Folder
    Dim objItem As Shell32.FolderItem

    Set objShell = New Shell
    Set objFolder = objShell.Namespace(fileFolder)
    Set objItem = objFolder.ParseName(fileName)
   
    GetMetaProp = objFolder.GetDetailsOf(objItem, lngItem)
   
End Function
'################################################################

The following code between the dollar signs is the test for the function. Use the row number from the output on sheet1 as the value for lngMetaProp

'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Sub testFunction()
  Dim strPath As String
  Dim strFile As String
  Dim lngMetaProp As Long
  Dim strTemp As String
  Dim i
 
  strPath = ThisWorkbook.Path & "\Picture Test" 'Edit to your path
 
  strFile = "DSC00093.JPG"    'Edit to your filename
 
  lngMetaProp = 12
 
  strTemp = GetMetaProp(strPath, strFile, lngMetaProp)
 
  MsgBox strTemp
End Sub
'$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

---

Regards, OssieMac

Answer 4

OssieMac....Thanks for the code.

I think this "?" issue is a "feature" of Window 7.  I just ran another macro which I had regularly used on Windows XP and it failed getting the photo dimensions.  Similar problem, a "?" before the numbers which "irritated" VB when if tried to treat it as an integer.

I've now added the new code to my function for retrieving EXIF property values.....RDK2

Answer 5

I think this "?" issue is a "feature" of Window 7.  I just ran another macro which I had regularly used on Windows XP and it failed getting the photo dimensions. 

The question mark character is also in Vista.

I tested the code to extract and list all of the metadata on a computer with Win XP. It only displays a total of 40 items but in Vista it displays 278 items and the sequence of items is not the same.

The dimensions is item 26 in Win XP and item 31 in Vista.

I actually thought that I might have lost some of the metadata by copying the file to Win XP so I copied it back to Vista again and all of the metadata is still there. It is just not read in Win XP.

Anyway I am happy for you that you have it working and that is the main thing.

---

Regards, OssieMac

Answer 6

Great code Thanks... I re-wrote to work with MS Access, the return of the function is one long string with each of the *populated* meta lines (it skips blanks, remove the vbCRLF if you'd like) that I'm then dropping into a Memo field in a table.  I can already see room for improvement, but I have to get this project done.

 

Public Function ListMetadata(FileFolder As String, Filename As String) As String

    'Reqires the following Referance Library

    'Microsoft Shell Controls and Automation

 

    Dim objShell As Shell32.Shell

    Dim objFolder As Shell32.Folder

    Dim objItem As Shell32.FolderItem

    Dim i As Long

 

    Set objShell = New Shell

    Set objFolder = objShell.Namespace(FileFolder)

    Set objItem = objFolder.ParseName(Filename)

 

    ListMetadata = ""

 

    With objFolder

      For i = 1 To 500

      If Len(.GetDetailsOf(objItem, i)) > 1 Then

        ListMetadata = ListMetadata & i & " - " & .GetDetailsOf(objItem, i) & vbCrLf

      End If

      Next i

    End With

 

    Set objItem = Nothing

    Set objFolder = Nothing

    Set objShell = Nothing

 

End Function