Cancel Code if No or Cancel response

[maxwell13]

Hi All

I am using the following code to save files into folders based on cell values.

Sub makeDir()

Application.ScreenUpdating = False

    Dim str As String
    
    
    ActiveSheet.Copy
        Range("A1").Select
        
    str = "C:\" & Range("a2")
    
    If Len(Dir(str, vbDirectory)) = 0 Then
        MkDir str
    End If
    
    str = str & "\" & Range("a3")
    
    If Len(Dir(str, vbDirectory)) = 0 Then
        MkDir str
    End If
    
    str = str & "\" & Range("a4")
    
    If Len(Dir(str, vbDirectory)) = 0 Then
        MkDir str
    End If

    str = str & "\" & Range("a5")
    
    If Len(Dir(str, vbDirectory)) = 0 Then
        MkDir str
    End If
   
     ChDir str
  
ActiveWorkbook.SaveAs Filename:=str & "\" & Range("a7").Text, FileFormat:=51

   Range("a1").Select

    ActiveWorkbook.Save
    
    ActiveWorkbook.Close
    
    
   Application.ScreenUpdating = True
  
   
End Sub

If the file name exists this line gives a Yes No Cancel MsgBox to overwrite or not.

ActiveWorkbook.SaveAs Filename:=str & "\" & Range("a7").Text, FileFormat:=51

Clicking Yes overwrites the file but No or Cancel throw an error and creates a new workbook.

Can I cancel the whole procedure if No or Cancel are selected so that a new workbook is not created and the user gets a MsgBox that nothing happened?

Thanks in advance for any help

maxwell13

 

[johnnyL]

If the file name exists this line gives a Yes No Cancel MsgBox to overwrite or not.

ActiveWorkbook.SaveAs Filename:=str & "\" & Range("a7").Text, FileFormat:=51

Clicking Yes overwrites the file but No or Cancel throw an error and creates a new workbook.

Can I cancel the whole procedure if No or Cancel are selected so that a new workbook is not created and the user gets a MsgBox that nothing happened?

I don't believe that line of code, by itself, will give a Yes No Cancel option.

 

[maxwell13]

I don't believe that line of code, by itself, will give a Yes No Cancel option.

I don't believe that line of code, by itself, will give a Yes No Cancel option.

Thanks for your response johnnyL:

That is the line that is highlighted when the error occurs

 

[johnnyL]

Ahh, I see what you are saying now.

Try replacing:

ActiveWorkbook.SaveAs Filename:=Str & "\" & Range("a7").Text, FileFormat:=51

with the following:

    FileNameToSave = Str & "\" & Range("a7").Text
'
    If Not Dir(FileToSave) <> "" Then                                       ' File doesn't exist yet
        ActiveWorkbook.SaveAs Filename:=FileNameToSave, FileFormat:=51
    Else                                                                    ' File exists already
        MsgBox "File name already exists. No action will be taken."
    End If

 

[johnnyL]

Oops. correction needed for my last post here.

If Not Dir(FileToSave) <> "" Then

should be:

If Not Dir(FileNameToSave) <> "" Then

Sorry about that.