Vba to test file path error

[Gealer]

I have this code which I need to test the file path, as if your are working offline then the file path will not exist. It will only work if you are connected to the server.

Private Sub Workbook_Open()

Dim sFolderPath As String

Dim sCurrentWorkbookName As String



sFolderPath = "\\ SHARED\Public Folder\Switch Off"

If Right(sFolderPath, 1) <> "\" Then

sFolderPath = sFolderPath & "\"

End If

Application.DisplayAlerts = False

If Dir(sFolderPath, vbDirectory) <> vbNullString Then

sCurrentWorkbookName = ThisWorkbook.Name

Open "\\SHARED\Public Folder\ Switch Off\Log.txt" For Append As #1

Print #1, Format(Now, "dd/mm/yyyy hh:mm:ss"); ","; Environ("username"); ","; sCurrentWorkbookName; ","

Close #1

Else

Exit Sub

End If

Application.ScreenUpdating = True

End Sub

 

[Ruddles]

You can test whether your code can see the file path as follows:-

On Error Resume Next
Debug.Print Dir(sFolderPath, vbDirectory) ' just do something/anything with the file path
If Err.Number = 0 Then
    MsgBox "Found!" ' whatever you need to do if the file path exists
Else
    MsgBox "Not found!" ' whatever you need to do if the file path doesn't exist
End If
On Error GoTo 0

 

[Mentor82]

Hi,
How about this.

Dim fs as object
fs = CreateObject("Scripting.FileSystemObject")
If fs.FolderExists(sFolderPath)=False than exit sub

 

[Gealer]

Thanks for the help, both were worked fine.

 

[Ruddles]

Mentor82's solution is neater!

 

[Mentor82]

Glad I could help. Thanks for reply ?