none
Problem with Dir and files with a single quote in the name RRS feed

  • Question

  • I have a Class that recursively gets Folder names and all file names. I have decided to tidy up my music files so was using this Class to get file names into Excel. I found that when a file had a single quote in the filename the whole thing crashed. I use Dir to get the filenames but I tried using FileSystemObject, GettAttr etc. and they all claim not to be able to find the file. The filename is below.

    S:\Music\Artist\Dolly Parton\Ultimate\07 - Please Don't Stop Loving Me (Porter Wagoner & Dolly Parton).mp3

    Note the single quote in Don't. Dir finds the file, and returns a filename if I use *.* and Dir() to get all file names but the name returned by Dir is:

    07 - Please Don'?t Stop Loving Me (Porter Wagoner & Dolly Parton).mp3

    Note the question mark behind the quote. Try Dir with the actual filename, filename with question mark and it is not found. There are many files in my library like this. What have I missed and how do I get round this?

    Jeff

    Friday, December 21, 2012 1:44 AM

Answers

  • I had no problems with FileSystemObject: put this into a regular module in a workbook and store the workbook in the folder S:\Music\Artist\Dolly Parton\Ultimate\

    Sub FindFilesWithSingleQuotes()
        Dim objFSO As Scripting.FileSystemObject
        Dim objFolder As Scripting.Folder
        Dim objFile As Scripting.File

        Set objFSO = CreateObject("Scripting.FileSystemObject")

        Set objFolder = objFSO.GetFolder(ThisWorkbook.Path)
        For Each objFile In objFolder.Files
            If InStr(1, objFile.Name, "'") > 0 Then
                MsgBox objFile.Name & " has an apostrophe"
            End If
        Next objFile

    End Sub

    If it shows the '?, that may be because your mp3 are being handled by iTunes, a notoriously stupid program that may use mac characters in file names. Try running this version, with the function beneath

    Sub FindFileNamesWithMacCharacters()
        Dim objFSO As Scripting.FileSystemObject
        Dim objFolder As Scripting.Folder
        Dim objFile As Scripting.File

        Set objFSO = CreateObject("Scripting.FileSystemObject")

        Set objFolder = objFSO.GetFolder(ThisWorkbook.Path)
        For Each objFile In objFolder.Files
            If InStr(1, objFile.Name, "'") > 0 Then
                If objFile.Name <> NoMacCharacters(objFile.Name) Then
                MsgBox objFile.Name & " has Mac characters"
                End If
            End If
        Next objFile
        MsgBox "All done"

    End Sub

    Function NoMacCharacters(strOrig As String) As String
        Dim c(1 To 2, 1 To 6) As Integer
        Dim i As Integer

        c(1, 1) = 146
        c(2, 1) = 39

        c(1, 2) = 151
        c(2, 2) = 45

        c(1, 3) = 147
        c(2, 3) = 34

        c(1, 4) = 10
        c(2, 4) = 32

        c(1, 5) = 148
        c(2, 5) = 34

        c(1, 6) = 148
        c(2, 6) = 34
       
        NoMacCharacters = strOrig
        For i = 1 To UBound(c, 2)
            NoMacCharacters = Replace(NoMacCharacters, Chr(c(1, i)), Chr(c(2, i)))
        Next i

    End Function


    Friday, December 21, 2012 4:38 PM

All replies

  • I had no problems with FileSystemObject: put this into a regular module in a workbook and store the workbook in the folder S:\Music\Artist\Dolly Parton\Ultimate\

    Sub FindFilesWithSingleQuotes()
        Dim objFSO As Scripting.FileSystemObject
        Dim objFolder As Scripting.Folder
        Dim objFile As Scripting.File

        Set objFSO = CreateObject("Scripting.FileSystemObject")

        Set objFolder = objFSO.GetFolder(ThisWorkbook.Path)
        For Each objFile In objFolder.Files
            If InStr(1, objFile.Name, "'") > 0 Then
                MsgBox objFile.Name & " has an apostrophe"
            End If
        Next objFile

    End Sub

    If it shows the '?, that may be because your mp3 are being handled by iTunes, a notoriously stupid program that may use mac characters in file names. Try running this version, with the function beneath

    Sub FindFileNamesWithMacCharacters()
        Dim objFSO As Scripting.FileSystemObject
        Dim objFolder As Scripting.Folder
        Dim objFile As Scripting.File

        Set objFSO = CreateObject("Scripting.FileSystemObject")

        Set objFolder = objFSO.GetFolder(ThisWorkbook.Path)
        For Each objFile In objFolder.Files
            If InStr(1, objFile.Name, "'") > 0 Then
                If objFile.Name <> NoMacCharacters(objFile.Name) Then
                MsgBox objFile.Name & " has Mac characters"
                End If
            End If
        Next objFile
        MsgBox "All done"

    End Sub

    Function NoMacCharacters(strOrig As String) As String
        Dim c(1 To 2, 1 To 6) As Integer
        Dim i As Integer

        c(1, 1) = 146
        c(2, 1) = 39

        c(1, 2) = 151
        c(2, 2) = 45

        c(1, 3) = 147
        c(2, 3) = 34

        c(1, 4) = 10
        c(2, 4) = 32

        c(1, 5) = 148
        c(2, 5) = 34

        c(1, 6) = 148
        c(2, 6) = 34
       
        NoMacCharacters = strOrig
        For i = 1 To UBound(c, 2)
            NoMacCharacters = Replace(NoMacCharacters, Chr(c(1, i)), Chr(c(2, i)))
        Next i

    End Function


    Friday, December 21, 2012 4:38 PM
  • Bernie

    Thank you for the idea. I had looked at using FileSystemObject but ran into the same problem.

    I did look at your other suggestion re the strange characters and although I wouldn't touch iTunes with a bargepole the strange characters must have been there even though unseen. I retyped the name and that solved the problem. If I find any more file names with this issue I will run them through your remove mac characters function. The ? returned from Dir must have been the unrecognised character.

    Weird!!

    Thank you

    Jeff

    Saturday, December 22, 2012 4:21 PM