Check Existing File Failed

[seageath]

Hi ex(cel)perts.

I got the following code to check existing file to be opened.
But failed for one of the file on mapping drive
For example I want to check S:\Files\zTB Group.xls, it failed.
But on other file S:\Files\TB Group.xls it succeded
May I know why? It's been bugging me this whole day.

Function Control_IsFile_Exist(ByVal path As String)
    Dim value As Boolean
    
    If Dir$(path, vbDirectory) <> "" Then
        value = True
    ElseIf Dir$(path & ".xls", vbDirectory) <> "" Or Dir$(path & ".xlsx", vbDirectory) <> "" Or Dir$(path & ".xlsm", vbDirectory) <> "" Then
        value = True
    Else
        value = False
    End If
    Control_IsFile_Exist = value
End Function

Thanks in advance

-sea-

 

[seageath]

Any idea what might cause the error?
Need your help urgently. Thanks a lot.

-sea-

Hi ex(cel)perts.

I got the following code to check existing file to be opened.
But failed for one of the file on mapping drive
For example I want to check S:\Files\zTB Group.xls, it failed.
But on other file S:\Files\TB Group.xls it succeded
May I know why? It's been bugging me this whole day.

Function Control_IsFile_Exist(ByVal path As String)
    Dim value As Boolean
    
    If Dir$(path, vbDirectory) <> "" Then
        value = True
    ElseIf Dir$(path & ".xls", vbDirectory) <> "" Or Dir$(path & ".xlsx", vbDirectory) <> "" Or Dir$(path & ".xlsm", vbDirectory) <> "" Then
        value = True
    Else
        value = False
    End If
    Control_IsFile_Exist = value
End Function

Thanks in advance

-sea-

 

[Richard Schollar]

Please could you expand on what actually happens ie do you get a VBA error? Or do you mean you get an unexpected result (eg because you think the file exists and the function returns False)? If an unexpected result, have you used F8 within the VBE to step through the code line by line to identify why the code may not be returning the expected result?

 

[thunder_anger]

what version of Excel are you using
2007 - 2003?

 

[thunder_anger]

Try this

Function Control_IsFile_Exist(ByVal path As String)
    Dim value As Boolean
    
    If Dir$(path, vbDirectory) <> "" Or Dir$(path & ".xls", vbDirectory) <> "" Or Dir$(path & ".xlsx", vbDirectory) <> "" Or Dir$(path & ".xlsm", vbDirectory) <> "" Then
        
        value = True
    Else
        value = False
    End If
    Control_IsFile_Exist = value
End Function

please avoid using words like Value, Date, Time in variables it confuses VBA

 

[seageath]

Case Closed - Check Existing File Failed

Hi all, sorry for the "silly question"

Just found out there is a "space" after ".xls" therefore I just put Trim function for the value and everything working just fine. Thanks for all the help



-sea-