52

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.

Improve this question
3
  • Try checking for zero-length string (i.e., ""), rather than for Empty. Commented Mar 18, 2013 at 15:32
  • Dir returns a zero length string if nothing is found. so dont use Empty use "" instead Commented Mar 18, 2013 at 15:34
  • 1
    Hi Guys, Thanks for the advise, I have changed it to "", I never knew it checked for zero length strings. While I'll use this for future best practice, it hasn't solved the issue. Any more suggestions? Commented Mar 18, 2013 at 15:49

7 Answers 7

Reset to default
130

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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 :)
Fails (= returns "") when the path is (just) a network share, e.g. "\\myServer\myShare".
Fails (= returns "") when path includes unicode characters. But ozmike's solution works.
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!
44

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
Improve this answer

3 Comments

Cleanest, and most efficient method! Kudos for this!
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
For those curious FileSystemObject docs
7

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.

Improve this answer

6 Comments

Can you explain third line of your code? How this function is evaluated?
@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.
ozmike's answer looks more elegant.
@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.
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.
|
6

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

Improve this answer

3 Comments

Dir(Directory, vbDirectory) returns "." when Directory is blank.
This works. @KalenGi this means the current directory exists and the first file in it is "." (refer MS-DOS directory listings).
This hangs when "" or "//" is passed. This answer is the best: stackoverflow.com/questions/15480389#41434560
6

This is the cleanest way... BY FAR:

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

Comments

5 
If Len(Dir(ThisWorkbook.Path & "\YOUR_DIRECTORY", vbDirectory)) = 0 Then
   MkDir ThisWorkbook.Path & "\YOUR_DIRECTORY"
End If
Improve this answer

Comments

-1

You can replace WB_parentfolder with something like "C:\". For me WB_parentfolder is grabbing the location of the current workbook. file_des_folder is the new folder i want. This goes through and creates as many folders as you need.

        folder1 = Left(file_des_folder, InStr(Len(WB_parentfolder) + 1, file_loc, "\"))
        Do While folder1 <> file_des_folder
            folder1 = Left(file_des_folder, InStr(Len(folder1) + 1, file_loc, "\"))
            If Dir(file_des_folder, vbDirectory) = "" Then      'create folder if there is not one
                MkDir folder1
            End If
        Loop
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.