A marco to check if file exists and ask to save over

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:14 PM GMT

Question

The code listed below will take a workseet and save it as a PDF based on the text in 3 cells within the sheet. What I would now like it to do, is see if the file name already exists, and if so it will ask me if I wanted to save over or cancel. If the file name does not exist I do not want it to show any type of message, just save the file.

I assume this is going to use a nested If statement before the save function, but I can't seem to get the syntax correct.

Any and All help is greatly appreciated!

' SaveMe Macro ' Sub SaveMe() Dim Name1 As String Dim Name2 As String Dim Name3 As String Dim TotalName As String Dim MyDir As String Dim wbk As Workbook

'establish the filename to use With ThisWorkbook.Worksheets("Summ")

   Name1 = .Range("C15").Value
   Name2 = .Range("I15").Value
   Name3 = .Range("J15").Value

End With MyDir = "C:\Documents and Settings\n236scg\Desktop\" TotalName = MyDir & Name1 & Name2 & Name3 & ".pdf"

'perform the save operation

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=TotalName

' End Sub

Answer

IF DIR(TotalName)="" then

  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=TotalName

Else

  IF Msgbox(DIR(TotalName) & " Already exists -- overwrite?",Vbyesno)=VBYes then 
      Application.DisplayAlerts = False
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=TotalName
  End if

End If

Advertisement

©2017 eLuminary LLC. All rights reserved.