VBA Check if SharePoint PDF file already exists before saving to SharePoint folder

[drefiek2]

Greetings everyone,
I have searched around for this but I am struggling to find help for this particular matter. I have a module button which saves a sheet as a PDF in a particular SharePoint folder with the file name from cell D6 (including removing illegal characters) and D7 - it works perfectly. If it saves successfully I get a message box, if it doesn't I get a message box - both work perfectly (see full code below). Note I have made the SharePoint details random for posting here.

Sub SaveAsPDF()
'
' SaveAsPDF Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Dim SharePointPath As String
    Dim PdfFileName As String
    Dim msg As String

    On Error GoTo SaveError

    SharePointPath = "https://company.sharepoint.com/folder/"

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

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SharePointPath & PdfFileName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    msg = "Handover successfully delivered to SharePoint."
    MsgBox msg, vbInformation, "Save Successful"
    Exit Sub

SaveError:
    msg = "Handover was not delivered to SharePoint:" & vbCr & vbCr & Err.Number & " - " & Err.Description
    MsgBox msg, vbCritical, "Save Failure"
End Sub

What I am now keen for it to do, before the save is executed, is check whether the PDF already exists in the above SharePoint folder, if it does exist I want it to give a warning message box about overwriting (the user can still go ahead and save if they wish), if it doesn't exist then it should just save as normal. I tried the following but it did not work - it gave error 52 - bad file name or number. Can anyone assist with a corrected code or alternative code for doing this?

Sub SaveAsPDF()
'
' SaveAsPDF Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Dim SharePointPath As String
    Dim PdfFileName As String
    Dim msg As String

    On Error GoTo SaveError

    SharePointPath = "https://company.sharepoint.com/folder/"

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

    If Dir(SharePointPath & PdfFileName) <> "" Then
        MsgBox "File already exists"
    Else
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SharePointPath & PdfFileName, _
         Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

        msg = "Handover successfully delivered to SharePoint."
        MsgBox msg, vbInformation, "Save Successful"
    End If
    Exit Sub

SaveError:
    msg = "Handover was not delivered to SharePoint:" & vbCr & vbCr & Err.Number & " - " & Err.Description
    MsgBox msg, vbCritical, "Save Failure"
End Sub