Creating Unique PDF name using VBA in Excel

[dan_sherbs]

Hi,

I have a tool that produces a set of outputs in the form of a quote in a tab called "SalesQuotation".

On this worksheet, i have a button which when clicked runs the following VBA

Sheets("SalesQuotation").Range("A1:J70").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\" & Environ$("Username") & _
    "\Desktop\Customer_QUOTE.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True

This works fine which opens up the quote. However, whilst this is open & i click the button again i get a run time error relating to Document not Saved.

How can i prevent this? Will the filename have to be unique or will it need to overwrite the existing one?

Many Thanks
Dan

 

[AlphaFrog]

How can i prevent this? Will the filename have to be unique or will it need to overwrite the existing one?

Either would work. Which do you want: close and overwrite the previous file, or create a new file with a unique name?

Unique names base on date-time

Sheets("SalesQuotation").Range("A1:J70").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\" & Environ$("Username") & _
        "\Desktop\Customer_QUOTE[COLOR=#ff0000]" & Format(Now, " mmddyy hhmmss") & "[/COLOR].pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True

 

[dan_sherbs]

Thanks, how would you close and overwrite the previous file?

 

[AlphaFrog]

Thanks, how would you close and overwrite the previous file?

The simple solution is to trap the error and prompt the user to first close it.

Closing it from VBA is more problematic. It depends on your PDF viewer. I found this...
VBA Prompt to close PDF so file can be overwrtitten or close automatically [SOLVED]