How do I handle all the IFs in VBA 2013?

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

    End If

Open in new window


If the folder already exists, I want to offer to delete it, and do so if the user hits OK.
If they don't exist, it should just make them and continue with the rest of my macro, not shown here

The above isn't working, and I don't understand what the Excel123 is about, but I suspect I'll find out when I (we/you) work out which IF goes where. :)

Thanks in advance!
LVL 22
Anne TroyEast Coast ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

ProfessorJimJamMicrosoft Excel ExpertCommented:
Excel123 has no role to change anything it is just a title to the msgbox

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
Saqib Husain, SyedEngineerCommented:
This should do it
    If Len(Dir(ThisWorkbook.Path & "\Report " & Format(Date, "yyyymmdd"), vbDirectory)) = 0 Then
        MkDir ThisWorkbook.Path & "\" & "Report " & Format(Date, "yyyymmdd")
    Else
        Select Case MsgBox("You already ran today's reports and need to delete the folder before running them again. Press Ok To Delete it, cancel to Exit.", vbOKCancel, "Excel123")
        Case vbCancel
            Exit Sub
        Case vbOK
            RmDir ThisWorkbook.Path & "\" & "Report " & Format(Date, "yyyymmdd")
        End Select
    End If

Open in new window

Saqib Husain, SyedEngineerCommented:
BTW what you had was

if     vbcancel = msgbox(...blah blah) = vbcancel

which is probably what was throwing it off.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Anne TroyEast Coast ManagerAuthor Commented:
Thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, ...
Anne TroyEast Coast ManagerAuthor Commented:
Okay, I spoke too soon. When the folder exists, and I hit OK to delete it, I get Runtime error 75, invalid path/filename on this line:

           
 RmDir ThisWorkbook.Path & "\" & "Report " & Format(Date, "yyyymmdd")

Open in new window

Saqib Husain, SyedEngineerCommented:
Highlight the part of the line after rmdir and then press shift-F9
This will show you the entire path name
Compare this with the actual pathname in your explorer
ProfessorJimJamMicrosoft Excel ExpertCommented:
perhaps the file from which you run the code is not saved anywhere.  

i tested the code provided by Sayed and it has no problem.

if there is no directory, it create the folder then if folder already exists then if you click ok to the messagebox then it deletes the existing folder.
Anne TroyEast Coast ManagerAuthor Commented:
Thank you, gentlemen. Here is a picture.

errorimage.png
Saqib Husain, SyedEngineerCommented:
You highlighted only the part thisworkbook.path
Extend this to the end of the line and again do shift-f9
then click Ok
Now select the string shown (sans the quotes), copy it and paste in the explorer and see whether it fetches the correct folder.
ProfessorJimJamMicrosoft Excel ExpertCommented:
can you try with this code, i changed it to FSO method.

Sub TEST()


If Len(Dir(ThisWorkbook.Path & "\Report " & Format(Date, "yyyymmdd"), vbDirectory)) = 0 Then
        MkDir ThisWorkbook.Path & "\" & "Report " & Format(Date, "yyyymmdd")
    Else
        Select Case MsgBox("You already ran today's reports and need to delete the folder before running them again. Press Ok To Delete it, cancel to Exit.", vbOKCancel, "Excel123")
        Case vbCancel
            Exit Sub
        Case vbOK
        
Dim fso As Object

On Error GoTo ErrHandler

    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.DeleteFolder ThisWorkbook.Path & "\Report " & Format(Date, "yyyymmdd"), True
    Set fso = Nothing
    
    Exit Sub
    
ErrHandler:

    MsgBox Err.Description
    Err.Clear
    Exit Sub

        End Select
    End If

End Sub

Open in new window

Anne TroyEast Coast ManagerAuthor Commented:
I had to change it a little bit, but it works. Thank you! Years ago, I could create a question to award you points. Is that still okay to do here?
ProfessorJimJamMicrosoft Excel ExpertCommented:
Are you referring to me or Sayed?
ProfessorJimJamMicrosoft Excel ExpertCommented:
If you are referring to me then you can request attention to this question and then you write what you would like to do
Anne TroyEast Coast ManagerAuthor Commented:
Thank you!
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.