General discussion

Locked

Excel Macro help

By Nsohenick ·
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

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel Macro help

by DKlippert In reply to Excel Macro help

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

Sub SaveandAsk()
ActiveWorkbook.SaveAs
End Sub

Collapse -

Excel Macro help

by Nsohenick In reply to Excel Macro help

Poster rated this answer

Collapse -

Excel Macro help

by FirstPeter In reply to Excel Macro help

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.

Collapse -

Excel Macro help

by Nsohenick In reply to Excel Macro help

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

Collapse -

Excel Macro help

by FirstPeter In reply to Excel Macro help

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.

Collapse -

Excel Macro help

by Nsohenick In reply to Excel Macro help

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.

Collapse -

Excel Macro help

by Nsohenick In reply to Excel Macro help

This question was closed by the author

Collapse -

Excel Macro help

by Nsohenick In reply to Excel Macro help

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?

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums