Return if file exists VBA Mac Office 2011
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 wit开发者_开发技巧h Office 2011, the function returns an error code 76 when the file doesn't exist.
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
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!
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
精彩评论