Software

Ask something
Discussion Locked

Excel Macro help

0 helpful
8 replies

by nsohenick

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How can I write a macro to save a file and answer a command prompt asking me if I want to overwrite existing file?

Thanks

8 replies

by dklippert

You can just use File>Save As.
Here's a macro

Sub SaveandAsk()
ActiveWorkbook.SaveAs
End Sub

by firstpeter

This should work if you're going to "Save As" the workbook:

Sub SaveFile()
Dim strFileName, strPathName As String
Dim strDirFN As String

strPathnName = "C:\"
strFileName = "TestFile.xls"

strDirFN = Dir(strPathName & strFileName)

' If the file exists, KILL (delete) it.
If strDirFN = strFileName Then Kill strFileName

' If there is no file present (because we killed it), simply SaveAs.
ActiveWorkbook.SaveAs Filename:=strPathName & strFileName
End Sub

Simply saving a currently opened file as the same name should be:

Activeworkbooks.Save

which shouldn't prompt you to overwrite.

by nsohenick

That worked great. Thanks. I have another question with VBA and Excel. I'll make a new post for it.

by firstpeter

Sorry - I inadvertently misstated a piece:

If strDirFN = strFileName Then Kill strPathName & strFileName

Otherwise it will look for strFileName in the current active directory, not in the strPathName location.

by nsohenick

Sorry bad problem definition. I have that part working. I'd like to be able to have that question "Would you like to overwrite this file?" answered Ok or Yes automatically.

How can I program just hitting the enter key? I tried recording it, but it turned out to say select B5 or whatever I moved to next? It would be great if I could do a short count loop after the SaveAs macro and hit enter to overwrite in 5 seconds.

Thanks.

by nsohenick

I noticed. Thanks. But even after that is said and done when it comes time to close Excel, it asks you again do you want to save the changes. Is there anyway to avoid the follow up question?

This discussion is closed to new replies.