VBA to Promt if PDF exists and to overwrite - Close Existing File first

[bdouglas1011]

I have an Excel sheet that I convert to PDF --here is Example of the name after PDF creates"Morning Report_08.16.14" , When it runs it just overwrites the file automatically if one was already on that date - I would like it to prompt to overwrite & if they choose 'yes' to overwrite the file and if not they can change the name If the new one is in addition to the one that already exists and I want it to save the new file with in the same folder.

Also when the current code is run if the PDF was left opened it stops the macro - Is it possible to see if that file is open and if so close it down so the code will work or is it possible to overwrite the file when open.

Please Login or Register  to view this content.

[patel45]

Please Login or Register  to view this content.

[bdouglas1011]

That worked great, thank You How would I add that if they choose to hit cancel then it cancels? Right now if they do anything else beside enter "Y" it makes new file...Can it be modified to just be "yes" "no" "Cancel" - anything other than that gives message you must choose Y,N or Cancel.

Yes = Overwrite file / No = Makes the new time stamped file / Cancel = Exits entirely.

Also

Is it possible that after it knows the file exists and it prompts if you want to overwrite & if you choose overwrite then it makes sure the PDF is closed so it can overwrite

Right now you get a runtime error and the procedure stops until the user manually closes the file.

Maybe If it cant close automatically then it would prompt a different box saying that "the PDF seams to be open can not overwrite please close PDF and rerun." then exit

That way they would not get the run-time error saying could not save.....