Exists?

[Tom]

How can I in Excel VBA check if folder exists?
Something like:
path=C:\temp
If path exists then a=true

and same things for sheet
sheetname=test1
if sheetname exists then a=true

thanks tom

 

[Bob Phillips]

'-----------------------------------------------------------------
Function FolderExists(Folder) As Boolean
'-----------------------------------------------------------------
Dim sFolder As String
On Error Resume Next
sFolder = Dir(Folder, vbDirectory)
If sFolder <> "" Then
If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then
FolderExists = True
End If
End If
End Function


'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[KL]

Hi Tom,

for path:

If Dir(path)<>"" Then a=True

For sheet:

On Error Resume Next
Set s=Activeworkbook.Sheets(sheetname)
On Error GoTo 0
If Not s Is Nothing Then a=True

Regards,
KL

 

[Ron de Bruin]

Hi tom

One way

Sub testing1()
Dim Dirname As String
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dirname = "C:\Data\"
If Not fs.FolderExists(Dirname) Then
MsgBox "Not Exist"
Else
MsgBox "Exist"
End If
End Sub