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.
If Len(Dir(ThisWorkbook.Path & "\YOUR_DIRECTORY", vbDirectory)) = 0 Then
MkDir ThisWorkbook.Path & "\YOUR_DIRECTORY"
End If
This is the cleanest way... BY FAR:
Public Function IsDir(s) As Boolean
IsDir = CreateObject("Scripting.FileSystemObject").FolderExists(s)
End Function
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
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
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.
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
Source: Stackoverflow.com