Checks if folder exists at the given address [VBA]

[Martin_H]

Hi team,

I have been using this code (see below) to check if Excel.xlsm exists at given address. This code works perfectly, without any problem.

Public Sub Open_Excel(ByRef control As Office.IRibbonControl)

'Checks if Excel.xlsm exists at the given address
Dim FilePath As String
Dim TestStr As String
FilePath = "P:\ADDRESS\ADDRESS\Excel.xlsm"
TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0
If TestStr = "" Then
MsgBox "Does not exist.", vbExclamation
Exit Sub
Else
End If

'Open Excel.xlsm
With Application
.ScreenUpdating = False
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("Excel.xlsm")
On Error GoTo 0
If wb Is Nothing Then
Set wb = Workbooks.Open("P:\ADDRESS\ADDRESS\Excel.xlsm")
End If
.ScreenUpdating = True
End With

End Sub

Now I want to add something similar to the code below.

As you can see in the code above, under the line 'Checks if Excel.xlsm exists at the given address, but in this case for a folder.

Basically I want to check if specific folder named Folder exists at the given address before actually opening Folder.

Public Sub Open_Folder(ByRef control As Office.IRibbonControl)

'Open Folder
With Application
.ScreenUpdating = False
Dim strFolder As String
strFolder = "P:\ADDRESS\ADDRESS\Folder"
ActiveWorkbook.FollowHyperlink Address:=strFolder, NewWindow:=True
.ScreenUpdating = True
End With

End Sub

I hope it is clear.

Thank you.

 

[Martin_H]

Solved.

Public Sub Open_Folder(ByRef control As Office.IRibbonControl)

'Checks if folder exists at the given address
Dim sFolderPath As String
sFolderPath = "P:\ADDRESS\ADDRESS\Folder"
If Right(sFolderPath, 1) <> "\" Then
sFolderPath = sFolderPath & "\"
End If
If Dir(sFolderPath, vbDirectory) <> vbNullString Then

'Open Folder
With Application
.ScreenUpdating = False
Dim strFolder As String
strFolder = "P:\ADDRESS\ADDRESS\Folder"
ActiveWorkbook.FollowHyperlink Address:=strFolder, NewWindow:=True
.ScreenUpdating = True
End With

Else

MsgBox "Does not exist.", vbExclamation
End If

End Sub