Return if Mac Office 2011 VBA file exists

Question

I am trying to check if a file exists with VBA in Mac Office 2011.

My macro works with Office 2004, but does not work since 2011.

I am using the Dir function. If the function returns nothing, it means that the file does not exist. But with Office 2011, the function returns error code 76 when the file does not exist.

Answer 1

You can simply create your own function that handles the error. For example, 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 you won’t succeed at any time when you encounter a truncated file name problem that Mac Excel 2011 handles — even when using SP1.

The main problem is described in 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 VAR-specific DAR function.

In other words, it returns the same truncated file name that Dir returns, therefore, you cannot determine if a file with a long name, which is actually displayed to the user in the listing in Finder, exists using VBA for Excel 2011 SP1, without resorting to AppleScripting!

Answer 3

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

For instance:

 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