Excel VBA: create and open date stamp folder

Luis Diaz
Luis Diaz used Ask the Experts™
on
Hello experts,
I am looking for a procedure to create a  date stamp folder with the following requirements:
1-MsgBox: "Please make sure to report folder in which should be located new date stamp folder in range A1". if you haven't reported folder click on cancel (exit sub)."
2-Check if reported folder exist if not MsgBox: "Unable to create folder as reported folder doesn't exist.". Exit sub
3-If date stamp folder has been created: MsgBox "Date stamp folder has been created" and open created folder

I have the following procedure part which can help.
Sub Create_Date_Stamp_Folder()
Dim sFolder As String

    If Len(Dir(sFolder & Format(Now, "YYYYMM_DDHHMMSS") & "\", vbDirectory)) = 0 Then
    MkDir sfolder & Format(Now, "YYYYMM_DDHHMMSS") & "\"
    End If

End Sub

Open in new window


Thank you in advance for your feedback.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Erm,
your explaination 2 and 3 make no sens to begin with:
2) Creation should fail if the folder does not exist, but if the folder exist, there is no need to create it.
3) because of the above, folder can never be created.

And, by respect to SRP, a procedure responsible for creatinga folder, should not prompt the user for anything, this isn't it's job.
In the end, you'll need 2 functions:
First one will create the folder (and only that).
2nd one diplaying messages and calling the first one.
Luis DiazIT consultant

Author

Commented:
Point 2 refers to root folder in which will be created date stamp foder. So if this folder doesn't exist an exit sun is required. This will avoid to have an unwanted error.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You may try something like this...
Sub Create_Date_Stamp_Folder()
    Dim sFolder As String
    
    'This assumes that the root folder is listed in A1 of the Active Sheet
    'If this is not the case, declare a Sheet Variable and set it accordingly and then qualify the range with the Sheet Refernce
    If Range("A1").Value = "" Then
        MsgBox "You haven't input the Report Folder in the cell A1.", vbExclamation
        Exit Sub
    End If
    sFolder = Range("A1").Value
    'Checking if the Root Folder doesn't exist.
    If Len(Dir(sFolder, vbDirectory)) = 0 Then
        MsgBox "The Report Folder doesn't exists.", vbExclamation, "Action Cancelled!"
        Exit Sub
    End If
    If Right(sFolder, 1) <> "\" Then sFolder = sFolder & "\"
    'Creating New Folder with DateTimeStamp inside the Root Folder
    sFolder = sFolder & Format(Now, " YYYYMM_DDHHMMSS")
    MkDir sFolder
    MsgBox "Report Folder " & sFolder & " has been created successfully.", vbInformation, "Report Folder Created!"
End Sub

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Also to open the created folder, try this...

Sub Create_Date_Stamp_Folder()
    Dim sFolder As String
    
    'This assumes that the root folder is listed in A1 of the Active Sheet
    'If this is not the case, declare a Sheet Variable and set it accordingly and then qualify the range with the Sheet Refernce
    If Range("A1").Value = "" Then
        MsgBox "You haven't input the Report Folder in the cell A1.", vbExclamation
        Exit Sub
    End If
    sFolder = Range("A1").Value
    'Checking if the Root Folder doesn't exist.
    If Len(Dir(sFolder, vbDirectory)) = 0 Then
        MsgBox "The Report Folder doesn't exists.", vbExclamation, "Action Cancelled!"
        Exit Sub
    End If
    If Right(sFolder, 1) <> "\" Then sFolder = sFolder & "\"
    'Creating New Folder with DateTimeStamp inside the Root Folder
    sFolder = sFolder & Format(Now, "YYYYMM_DDHHMMSS")
    MkDir sFolder
    MsgBox "Report Folder " & sFolder & " has been created successfully.", vbInformation, "Report Folder Created!"
    Shell "C:\WINDOWS\explorer.exe """ & sFolder & "", vbNormalFocus
End Sub

Open in new window

Luis DiazIT consultant

Author

Commented:
Thank you. Unable to test it right now I will keep you posted.
Luis DiazIT consultant

Author

Commented:
It works. Thank you very much for your help!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial