Only save file is it doesnt exist in folder

[ipbr21054]

Hi,

I am using the code below to see if file is in folder before saving.

If Yes show msgbox to advise user.
If No then continue with the save.

My problem is that i see the Msgbox PDF ALLREADY GENERATED WHETHER IT IT OR IT ISNT.
Do you see why Thanks.

Private Sub PurchasedCode_Click()
  Dim sPath As String
  Dim strFileName As String
  Dim sh As Worksheet
 
  With ActiveSheet
    If .Range("Q1") = "" Then
      MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
      Exit Sub
    End If
    If .Range("N1") = "M" Then
    
    Dim File As String
        File = PurchasedCode.Value ' CUSTOMERS NAME IN USERFORM TEXTBOX
    Dim DirFile As String

        DirFile = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF" & File ' PATH TO WHERE PDF FILES ARE SAVED
        
    If Dir(DirFile) = "" Then ' IF FILE DOES NOT EXISTS THEN CONTINUE TO SAVE FILE
    
        strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " (SLS).pdf"
        .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    Else
        strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & ".pdf"
        .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    End If
    Else ' IF FILE IS PRESENT DO NOT ALLOW FILE TO BE OVERWRITTEN & TO SHOW MSGBOX
        MsgBox "PDF ALLREADY GENERATED", vbCritical + vbOKOnly, "PDF ALLREADY GENERATED MESSAGE"
    End If
    Exit Sub
    
    .Range("B3").Select
    Unload PrinterForm
    MsgBox "PDF FILE HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
 End With
 
End Sub

 

[Alex Blakenburg]

You need to clean up your indentation to make it clear what lines of code are executed in what If statements.
Your message box line is going to execute whenever Range("N1") is not = to "M"

    If .Range("N1") = "M" Then
        Dim File As String
        File = PurchasedCode.Value ' CUSTOMERS NAME IN USERFORM TEXTBOX
        Dim DirFile As String
    
        DirFile = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF" & File ' PATH TO WHERE PDF FILES ARE SAVED
            
        If Dir(DirFile) = "" Then ' IF FILE DOES NOT EXISTS THEN CONTINUE TO SAVE FILE
            strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " (SLS).pdf"
            .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
        Else
            strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & ".pdf"
            .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
        End If
    Else ' IF FILE IS PRESENT DO NOT ALLOW FILE TO BE OVERWRITTEN & TO SHOW MSGBOX
        MsgBox "PDF ALLREADY GENERATED", vbCritical + vbOKOnly, "PDF ALLREADY GENERATED MESSAGE"
    End If

 

[ipbr21054]

With regards

If .Range("N1") = "M" Then

The difference to the saved file is that (SLS) is added to the saved name.
Example
TOM JONES 001.pdf or TOM JONES 001 (SLS).pdf

My goal is to not allow in the future the file to be overwritten.

If the file TOM JONES 001.pdf exists & say another customer with same name is trying to be saved like so TOM JONES 001.pdf then a Msgbox should pop up & alert the user to take a look at it

Thanks

 

[Alex Blakenburg]

You are adding SLS if this condition is met:
If Dir(DirFile) = ""
which is the file does not exit.
In the else part of that If statement being when the file already exists you are dropping the SLS.

The only condition that triggers your File exists message is when
.Range("N1") does not = "M"

 

[ipbr21054]

Well im now confused.

Can you advise please & here is the file without the code that i added.

Private Sub PurchasedCode_Click()
  Dim sPath As String
  Dim strFileName As String
  Dim sh As Worksheet
 
  With ActiveSheet
    If .Range("Q1") = "" Then
       MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
       Exit Sub
    End If

   If .Range("N1") = "M" Then    
     strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " (SLS).pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    Else
      
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    End If

    .Range("B3").Select
    Unload PrinterForm
    MsgBox "PDF FILE HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
   End With

 

[Alex Blakenburg]

Try the below:
You will need to work out whether you want the last 2 lines to run regardless of whether you save the file or not.
The last 2 lines being B3 Select and unload the form.

Private Sub PurchasedCode_Click()
  Dim sPath As String
  Dim strFileName As String
  Dim sh As Worksheet
 
  With ActiveSheet
    If .Range("Q1") = "" Then
       MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
       Exit Sub
    End If

    If .Range("N1") = "M" Then
       strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " (SLS).pdf"
    Else
       strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & ".pdf"
    End If
    
    If Dir(strFileName) = "" Then
       .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
        MsgBox "PDF FILE HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
    Else
        ' IF FILE IS PRESENT DO NOT ALLOW FILE TO BE OVERWRITTEN & TO SHOW MSGBOX
        MsgBox "PDF ALLREADY GENERATED", vbCritical + vbOKOnly, "PDF ALLREADY GENERATED MESSAGE"
    End If

    .Range("B3").Select
    Unload PrinterForm

   End With

End Sub

 

[ipbr21054]

Many thanks that did the trick & works well.

I try to do what i can but when it doesnt work as it should i ask in the group for help / assistance but then i find i spend more time trying to work out what a member has asked or suggested etc i then get confused due to my issues.

It works great.
Have a nice weekend & thanks for the reply to assist.

 

[Alex Blakenburg]

If you need me to explain any of it let me know. Otherwise I am glad it is working for you.

 

[ipbr21054]

OK thanks,i think ive got it.

I will use the same code for not allowing duplicates on another sheet.
If i get stuck etc i will start a new post & tag you in.

Thanks