VBA save as PDF overwrite warning code

[drefiek2]

Hi,
I have the following code which saves the sheet as a PDF in a particular folder. It works as desired. I need some code to add to this which will check the folder to see if the PDF already exists. It should produce an overwrite warning if it does and ask the user if they would like to continue or not.

 Select Case MsgBox("Is the date and shift type correct?", vbYesNo Or vbQuestion, Application.Name)
    Case vbNo
        Debug.Print "User exit"
        Exit Sub
    End Select
    Dim SharePointPath As String
    Dim PdfFileName As String
    Dim msg As String

    On Error GoTo SaveError

    SharePointPath = "FOLDER"

    PdfFileName = Replace(Range("D6").Value, "/", "") & ActiveSheet.Range("J6").Value
    
    If Worksheets("Mechanics").Range("B15").Value = True Then
    Call DarkMode
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SharePointPath & PdfFileName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Call DarkMode
    Else
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SharePointPath & PdfFileName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End If

    msg = "Handover saved. You can now close the spreadsheet."
    MsgBox msg, vbInformation, "Upload Successful"
    Exit Sub

SaveError:
    msg = "Handover not saved. Please contact X on e-mail and use the backup document for today." & vbCr & vbCr & Err.Number & " - " & Err.Description
    MsgBox msg, vbCritical, "Upload Failure"
End Sub

 

[DanteAmor]

After this line:

PdfFileName = Replace(Range("D6").Value, "/", "") & ActiveSheet.Range("J6").Value

Put this:

  If Dir(SharePointPath & PdfFileName & ".pdf") <> "" Then
    If MsgBox("PDF already exists. Continue", vbExclamation + vbYesNo) = vbNo Then
      Exit Sub
    End If
  End If

 

[drefiek2]

After this line:

PdfFileName = Replace(Range("D6").Value, "/", "") & ActiveSheet.Range("J6").Value

Put this:

  If Dir(SharePointPath & PdfFileName & ".pdf") <> "" Then
    If MsgBox("PDF already exists. Continue", vbExclamation + vbYesNo) = vbNo Then
      Exit Sub
    End If
  End If

Hi, thanks for your reply.
I receive error 52 - bad file name or number. I expect the issue will be my SharePointPath which is a http SharePoint folder (mapped to everyone's local C: OneDrives). I did temporarily change the SharePointPath to my C:Downloads folder to test it though and the code works.
My question now I suppose is what can we do to swap out the https:// path in favour of a local C: OneDrive path. Bearing in mind 5-6 people will be using this spreadsheet and therefore I cannot put my own C:Drive user on there. The code will need to adapt to whoever it using it. Everyone has the same OneDrive path to the folder e.g. C:\Users\USER\OneDrive - COMPANY\Folder

 

[DanteAmor]

C:\Users\USER\OneDrive - COMPANY\Folder

So in your original code, replace this:

  SharePointPath = "FOLDER"
  PdfFileName = Replace(Range("D6").Value, "/", "") & ActiveSheet.Range("J6").Value

For this

  SharePointPath = Environ("USERPROFILE") & "\OneDrive - COMPANY\Folder\"
  PdfFileName = Replace(Range("D6").Value, "/", "") & ActiveSheet.Range("J6").Value
 
  If Dir(SharePointPath & PdfFileName & ".pdf") <> "" Then
    If MsgBox("PDF already exists. Continue", vbExclamation + vbYesNo) = vbNo Then
      Exit Sub
    End If
  End If

Note: Beware the path separator is now "\"
I don't know what you have in cell "/" or "\"

Replace(Range("D6").Value, "/", "")

 

[drefiek2]

So in your original code, replace this:

  SharePointPath = "FOLDER"
  PdfFileName = Replace(Range("D6").Value, "/", "") & ActiveSheet.Range("J6").Value

For this

  SharePointPath = Environ("USERPROFILE") & "\OneDrive - COMPANY\Folder\"
  PdfFileName = Replace(Range("D6").Value, "/", "") & ActiveSheet.Range("J6").Value
 
  If Dir(SharePointPath & PdfFileName & ".pdf") <> "" Then
    If MsgBox("PDF already exists. Continue", vbExclamation + vbYesNo) = vbNo Then
      Exit Sub
    End If
  End If

Note: Beware the path separator is now "\"
I don't know what you have in cell "/" or "\"

Replace(Range("D6").Value, "/", "")

Works perfectly! D6 is a date cell with / separators which are illegal save characters so I wanted to remove them out of the file name. Thanks for your help!