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 StringDim site As StringDim screeningdate As Datescreeningdate = Range("b7").ValueDim screeningdate_text As Stringscreeningdate_text = Format$(screeningdate, "yyyy\-mm\-dd")client = Range("B3").Valuesite = Range("B23").ValueDim SrceFileDim DestFileIf Dir("C:\2013 Recieved Schedules" & "\" & client) = Empty ThenMkDir "C:\2013 Recieved Schedules" & "\" & clientEnd IfSrceFile = "C:\2013 Recieved Schedules\schedule template.xlsx"DestFile = "C:\2013 Recieved Schedules\" & client & "\" & client & " " & site & " " & screeningdate_text & ".xlsx"FileCopy SrceFile, DestFileRange("A1:I37").SelectSelection.CopyWorkbooks.Open Filename:= _"C:\2013 Recieved Schedules\" & client & "\" & client & " " & site & " " & screeningdate_text & ".xlsx", UpdateLinks:= _0Range("A1:I37").PasteSpecial Paste:=xlPasteValuesRange("C6").SelectApplication.CutCopyMode = FalseActiveWorkbook.SaveActiveWindow.CloseEnd SubYou'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.
Best Answer
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) = "" ThenNote 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.
Use the FolderExists method of the Scripting object.
Public Function dirExists(s_directory As String) As BooleanDim oFSO As ObjectSet oFSO = CreateObject("Scripting.FileSystemObject")dirExists = oFSO.FolderExists(s_directory)End FunctionTo be certain that a folder exists (and not a file) I use this function:
Public Function FolderExists(strFolderPath As String) As BooleanOn Error Resume NextFolderExists = ((GetAttr(strFolderPath) And vbDirectory) = vbDirectory)On Error GoTo 0End FunctionIt works both, with \ at the end and without.
I ended up using:
Function DirectoryExists(Directory As String) As BooleanDirectoryExists = FalseIf Len(Dir(Directory, vbDirectory)) > 0 ThenIf (GetAttr(Directory) And vbDirectory) = vbDirectory ThenDirectoryExists = TrueEnd IfEnd IfEnd Functionwhich 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
This is the cleanest way... BY FAR:
Public Function IsDir(s) As BooleanIsDir = CreateObject("Scripting.FileSystemObject").FolderExists(s)End FunctionIf Len(Dir(ThisWorkbook.Path & "\YOUR_DIRECTORY", vbDirectory)) = 0 ThenMkDir ThisWorkbook.Path & "\YOUR_DIRECTORY"End IfYou 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_folderfolder1 = Left(file_des_folder, InStr(Len(folder1) + 1, file_loc, "\"))If Dir(file_des_folder, vbDirectory) = "" Then 'create folder if there is not oneMkDir folder1End IfLoop