PDA

View Full Version : Solved: ChkFolderExists not quite working



cdbrown
07-07-2006, 01:33 AM
Hi all,

I'm using the following code to check if a folder exists and if not create it. I'm recording the folder name in a cell and referencing that but it always defaults to False in the Function section

Function ChkFolderExists(strFolder As String) As Boolean

On Error GoTo ErrHandler:
If Dir(strFolder) <> "" Then
ChkFolderExists = True
Else
ChkFolderExists = False
End If
Exit Function
ErrHandler:
ChkFolderExists = False
End Function


Set wb1 = ThisWorkbook
Sheets("List").Select
SaveFolder = Cells(3, 6)
Cells(14, 6).Value = SaveFolder & "\Consequences\"
Folder = CStr(Cells(14, 6))
If ChkFolderExists(Folder) = False Then
MkDir Cells(14, 6)
End If
Rows = Sheets("Relat").Cells(8, 3)


When stepping through the macro I put the mouse over the strFolder and it returns

strFolder="K:\Test\Consequences\"
The folder exists and as it comes up false it tries to make the directory which causes an error. I have tried it both with the folder there and without and it always returns a false - any ideas? This loop works fine in a different macro.

Cheers
-cdbrown

Bob Phillips
07-07-2006, 02:14 AM
Try this



Function ChkFolderExists(strFolder As String) As Boolean

On Error GoTo ErrHandler:
ChkFolderExists = Dir(strFolder, vbDirectory) <> ""
Exit Function
ErrHandler:
ChkFolderExists = False
End Function

ALe
07-07-2006, 02:38 AM
your function returns an error if the folder is empty. xld function has not this problem

cdbrown
07-07-2006, 03:34 AM
Thank you both it worked a treat. Is it possible to also check if a file exists - if not skip over some of the code in the same way as the chkfolder. I store the filename and path of the file I want to open but if it doesn't exist I want to move on. Currently just got a little hack in the system
On Error GoTo Skip
but of course that will jump over any other problems.
Function ChkFileExists(strFName As String) As Boolean
On Error GoTo nofile
If ChkFileExists <> "" Then
ChkFileExists = True
Else
ChkFileExists = False
Exit Function
nofile:
ChkFileExists = False
End Function
Fname = CStr(Cells(14, 6))

Cheers
-cdbrown

johnske
07-07-2006, 04:44 AM
...Is it possible to also check if a file exists - if not skip over some of the code in the same way as the chkfolder...

Public Function FileExists(FilenameAndPath As String) As Boolean
' 'Dick Kusleika's technique
FileExists = CBool(Len(Dir(FilenameAndPath)))
End FunctionExample usage
Private Sub FileExistsExample()
If FileExists(ThisWorkbook.FullName) Then MsgBox "Yes" Else MsgBox "No"
If FileExists("C:\Garbage.xls") Then MsgBox "Yes" Else MsgBox "No"
End Sub

cdbrown
07-07-2006, 08:39 AM
That's done the trick - thanks johnske!

-cdbrown