Excel VBA Save As (No Overwrite / Clear Macros)

Have a question on Excel VBA save as functionality. The following basically grabs the filename entered by the user from a cell in the workbook and saves the workbook as a non-macro enabled workbook (xlsx).

Please Login or Register  to view this content.

It works, but was wondering what is the best way modify to account for the issues below:

1. I do not want the user to be able to overwrite existing files. So if the file exists I would like the to be prompted to change the file name.

2. The Save As xlsx will drop all the active macros. But it does not do this till the user closes the file. The problem is I have a "Workbook_BeforeClose" sub within the original workbook. This is only supposed to run on the original document. But it is running when the new file is closed. Is there a way to clear the active macros after the save as?

for issue no. 1 - i guess you can save it as read only file

Figured out the answer to number 2. I placed an if statement in the "Workbook_BeforeClose" sub to check if the file name matches the template. If not, it bypasses the function that is not needed.

Please Login or Register  to view this content.

For number 1, "Application.DisplayAlerts = False" is bypassing the prompt for saving without macros as intended. But it is also bypassing the overwrite existing file prompt. Is there maybe a different way to get around the macro prompt? Or possibly an if statement that checks "Filename" before it proceeds with the save?

Appreciate your help on this!

Figured it out. The answer was to use FSO to check for the file name. I have placed the code below in case anyone is looking for a similar solution.

Please Login or Register  to view this content.

If anyone has a way I could improve upon either solution I would appreciate the feedback.

Hi there,

Here's another possible approach - it uses Dir rather than FileSystemObject to determine whether or not the file already exists:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps.

Regards,

Greg M

Thanks for your help!

One more question I ran into on this. The users will be on a local network or connected to a VPN. When testing this I tried to run while not connected to the VPN. It freezes up excel.

Is there a way to display an error message if the folder is unreachable?

Hi again,

The following code will generate an error message if the workbook cannot be saved:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M