Test if file name exist problems

Question

Hi all

Below are two ways to test if a file exist and both stop working if the filename is more then 27 characters

What is working correct on on Excel 2011

Sub Test_File_Exist_With_Dir()

    Dim FilePath As String

    Dim TestStr As String

    'After 27 file name charaters testing if the file exist is not working anymore

    FilePath = "Ron:Users:rondebruin:Documents:123451234512345123451234512.xlsm"

    TestStr = ""

    On Error Resume Next

    TestStr = Dir(FilePath)

    On Error GoTo 0

    If TestStr = "" Then

        MsgBox "File doesn't exist"

    Else

        MsgBox "File exist"

    End If

End Sub

FSO is not working on a Mac but you can use the function below from Ken Puls that

is working on a Mac and on a Windows machine.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=559

This is the Function :

Function FileOrDirExists(PathName As String) As Boolean 'Macro Purpose: Function returns TRUE if the specified file ' or folder exists, false if not. 'PathName : Supports Windows mapped drives or UNC ' : Supports Macintosh paths 'File usage : Provide full file path and extension 'Folder usage : Provide full folder path ' Accepts with/without trailing "" (Windows) ' Accepts with/without trailing ":" (Macintosh)

Dim iTemp As Integer

'Ignore errors to allow for error evaluation
On Error Resume Next
iTemp = GetAttr(PathName)

'Check if error exists and set response appropriately
Select Case Err.Number
Case Is = 0
    FileOrDirExists = True
Case Else
    FileOrDirExists = False
End Select

'Resume error checking
On Error GoTo 0

End Function

You use it like this in your VBA code :

Sub TestItWithWindowsOrMac() 'Macro Purpose: To test the FileOrDirExists function

Dim sPath As String

'Change your directory here
sPath = "HardDriveName:Documents:Test.doc"

'In Windows use a path like this
' sPath = "C:\Test.xls"

'Test if directory or file exists
If FileOrDirExists(sPath) Then
    MsgBox sPath & " exists!"
Else
    MsgBox sPath & " does not exist."
End If

End Sub

Answer 1

Hi Bob and other Mac experts

I came up with this applescript that I call from VBA, I add it to this page

http://www.rondebruin.nl/mac.htm#exists

Or call AppleScript with VBA, no problems with the max of 27 file name characters.

It is slower but it is working correct as far as I know.

Sub CeckIfFileExistsOnMac()

'Call AppleScript to test if file exists, this example have no problem

'with long file names like the other examples(max of 27 characters)

Dim Filestr As String

Dim scriptToRun As String

Dim Result As Boolean

Filestr = "Ron:Users:rondebruin:Documents:yourshortorlongfilenamehere.xlsm"

scriptToRun = scriptToRun & "tell application " & Chr(34) & "Finder" & Chr(34) & Chr(13)

scriptToRun = scriptToRun & "exists file " & Chr(34) & Filestr & Chr(34) & Chr(13)

scriptToRun = scriptToRun & "end tell" & Chr(13)

Result = MacScript(scriptToRun)

MsgBox Result

End Sub

Is this correct or not, I am new to this stuff so please tell me

Answer 2

Hi Bob

Thanks for your reply

I add it to my problem page, when i am ready I think it will be a big page (many problems)

http://www.rondebruin.nl/mac.htm

Talking about this will maybe give us a good workeround

Thanks

Answer 3

Ron, Based on this and your other posts it looks like there is in deed several bugs at play (or maybe one with several symptoms). If noe of these work, I can not think of another way to do it in a foolproof manner on the Mac. All I can say is that the Mac development team is aware of the problems. But, i do not know if it will ever be fixed. some manifestation of these bugs has lasted through several versions of Mac Excel, so I am not holding my breath for a fix.

Answer 4

This is the second one (easier to read<g>

Function FileOrDirExists(PathName As String) As Boolean

'Macro Purpose: Function returns TRUE if the specified file

'               or folder exists, false if not.

'PathName     : Supports Windows mapped drives or UNC

'             : Supports Macintosh paths

'File usage   : Provide full file path and extension

'Folder usage : Provide full folder path

'               Accepts with/without trailing "" (Windows)

'               Accepts with/without trailing ":" (Macintosh)

    Dim iTemp As Integer

    'Ignore errors to allow for error evaluation

    On Error Resume Next

    iTemp = GetAttr(PathName)

    'Check if error exists and set response appropriately

    Select Case Err.Number

    Case Is = 0

        FileOrDirExists = True

    Case Else

        FileOrDirExists = False

    End Select

    'Resume error checking

    On Error GoTo 0

End Function

 You use it like this in your VBA code :

Sub TestItWithWindowsOrMac()

'Macro Purpose: To test the FileOrDirExists function

    Dim sPath As String

    'Change your directory here

    sPath = "HardDriveName:Documents:Test.doc"

    'In Windows use a path like this

    ' sPath = "C:\Test.xls"

    'Test if directory or file exists

    If FileOrDirExists(sPath) Then

        MsgBox sPath & " exists!"

    Else

        MsgBox sPath & " does not exist."

    End If

End Sub