Macro: Different PDF file name on each save

October 10, 2012 at 04:16:32
Specs: Windows Vista
Hello, I'm still learning this Excel stuff. I can make a macro to save it as a PDF by saying its filename is "invoice 1" but everytime I click my macro button, it always overwrites it. (Maybe because its just a simple saving macro i guess). But is there a way around this overwriting? Please could anybody give me a step by step? Most grateful thankyou. Nathan.

See More: Macro: Different PDF file name on each save

Report •

#1
October 10, 2012 at 09:05:28
You can use Application.InputBox to have the macro ask you for the name each time:

Sub SaveAsPDF()
   MyFilename = Application.InputBox("Enter Filename")
    If MyFilename = False Then Exit Sub
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:="Z:\Your_Real_Path\" & MyFilename & ".pdf", _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

If you will always be saving this file as a PDF, you can use the BeforeSave event to run the code whenever you save the file, without using your macro button.


In the VBE, double-click on the ThisWorkbook Module.
Use the pull down next to General and choose Workbook.
Use the pull down to the right and choose BeforeSave.
Use the instructions from above to get this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   MyFilename = Application.InputBox("Enter Filename")
    If MyFilename = False Then Exit Sub
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:="Z:\Your_Real_Path\" & MyFilename & ".pdf", _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question