Error Handling Exit Sub

[katekoz]

Hello,
I have an OnError GoTo statement. If there is an error with the MkDir (aka, the file already exists), then I want it to go to the ErrMsg that asks if the user wants to continue or not. If they continue, the files will overwrite. If they don't, the macro ends. This is working perfectly right now. The problem I have is when there is NO error. If that's the case, then the file gets created as expected, but then it sends the ErrMsg anyway. I don't want it to send the error message when there is no error. How do I fix this?

(side note: when the error exists, I want it to show the ErrMsg, NOT exit the sub)

'Create new month folder

strDirname = Format(Sheets("Inputs").Range("B2"), "yyyy-MM") & " Claims Prod Metrics" ' New folder name

On Error GoTo ErrMsg

MkDir "\\test.test.net\sites\departments\test\Test\Test Test\Test Test\" & strDirname

ErrMsg: ans = MsgBox("This file already exists.  Would you like overwrite the existing set of PDFs for the month/year that you generated?", vbYesNoCancel, "DUPLICATE FILE")
    Select Case ans
        Case vbYes
            ThisWorkbook.Sheets("Inputs").Range("M2").Value = UserValue
        Case vbNo
            MsgBox ("Files were not saved.")
            Exit Sub
        Case vbCancel
            MsgBox ("Files were not saved.")
            Exit Sub
    End Select

'Continue on with more code from here

 

[Norie]

Why would you overwrite the folder if it exists?

 

[Michael M]

Maybe

Sub PDF_User_Graphs()
Dim ws As Worksheet, sourcesheet As Worksheet, strDirname As String
Dim strPathname As String, strDefpath As String
Set sourcesheet = ActiveSheet
Application.ScreenUpdating = False
MsgBox "Please wait while the folder and files are created and saved.  This will take ~60 seconds.", , "Complete"
'Create new month folder
strDirname = Format(Sheets("Inputs").Range("B2"), "yyyy-MM") & " Claims Prod Metrics" ' New folder name
On Error GoTo ErrMsg
MkDir "\\test.test.net\sites\departments\test\" & strDirname
ErrMsg: ans = MsgBox("This file already exists.  Would you like overwrite the existing set of PDFs for the month/year that you generated?", vbYesNoCancel, "DUPLICATE FILE")
If ans = vbYes Then
        ThisWorkbook.Sheets("Inputs").Range("M2").Value = UserValue
       Else
            MsgBox ("Files were not saved.")
            Application.ScreenUpdating = True
        Exit Sub
End If
'Selects all of the username worksheets (excluding the list below) and saves them as pdf's to the filepath below.
For Each ws In Worksheets
    If ws.Name <> "All Users Month" And ws.Name <> "All Users Rolling" Then
        ws.Select
        sName = ws.Name
        sDate = Format(Sheets("Inputs").Range("B2"), " MMM-yy")
        
            Range("AT65:BF103").ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="\\test.test.net\sites\departments\test\" & strDirname & "\" & sName & sDate & ".pdf"
    End If
Next ws
'Selects the two sheets below and saves them as pdf's to the filepath below.
Sheets(Array("All Users Month Results", "All Users Rolling")).Select
ssName = "All User Results"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="\\test.test.net\sites\departments\test\" & strDirname & "\" & "1 " & ssName & sDate & ".pdf"
MsgBox "The files have been created and saved.", , "Complete"
Application.ScreenUpdating = True
Call sourcesheet.Activate
End Sub

 

[katekoz]

Michael M - I'll try that out, thanks! I'll let you know how it works.

Norie - The folder is for one month of data with about 20 pdf's within it. If a manager updates the data for that month (for example, adds training dates for a user) after he/she already created the folder and files within, then the files will all need to be updated with that new information, as all 20 files could change from that one input. All the files are saved as pdf's, so the only way to update them is to over-write each one of them with a new pdf.
I suppose I wouldn't need to re-create that folder, but would need to update all the files within the folder. I found that, if you re-create the file, then the macro just overwrites the pdf's within without any problems. THAT's the end goal - to overwrite all of those files within.

 

[Norie]

So if the folder exists you want to overwrite the files in the folder and if it doesn't you want to create the folder and then write the files to the folder?

 

[katekoz]

So if the folder exists you want to overwrite the files in the folder and if it doesn't you want to create the folder and then write the files to the folder?

Yes, exactly!

 

[katekoz]

Michael M - The macro worked, except that it prompted the message that "This file already exists..." when the file didn't already exist. If I clicked Yes, it did continue to create the files as expected. If I clicked No, it didn't create the files and exited the macro as expected. So, it seems that it's working properly except for prompting the error message when the file doesn't already exist. Perhaps there's a different error occurring that's causing it to send the message, other than the file already existing??

 

[katekoz]

I think I got it! I added the little "GoTo" in the vbYes case to jump it past creating the folder and straight to re-creating the files, and it seems to be working. It looks like it did overwrite the old set of files.

'Create new month folder

strDirname = Format(Sheets("Inputs").Range("B2"), "yyyy-MM") & " Claims Prod Metrics" ' New folder name

If Len(Dir("\\test.test.net\sites\departments\test\Test\" & strDirname, vbDirectory)) > 0 Then

    ans = MsgBox("This file already exists.  Would you like overwrite the existing set of PDFs for the month/year that you generated?", vbYesNoCancel, "DUPLICATE FILE")
        Select Case ans
            Case vbYes
                GoTo Exists
            Case vbNo
                MsgBox ("Files were not saved.")
                Exit Sub
            Case vbCancel
                MsgBox ("Files were not saved.")
                Exit Sub
        End Select
Else: MkDir "\\test.test.net\sites\departments\test\Test\" & strDirname
End If

Exists:
ThisWorkbook.Sheets("Inputs").Range("M2").Value = UserValue

'Selects all of the username worksheets (excluding the list below) and saves them as pdf's to the filepath below.
For Each ws In Worksheets
'Continue on with macro...

I need to do some more testing, but I think this will work. Thank you both SO MUCH for your help!!