VBA Code - Message Box "If file exists"

[Spaztic]

Hi, I just got some great help on being able to save my Excel File as a PDF.

But now, if the file name already exists where the pdf is being saved, I would like a warning message box to come up before it saves asking "File already exists, overwrite?" Yes button and No button.

• If Yes, the save happens,
• If No, the save doesn't happen
• If a file with the same name doesn't exist...the message box should not come up and the save should happen

Dim sPath As String, sName As String

sPath = ThisWorkbook.Path
sName = ThisWorkbook.Name
sName = Left(sName, InStrRev(sName, ".") - 1)

ThisWorkbook.Save
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, fileName:=sPath & "\" & sName & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False

 

[rollis13]

Add this line of code here:

ThisWorkbook.Save
If Not Dir(sPath & "\" & sName & ".pdf") = vbNullString Then If MsgBox("File already exists, overwrite ?", vbYesNo) = vbNo Then Exit Sub   '<- added
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, fileName:=sPath & "\" & sName & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False

 

[PeteWright]

another method:

Sub save()
   Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
   Dim sPath As String, sName As String, fName As String
   Dim Msg As String

   sPath = ThisWorkbook.path
   sName = ThisWorkbook.Name
   sName = Left(sName, InStrRev(sName, ".") - 1)
   fName = sPath & "\" & sName & ".pdf"
  
   If fso.FileExists(fName) Then
      GoTo OverwriteChoice
   Else
      GoTo SaveFile
   End If

OverwriteChoice:
   Msg = MsgBox("File already exists, overwrite?", vbYesNo Or vbExclamation, "File exists")
   If Msg = vbYes Then
      Debug.Print "file overwritten"
      GoTo SaveFile
   End If
   If Msg = vbNo Then
      Debug.Print "file was not saved"
      GoTo TheEnd
   End If

SaveFile:
   ThisWorkbook.save
   ActiveWorkbook.ExportAsFixedFormat _
   Type:=xlTypePDF, _
   filename:=fName, _
   Quality:=xlQualityStandard, _
   IncludeDocProperties:=True, _
   IgnorePrintAreas:=True, _
   OpenAfterPublish:=False

TheEnd:
End Sub

but the code snippet @rollis13 wrote is much prettier

 

[Spaztic]

@rollis13 , I get 'Bad File Name or Number' (that may be from my code...not what you added)

@PeteWright , I don't get an error when running your code. However, this is what I see.

• When I run the code the first time, it puts the pdf file in the same desktop folder as the excel file
• However, when I run the code again, it seems to just overwrite the pdf file (I get no Message Box)

 

[PeteWright]

Is your file on SharePoint or OneDrive?
There are known issues with VBA in files on SharePoint or OneDrive, it doesn't work.

 

[Spaztic]

It is...that may explain it. I guess maybe I'll just provide instructions on how to 'export' or 'save a copy' rather than going the VBA route. Thanks

 

[PeteWright]

I have an idea that may work. The problem with VBA on SharePoint/OneDrive is that the paths don't work, the code itself will run just fine.
What you could do is to map your OneDrive/SharePoint folder to a drive letter, let's say X:\
This can be done with a program called XSubst (which is old, but still works - and is portable): XSubst - Softpedia

Let me know if you have any further questions.

 

[Spaztic]

This worked for sharepoint for me...

Dim sPath As String, sName As String

sPath = ThisWorkbook.Path
sPath = ThisWorkbook.GetLocalPath(ThisWorkbook.Path)

sName = ThisWorkbook.Name
sName = Left(sName, InStrRev(sName, ".") - 1)

' Check to see if file exists
    Dim strFileExists As String
    Dim strFileName As String
    
    Dim tsfile As String
    
    strFileName = sName & ".pdf"   ' old   sNameBuildFilePath
    tsfile = sPath & "\" & strFileName

    strFileExists = Dir(tsfile)
    
    Dim AnswerYes As String
        
    If strFileExists <> "" Then
        MsgBox "Error: " & strFileName & " already exists"
       
        AnswerYes = MsgBox("Do you Wish to overwrite?", vbQuestion + vbYesNo, "File Exists")
         
        If AnswerYes = vbNo Then
            GoTo End Sub
        End If
        
    End If
  
ThisWorkbook.Save
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, fileName:=sPath & "\" & sName & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False

 

[rollis13]

So where's the difference with my one line code ?

If Not Dir(sPath & "\" & sName & ".pdf") = vbNullString Then If MsgBox("File already exists, overwrite ?", vbYesNo) = vbNo Then Exit Sub

    strFileName = sName & ".pdf"   
    tsfile = sPath & "\" & strFileName
    strFileExists = Dir(tsfile)
    If strFileExists <> "" Then
        MsgBox "Error: " & strFileName & " already exists"
        AnswerYes = MsgBox("Do you Wish to overwrite?", vbQuestion + vbYesNo, "File Exists")
        If AnswerYes = vbNo Then
            GoTo End Sub
        End If

You just added a couple of variables and flipped the criterion: mine was Not = vbNullString and yours is strFileExsistes = "".

 

[Spaztic]

Not sure but for whatever reason, it didn't work for me.