VBA for Autosaving a file to .pdf BUT not allowing overwrite if file already exists

[alex900_6]

Hi,

My knowledge of VBA is patchy at best but i have found my around the below but experiencing issues!

I have a PO log spreadsheet that when opened on the click of a button will generate a new PO number. When I run the autosave it will save the file as a PDF into the exact designated location however, if the file name (PO Number) already exists it simply overwrites the file with the new version. Is there anyway i can ad a prompt "PO already in use" should the file already exist or continue should it not exist?? . i.e. I don't want to override at all!

Any help would be really appreciated.
Many thanks

Here's my current coding;

Sub Autosave()
Dim vDir

pdfname = ActiveSheet.Range("Q2")
vDir = "\\cdhnas\cdh\Reports\Internal PO Log\PO pdf's\"
fileSaveName = vDir & pdfname

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

MsgBox "PDF File Saved (Reports\Internal PO Log\PO pdf's)"
End Sub

[maniacb]

Try this code

Please Login or Register  to view this content.

Note: Welcome to the Forum, please use code tags around the code you posted(edit your posts, highlight your code, and press the # in bar above the edit box)

[alex900_6]

Thanks for the quick response (and for the advice, i'm new!!).....however it didn't work!!

I tried switching the code round as below; it kind of made more sense to me but in coding world probably the opposite!

Please Login or Register  to view this content.

But i get the opposite now, i.e. "The file exists"

[maniacb]

Try the code this way

Please Login or Register  to view this content.

[alex900_6]

Still no joy!!!.......just keeps over writing the previous save!!

Sorry to be a pain

[maniacb]

Another approach, also checking for pdf extension

Please Login or Register  to view this content.

[alex900_6]

YES!!!!......perfect, it's worked :-)

Many thanks for your help, Excel is so frustrating but so satisfying at the same time!!

Thanks again

[maniacb]

If that takes care of your original question, please select*Thread Tools*from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[alex900_6]

Understood....just done that so again thanks, i'll now close the thread as solved.

As you say, these are only things i'll pick up as i get more experienced with the forum.

Thanks again

[maniacb]

Your welcome,