Save Macro didn't answer "already exists" prompt

[mikecox39]

ActiveWorkbook.Save
ChDir "C:\Users\mikec\Documents\XL"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\mikec\Documents\XL\Budget Personal.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

I created this macro to backup my budget. It works, but when I created it I answered "Y" the "already exists" prompt. But that didn't appear to record because when I run it I am prompted to overwrite the file that "already exists".

How do I force the macro to overwrite the file without being prompted?

 

[MrTeeny]

Just turn off the alerts then turn them back

<code class="x-hidden-focus" style="box-sizing: border-box; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; font-size: inherit; padding: 0px; color: inherit; background-color: transparent; white-space: pre-wrap; border-radius: 0px;"> Application.DisplayAlerts = False



Application.DisplayAlerts = True</code>

 

[mikecox39]

Just turn off the alerts then turn them back

<code class="x-hidden-focus" style="box-sizing: border-box; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; font-size: inherit; padding: 0px; color: inherit; background-color: transparent; white-space: pre-wrap; border-radius: 0px;"> Application.DisplayAlerts = False

Application.DisplayAlerts = True</code>

Thanks for resending but I'm afraid I don't understand what your saying

Do you mean to insert the False statement at the top of the code and the True statement at the bottom, is that why there is that space in your answer?

 

[MrTeeny]

Sorry I should have been clearer try it with the alerts turned off before your save and it should go thru fine, just turns back on after the save is run otherwise they'll be set as off

[COLOR=#333333][FONT=Menlo][I]Application.DisplayAlerts = False[/I][/FONT][/COLOR]
[COLOR=#333333]ActiveWorkbook.Save[/COLOR]
[COLOR=#333333]ChDir "C:\Users\mikec\Documents\XL"[/COLOR]
[COLOR=#333333]ActiveWorkbook.SaveAs Filename:= _[/COLOR]
[COLOR=#333333]"C:\Users\mikec\Documents\XL\Budget Personal.xlsm", FileFormat:= _[/COLOR]
[COLOR=#333333]xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[/COLOR][COLOR=#333333][FONT=Menlo][I]Application.DisplayAlerts = True[/I][/FONT][/COLOR]

 

[mikecox39]

Sorry I should have been clearer try it with the alerts turned off before your save and it should go thru fine, just turns back on after the save is run otherwise they'll be set as off

[COLOR=#333333][FONT=Menlo][I]Application.DisplayAlerts = False[/I][/FONT][/COLOR]
[COLOR=#333333]ActiveWorkbook.Save[/COLOR]
[COLOR=#333333]ChDir "C:\Users\mikec\Documents\XL"[/COLOR]
[COLOR=#333333]ActiveWorkbook.SaveAs Filename:= _[/COLOR]
[COLOR=#333333]"C:\Users\mikec\Documents\XL\Budget Personal.xlsm", FileFormat:= _[/COLOR]
[COLOR=#333333]xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[/COLOR][COLOR=#333333][FONT=Menlo][I]Application.DisplayAlerts = True[/I][/FONT][/COLOR]

Ok, got it, thanks.

As I understand it this code turns the alerts off, so there will be no "already exists" alert then turns the alert back on when the macro is finished, so alerts with be active again.

I will add this to my, very small, bag of tricks, as I develop new macros.

This macro saves a backup of my budget to my laptop. But I can't use it on my desktop because my profile name isn't that same on my desktop. So I'm working on a Message Box with a prompt that I can use with the If Then function to direct the macro to select the appropriate path. I'm sure I'll have more questions

 

[Fluff]

Hello Mike
I would also recommend adding an error handler such as

    Application.DisplayAlerts = False
    
    [COLOR=#0000ff]On Error GoTo Xit[/COLOR]
    ActiveWorkbook.Save
    ChDir "C:\Users\mikec\Documents\XL"
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\mikec\Documents\XL\Budget Personal.xlsm", FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Application.DisplayAlerts = True
[COLOR=#0000ff]Exit Sub

Xit:
    Application.DisplayAlerts = True[/COLOR]

End Sub

otherwise if the macro should fail for any reason DisplayAlerts will still be turned off

 

[MrTeeny]

This macro saves a backup of my budget to my laptop. But I can't use it on my desktop because my profile name isn't that same on my desktop. So I'm working on a Message Box with a prompt that I can use with the If Then function to direct the macro to select the appropriate path. I'm sure I'll have more questions

You can pick up the logon username using Environ("UserName") so maybe it might be worth you using the logon name to direct your save address rather than a prompt.

There's also an event called Workbook_BeforeSave , stick the following in your ThisWorkbook object and you could amend it to fire off your backup routine automatically

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

MsgBox Environ("UserName")


End Sub

 

[mikecox39]

Hello Mike
I would also recommend adding an error handler such as

    Application.DisplayAlerts = False
    
    [COLOR=#0000ff]On Error GoTo Xit[/COLOR]
    ActiveWorkbook.Save
    ChDir "C:\Users\mikec\Documents\XL"
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\mikec\Documents\XL\Budget Personal.xlsm", FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Application.DisplayAlerts = True
[COLOR=#0000ff]Exit Sub

Xit:
    Application.DisplayAlerts = True[/COLOR]

End Sub

otherwise if the macro should fail for any reason DisplayAlerts will still be turned off

Good idea! Thanks.

I've noticed that Code can be isolated from the rest of the post, like links and quotes, etc., but I don't see a Code icon on the toolbar. Does that mean that members are adding the HTML code manually? I'm assuming that it can be cut and pasted, as is, into modules.

 

[Fluff]

Also if both files are stored in the same location you could use something like this

    On Error GoTo Xit
    PathName = ActiveWorkbook.Path
    ActiveWorkbook.Save
    ActiveWorkbook.SaveAs Filename:= _
    PathName & "\Budget Personal.xlsm", FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Application.DisplayAlerts = True
Exit Sub

Xit:
    Application.DisplayAlerts = True

End Sub

 

[Fluff]

The code icon is the # symbol