Confirm before overqriting files - VBA

[broch]

Hi there,

I'm afraid I'm a bit of VBA newbie, so this is probably quite a straightforward thing, I hope that means someone can help me.

I've created a macro (code below) in an excel workbook that saves the workbook as a pdf file and names it according to the contencts of cell J1.

All works fine, the only problem is that if the filename already exists there is no pop up to warn the user before overwriting. The file is simply overwritten.

How do I ensure that the user is prompted before overwriting?

Here is my code:

 Sub SaveAsPDF()
    '
    ' SaveAsPDF Macro
    '
 
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            Range("J1").Value _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True
    End Sub

All help will be very much appreciated.

Thanks

 

[Domenic]

Assuming that the filename in J1 contains the extension ".pdf", try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] SaveAsPDF()

    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] Ans [color=darkblue]As[/color] [color=darkblue]Integer[/color]

    strPath = "C:\Path\"  [color=green]'Or ActiveWorkbook.Path[/color]
    
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"
    
    strFile = Range("J1").Value  [color=green]'Or strFile = Range("J1").Value & ".pdf"[/color]
    
    [color=darkblue]If[/color] Dir(strPath & strFile) <> "" [color=darkblue]Then[/color]
        Ans = MsgBox("File already exists.  Overwrite?", vbQuestion + vbYesNo, "Overwrite?")
        [color=darkblue]If[/color] Ans = vbNo [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & strFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=[color=darkblue]True[/color]
            
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[broch]

Hi Domenic,
Thanks for your help - much appreciated.
However, I can't seem to make it work.

When I run the code you suggest I get "Run Time Error 52 - Bad file name or number"

The cell J1 contains the full path including the .pdf extension.

Any idea what's going wrong?

Cheers

 

[Domenic]

In that case, try the following instead...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] SaveAsPDF()

    [color=darkblue]Dim[/color] PathAndName [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] Ans [color=darkblue]As[/color] [color=darkblue]Integer[/color]

    PathAndName = Range("J1").Value
    
    [color=darkblue]If[/color] Dir(PathAndName) <> "" [color=darkblue]Then[/color]
        Ans = MsgBox("File already exists.  Overwrite?", vbQuestion + vbYesNo, "Overwrite?")
        [color=darkblue]If[/color] Ans = vbNo [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PathAndName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=[color=darkblue]True[/color]
            
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[broch]

Thanks Domenic, you're a star.
This seems to work perfectly now.

I just wish I understood it all!

I'll get there slowly but surely.

 

[Domenic]

You're very welcome! Thanks for the feedback!

 

[broch]

I realise I might be pushing my luck, but I have a tiny little niggle that it would be great to iron out. It's not a big thing, but.....

The code falls down if there is a pdf file with the same name as 'PathAndName' open. Is there a way to make the code close the open file before continuing to save the new one?