Make Directory and File If doesn't exist?

[Xepher]

I am trying to create a macro to Check if directory exists, if not then create, check if file exists, if not then create.

If they do exist then save?

Here is what I have an I am running into a bit of a bind Please help!



Sub SaveProposal_Click()


Dim strRet As String
Dim strFolder As String
Dim strFName As String

strFolder = Range("C5").Value


strFName = Range("C6").Value


strRet = Dir("G:\Prospects\" & strFolder, vbDirectory)

If strRet = "" Then
MkDir "G:\Prospects\" & strFolder
End If

If strRet & "\" & "Proposals" = "" Then
MkDir "G:\Prospects\" & strFolder & "\" & "Proposals"
End If


ActiveWorkbook.SaveAs ("G:\Prospects\" & strFolder & "\" & "Proposals" & "\" & strFName & Format(Now(), "-MMM-YYYY") & ".xlsm"), FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub


Thanks in advance for any help you might provide!
:banghead::banghead::banghead::banghead::banghead::banghead::banghead::bang head::banghead::banghead::banghead::banghead::banghead:

[Xepher]

I was able to create the directory if not created with the following code.



Private Sub CommandButton1_Click()
Dim strRet As String
Dim strFolder As String
Dim strFName As String


strFolder = Range("C14").Value


'strFolder = Range("C3").Value 'Or use this line if the folder name is on Sheet2


strFName = Range("C5").Value


strRet = Dir("G:\Prospects\" & strFolder, vbDirectory)


If strRet = "" Then
MkDir "G:\Prospects\" & strFolder
MkDir "G:\Prospects\" & strFolder & "\" & "Proposals"
End If


ActiveWorkbook.SaveAs "G:\Prospects\" & strFolder & "\" & "Proposals" & "\" & strFName & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub


However, If I then open up another one and have another "proposal" different name, I get a runtime error because directory already exists. I would like for it to check if directory is there and if not create it and then save as. If the directory IS there then just Save As.

Shouldn't this section of code, just skip if directory exists?

If strRet = "" Then
MkDir "G:\Prospects\" & strFolder
MkDir "G:\Prospects\" & strFolder & "\" & "Proposals"
End If

The way I am looking at this is If strRet = "SOMETHING" then Skip?

Any help would be greatly appreciated.

[Artik]

Try this:

In sheet module or in userform module
Private Sub CommandButton1_Click()
Dim strFolder As String
Dim strFName As String

strFolder = "G:\Prospects\" & Range("C14").Value & "\" & "Proposals\"

strFName = strFolder & Range("C5").Value & ".xlsm"

If CheckOrCreateMultiFolders(strFolder) Then
If Len(Dir(strFName)) = 0 Then
ActiveWorkbook.SaveAs Filename:=strFName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End If
Else
MsgBox "You do not have write permission to the folder:" & vbLf & _
"'" & strFolder & "'"
End If

End Sub

And in a standard module:
Function CheckOrCreateMultiFolders(strPath As String) As Boolean
'Checks if the entire path to the (sub)folder exists.
'If not, it tries to create it.
'The function returns:
'True - when the entire path exists or has been successfully created,
'False - when creation failed (e.g. due to lack of permissions)

Dim retVal As Long

If CreateObject("Scripting.FileSystemObject").FolderExists(strPath) Then
CheckOrCreateMultiFolders = True
Else
retVal = CreateObject("Wscript.Shell").Run("cmd /c " & "md """ & strPath & """", 0, True)
CheckOrCreateMultiFolders = (retVal = 0)
End If

End Function

Artik

[Xepher]

Thanks for the reply and sorry if this is a dumb question, I don't seem to understand how to tie in 2 macros to one button.

I have created the button and then "assigned Macro" in Macro I can put in the top Code. Where does the bottom code go?:bug:

I have attached a sample of what I am trying to do. Hope this helps

[Artik]

If the button you inserted is an ActiveX control, then the code programming the button is located in the worksheet module where you added the button.
If you have inserted a button from the "Form Controls" group into your worksheet, then the button programming code is usually placed in the standard module (in VBE from menu Insert/Module).


Since I can see the procedure declaration: Private Sub CommandButton1_Click(), I conclude that you used the ActiveX control.
The CheckOrCreateMultiFolders function should be placed in the standard module so that it is also available for other procedures that (in the future) could use it.


..::Edit
Take a look at the attachment ::..

Artik