none
Create folder user VBA RRS feed

  • Question

  • Hello - 

    I am looking for a way to have Excel (using VBA) create a folder in My Documents, save a file to the folder, and then delete the entire folder.

    Anyone have any suggestions on how this can be done? I will of course be combining this with some other functions etc., but just need a basis to start with on how to create the folder, save to it, and then be able to delete the folder.

    Any assistance will be greatly appreciated.

    Thanks in advance :)


    Wednesday, December 23, 2015 11:11 PM

Answers

  • The following example code saves the workbook containing the code as a copy to a new folder and then deletes the copy and then deletes the folder.

    If the code you are using will be saving another workbook (not the one containing the code) then use the usual "Save as" command with the workbook identification.

    Note that the files must be deleted first and then the folder removed. AFAIK this action cannot be done with a single command.

    Also note that the deleted files do not show up in the Deleted folder so take care that you are deleting the correct files because if you delete the wrong ones then might not be able to retrieve them. I have provided option code to delete all files from a folder so take exceptional care if using it.

    Sub Macro1()
        Dim strCurrFolder As String
        Dim strPath As String
        Dim strNewFolder As String
        Dim strNewWorkbook As String
           
        strCurrFolder = ThisWorkbook.Path
        strNewFolder = "TestFolder"
        MkDir strCurrFolder & "\" & strNewFolder
        strNewWorkbook = "My Workbook Copy.xlsm"
        ThisWorkbook.SaveCopyAs strCurrFolder & "\" & strNewFolder & "\" & strNewWorkbook
        Kill strCurrFolder & "\" & strNewFolder & "\" & strNewWorkbook
        'Kill strCurrFolder & "\" & strNewFolder & "\*.*"    'Optional Alternative to Kill all files in the folder
        RmDir strCurrFolder & "\" & strNewFolder
    End Sub


    Regards, OssieMac

    • Proposed as answer by David_JunFeng Thursday, December 24, 2015 2:44 AM
    • Marked as answer by David_JunFeng Tuesday, January 5, 2016 1:49 AM
    Thursday, December 24, 2015 1:17 AM