none
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. 

    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

    Thursday, August 25, 2011 5:30 AM

Answers

  • 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
    • Marked as answer by RDK2 Thursday, August 25, 2011 2:19 PM
    Thursday, August 25, 2011 11:50 AM

All replies

  • 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
    • Marked as answer by RDK2 Thursday, August 25, 2011 2:19 PM
    Thursday, August 25, 2011 11:50 AM
  • 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?



    • Edited by RDK2 Friday, August 26, 2011 4:50 AM Corrected $ to ?
    Thursday, August 25, 2011 2:25 PM
  • 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

    Thursday, August 25, 2011 8:11 PM
  • 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

    Saturday, August 27, 2011 9:18 AM
  • 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
    Saturday, August 27, 2011 9:54 PM
  • 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

     

     

    Sunday, January 08, 2012 2:36 PM
  • I do it with this code

     For J = 1 To Len(my_string)
     a = Mid(my_string, J, 1)
      Select Case Mid(my_string, J, 1)

      Case 0 To 9
          temp2 = temp2 & Mid(my_string, J, 1)
      Case " "
            temp2 = temp2 & Mid(my_string, J, 1)
      Case "."
            temp2 = temp2 & Mid(my_string, J, 1)
      Case ":"
            temp2 = temp2 & Mid(my_string, J, 1)
      Case Else
      End Select
     Next J

      
    Sunday, March 16, 2014 1:51 PM