Excel VBA Check if directory exists error

Question

I have a spreadsheet that upon clicking a button will duplicate itself by copying/pasting everything to a new workbook and save the file with a name that is dependent upon some variable values (taken from cells on the spreadsheet). My current goal is to get it to save the sheet in different folders depending on the name of client name (cell value held in variable), while this works on the first run, I get an error after.

The code checks if the directory exists and creates it if not. This works, but after it is created, running it a second time throws the error:

Runtime Error 75 - path/file access error.

My code:

Sub Pastefile()

Dim client As String
Dim site As String
Dim screeningdate As Date
screeningdate = Range("b7").Value
Dim screeningdate_text As String
screeningdate_text = Format$(screeningdate, "yyyy\-mm\-dd")
client = Range("B3").Value
site = Range("B23").Value

Dim SrceFile
Dim DestFile

If Dir("C:\2013 Recieved Schedules" & "\" & client) = Empty Then
    MkDir "C:\2013 Recieved Schedules" & "\" & client
End If

SrceFile = "C:\2013 Recieved Schedules\schedule template.xlsx"
DestFile = "C:\2013 Recieved Schedules\" & client & "\" & client & " " & site & " " & screeningdate_text & ".xlsx"

FileCopy SrceFile, DestFile

Range("A1:I37").Select
Selection.Copy
Workbooks.Open Filename:= _
    "C:\2013 Recieved Schedules\" & client & "\" & client & " " & site & " " & screeningdate_text & ".xlsx", UpdateLinks:= _
    0
Range("A1:I37").PasteSpecial Paste:=xlPasteValues
Range("C6").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

End Sub

You'll have to excuse my lack of knowledge in this area, I am still learning. I have a very strong feeling it has something to do with the directory checking logic, as when the error is thrown the MkDir line is highlighted.

Solution 1

To check for the existence of a directory using Dir, you need to specify vbDirectory as the second argument, as in something like:

If Dir("C:\2013 Recieved Schedules" & "\" & client, vbDirectory) = "" Then

Note that, with vbDirectory, Dir will return a non-empty string if the specified path already exists as a directory or as a file (provided the file doesn't have any of the read-only, hidden, or system attributes). You could use GetAttr to be certain it's a directory and not a file.

Solution 2

Use the FolderExists method of the Scripting object.

Public Function dirExists(s_directory As String) As Boolean
    Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    dirExists = oFSO.FolderExists(s_directory)
End Function

Solution 3

To be certain that a folder exists (and not a file) I use this function:

Public Function FolderExists(strFolderPath As String) As Boolean
    On Error Resume Next
    FolderExists = ((GetAttr(strFolderPath) And vbDirectory) = vbDirectory)
    On Error GoTo 0
End Function

It works both, with \ at the end and without.

Solution 4

I ended up using:

Function DirectoryExists(Directory As String) As Boolean
    DirectoryExists = False
    If Len(Dir(Directory, vbDirectory)) > 0 Then
        If (GetAttr(Directory) And vbDirectory) = vbDirectory Then
            DirectoryExists = True
        End If
    End If
End Function

which is a mix of @Brian and @ZygD answers. Where I think @Brian's answer is not enough and don't like the On Error Resume Next used in @ZygD's answer

Solution 5

This is the cleanest way... BY FAR:

Public Function IsDir(s) As Boolean
    IsDir = CreateObject("Scripting.FileSystemObject").FolderExists(s)
End Function

View more solutions

Comments

[user1571463]

Hi Brian, Thanks a ton for you're help. I thought it was an issue with the logic of comparing for a non-value. I appreciate it :)

[Can Sahin]

Fails (= returns "") when the path is (just) a network share, e.g. "\\myServer\myShare".

[Przemyslaw Remin]

Can you explain third line of your code? How this function is evaluated?

[Jose A]

Cleanest, and most efficient method! Kudos for this!

[KalenGi]

Dir(Directory, vbDirectory) returns "." when Directory is blank.

[ZygD]

Looks like it's a very consistent method. I would only add Dim OFSO As Object for those who employ the best practice of using Option Explicit

[ZygD]

@wotter - you can check it yourself employing Err.Number and Err.Description. I was able to get numbers 3 and 53. I think having several error codes is exactly the reason why I chose not to specify If conditions for different error numbers. Basically, there's only one case where you are happy - when you supply a proper directory address string, and this address is obviously the thing you would inspect in case the function resulted in a presumably incorrect False.

[ZygD]

ozmike's answer looks more elegant.

[wotter]

@ZygD in fact, I was wondering if you get a False at all when there is an error. My understanding is that in this case FolderExists = is not executed so it could have a value other than True or False, maybe?! (I ask because I had a case like this where my boolean had an undefined value). So I would suggest initializing FolderExists to False.

[ZygD]

I get your point. It is worth consideration. As far as my tests go, I do get False even launching this subsequently with incorrect and correct paths and vice versa. Apparently the function initially starts with False value and it is reinitialized every time it is called. I don't like this logic of resorting to errors, but at the time it seemed the best option.

[Henrik Erlandsson]

This works. @KalenGi this means the current directory exists and the first file in it is "." (refer MS-DOS directory listings).

[Henrik Erlandsson]

If boolean return values are False as default (or another value which evaluates as False), this works, although you shouldn't error handle everything. For example, if there's a typo or missing value or anything that doesn't generate a compile error inside the error handling, the code will not run as intended, and you would be oblivious to it.

[Philip Swannell]

Fails (= returns "") when path includes unicode characters. But ozmike's solution works.

[ZygD]

This hangs when "" or "//" is passed. This answer is the best: stackoverflow.com/questions/15480389#41434560

[JeroenDV]

For those curious FileSystemObject docs

[pablorenato]

THIS WORKS. I used Dir() without vbDirectory to check if directory exists. It always returned a blank string. After add vbDirectory parameter it returns the directory name (if you omit trailing slash) or a "." if you include trailing slash. In both cases, you get a non-empty string if the directory exists and an EMPTY string if it does not. So now I can safely check if directory exists!