Consulting

Results 1 to 5 of 5

Thread: Make Directory and File If doesn't exist?

  1. #1
    VBAX Newbie
    Joined
    Sep 2019
    Posts
    3
    Location

    Question Make Directory and File If doesn't exist?

    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!

  2. #2
    VBAX Newbie
    Joined
    Sep 2019
    Posts
    3
    Location
    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.
    Last edited by Xepher; 09-17-2019 at 12:59 PM.

  3. #3
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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

  4. #4
    VBAX Newbie
    Joined
    Sep 2019
    Posts
    3
    Location
    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?

    I have attached a sample of what I am trying to do. Hope this helps
    Attached Files Attached Files
    Last edited by Xepher; 09-17-2019 at 03:25 PM.

  5. #5
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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
    Attached Files Attached Files
    Last edited by Artik; 09-17-2019 at 03:56 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •