Error on make folder Excel VBA

If Len(Dir("Report " & Format(Date, "yyyymmdd"))) = 0 Then 
    MkDir "Report " & Format(Date, "yyyymmdd") 
Else 
    MsgBox "You already ran today's reports and need to delete the folder before running them again." 
    Exit Sub 

Open in new window


I just get a Path/File access error on the MkDir line of code instead of getting the message box.
I have tried moving the lines around, too. Anybody know what I'm doing wrong?
LVL 22
Anne TroyEast Coast ManagerAsked:
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.

Ramesh VCommented:
Try this

Sub create()
    If Len(Dir("Report " & Format(Date, "yyyymmdd"))) = 0 Then
        MkDir "C:\Users\rvatta\Desktop\Reports" & "\" & "Report " & Format(Date, "yyyymmdd")
    Else
        MsgBox "You already ran today's reports and need to delete the folder before running them again."
        Exit Sub
    End If
End Sub
0
Ramesh VCommented:
You got error because you did not mention where to create a folder.
You are trying to create a folder named "Report 20150525" but under which directory you did not mention.

Change the code according to your path. Suppose if you want to create the new folder where the macro workbook is placed replace the path with ThisWorkbook.Path

Updated Code:

Sub create()
    If Len(Dir("Report " & Format(Date, "yyyymmdd"))) = 0 Then
        MkDir ThisWorkbook.Path & "\" & "Report " & Format(Date, "yyyymmdd")
    Else
        MsgBox "You already ran today's reports and need to delete the folder before running them again."
        Exit Sub
    End If
End Sub
0

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
Ejgil HedegaardCommented:
It is good practice to either change directory before using Dir, or specify path in the command, but not needed, since both Dir and MkDir will use the current folder if no path is specified.
But the Dir command refers to a file, not a folder, and the file is not found, hence the error.
Specify directory in the Dir command, then it works.
If Len(Dir("Report " & Format(Date, "yyyymmdd"), vbDirectory)) = 0 Then
0
Anne TroyEast Coast ManagerAuthor Commented:
It appears I needed BOTH.

Sub create()
If Len(Dir("Report " & Format(Date, "yyyymmdd"), vbDirectory)) = 0 Then
        MkDir ThisWorkbook.Path & "\" & "Report " & Format(Date, "yyyymmdd")
    Else
        MsgBox "You already ran today's reports and need to delete the folder before running them again."
        Exit Sub
    End If
End Sub

Open in new window


Thanks so much!
0
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 Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.