Excel VBA: create and open date stamp folder

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.
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
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.
LD16Author 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 ExpertCommented:
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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Thank you. Unable to test it right now I will keep you posted.
LD16Author Commented:
It works. Thank you very much for your help!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.