Return if file exists VBA Mac Office 2011

Question

I'm trying to test if a file exists with VBA on Mac Office 2011.

My macro is working with Office 2004 but doesn't work with 2011.

I'm using the Dir function. If the function returns nothing, this means the file doesn't exist. But with Office 2011, the function returns an error code 76 when the file doesn't exist.

Answer 1

You can just create your own function that handles the error. For instance, something like this:

Function FileExists(ByVal AFileName As String) As Boolean
    On Error GoTo Catch

    FileSystem.FileLen AFileName

    FileExists = True

    GoTo Finally

    Catch:
        FileExists = False
    Finally:
End Function

Sub Test()
  If FileExists("Macintosh HD:Library:User Pictures:Flowers:Flower.tif") Then
    MsgBox "File exists"
  Else
    MsgBox "File doesn't exists"
  End If
End Sub

Answer 2

The solution in answer 1 should work. But will fail any time you encounter the truncated file name issue that plauges Mac Excel 2011 - even with SP1 applied.

The basic problem is described on Microsoft http://answers.microsoft.com/en-us/mac/forum/macoffice2011-macexcel/help-xl-2011s-dir-function-truncates-filename/e72fbf5d-749c-4a55-a77c-e2def6db24d9?msgId=644b9f20-251b-46fe-8df3-f5a28a1c37f6

and the FileSystem object has the same disease as using the VBA native Dir function.

In other words it returns the same truncated file name that Dir returns, consequently you can't actually determine if a file with a long name, the name that actually appears to the user in a listing in the Finder, exists using VBA behind Excel 2011 SP1, without resorting to AppleScripting!

Answer 3

Better answer is on msft website: http://msdn.microsoft.com/en-us/library/office/jj614412(v=office.14).aspx

For example:

Sub TestFile()
'First argument, 1 = file and 2 = folder.
'Note: This macro uses the FileOrFolderExistsOnMac function.
    If FileOrFolderExistsOnMac(1, "Macintosh HD:Users:<user name>:Documents:YourFileName.xlsx") = True Then
        MsgBox "File exists."
    Else
        MsgBox "File does not exist."
    End If
End Sub

Sub TestFolder()
'First argument, 1 = file and 2 = folder.
'Note: This macro uses the FileOrFolderExistsOnMac function.
    If FileOrFolderExistsOnMac(2, "Macintosh HD:Users:<user name>:Documents") = True Then
        MsgBox "Folder exists."
    Else
        MsgBox "Folder does not exist."
    End If
End Sub

Function FileOrFolderExistsOnMac(FileOrFolder As Long, FileOrFolderstr As String) As Boolean
'By Ron de Bruin
'30-July-2012
'Function to test whether a file or folder exist on a Mac.
'Uses AppleScript to avoid the problem with long file names.
    Dim ScriptToCheckFileFolder As String
    ScriptToCheckFileFolder = "tell application " & Chr(34) & "Finder" & Chr(34) & Chr(13)
    If FileOrFolder = 1 Then
        ScriptToCheckFileFolder = ScriptToCheckFileFolder & "exists file " & _
        Chr(34) & FileOrFolderstr & Chr(34) & Chr(13)
    Else
        ScriptToCheckFileFolder = ScriptToCheckFileFolder & "exists folder " & _
        Chr(34) & FileOrFolderstr & Chr(34) & Chr(13)
    End If
    ScriptToCheckFileFolder = ScriptToCheckFileFolder & "end tell" & Chr(13)
    FileOrFolderExistsOnMac = MacScript(ScriptToCheckFileFolder)
End Function