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]

When you post to the thread you'll see a line above your typing with font's etc , right at the end you'll send the # sign , you click that to add the code or simply add {CODE} your code {/CODE} where { are the square brackets.

 

[mikecox39]

The code icon is the # symbol

Oh, ok, thanks, that will make is easier (-:
I just ran this

[COLOR=#333333] On Error GoTo Xit[/COLOR]    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
 [COLOR=#333333]End Sub[/COLOR]

and it seemed to work! The only problem is XL remains open so I have to add a close code that doesn't deliver a prompt, since I've already saved it.

I want looking for the close command in the editor but when I opened the editor to figure out what the Close code was I found the macro file missing.

I don't understand what is going on with Module 1.

I've linked several screen shots in the hopes that you can you figure out what I'm doing that makes there be 2 project files with the same name and different Module 1 code, or why they both disappeared and were replaced by some old Module 1 code, or why Modules go missing and why there is a module with a ctrl-shift-M shortcut that I never created?

I'm thinking it has something to do with how I am saving my workbook file. I don't don't understand what's happening to the modules or why they come and go.

https://app.box.com/s/z5z0rcp4xgd4vwfe1wgn

 

[mikecox39]

Got it, thanks. Don't know why I did notice it when I hovered

 

[mikecox39]

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

So with that line, I simply respond with mikec [for my laptop] or michael [for my desktop]?

Great idea, thanks!

 

[MrTeeny]

Yes the Workbook_BeforeSave event fires just before you save the worksheet so simply you press save and excel will fire any coding within the event. So all you need to do is put your current saving macro within it and it'll automatically save your worksheet backup without the need for you to run a separate macro, it'll just run automatically before each save.

Something like this in the ThisWorkbook object not a module

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

Application.DisplayAlerts = False
On Error GoTo Xit


If Environ("UserName") ="mikec" Then
    
  
    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
    Exit Sub


Else
'WHATEVER ADDRESSES YOU'RE USING FOR THE OTHER ACCOUNT


    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
    Exit Sub

End If






Xit:
    Application.DisplayAlerts = True
    MsgBox "Backup failed"

End Sub

 

[Fluff]

Hello Mike
Just realised I gave you some really bad advice in post#6

[COLOR=#0000ff]Xit:
    Application.DisplayAlerts = True[/COLOR]

should be something like this

Xit:[COLOR=#0000ff]
    MsgBox "This macro has failed"
    [/COLOR]Application.DisplayAlerts = True[COLOR=#0000ff]
[/COLOR]

Otherwise you will be unaware that the macro failed.
HTH

 

[mikecox39]

Hello Mike
Just realised I gave you some really bad advice in post#6

[COLOR=#0000ff]Xit:
    Application.DisplayAlerts = True[/COLOR]

should be something like this

Xit:[COLOR=#0000ff]
    MsgBox "This macro has failed"
    [/COLOR]Application.DisplayAlerts = True[COLOR=#0000ff]
[/COLOR]

Otherwise you will be unaware that the macro failed.
HTH

My macro has gone through a couple iterations since that post. I'm not sure where to add that line now. I'm guessing it will need to be added in two places. Here's what I have now.

Sub BackupBudget()'
' BackupBudget Macro
'
'      On Error GoTo Xit
   Application.DisplayAlerts = False
On Error GoTo Xit




If Environ("UserName") = "mikec" Then
    
  
    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
    Exit Sub




Else
'WHATEVER ADDRESSES YOU'RE USING FOR THE OTHER ACCOUNT




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


End Sub

 

[mikecox39]

MrTeeny,

I have added this macro to my spreadsheet but have asked Fluff to explain where to put "the Macro has failed" Msgbox. Then I'll try it.

btw On my laptop, I inserted an icon on the Quick Access bar and assigned this macro to it. I want to do the same to the spreadsheet on my desktop but I can't remember what the option is called. Was looking for "icon" but that isn't an option on the list. I know it's an icon because was able to change the look of it to a dollar sign.

 

[Fluff]

Here you go

Sub BackupBudget() '
' BackupBudget Macro
'
   Application.DisplayAlerts = False
On Error GoTo Xit




If Environ("UserName") = "mikec" Then
    
  
    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
    Exit Sub




Else
'WHATEVER ADDRESSES YOU'RE USING FOR THE OTHER ACCOUNT




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

Exit Sub

Xit:
    MsgBox "This macro has failed"
    Application.DisplayAlerts = True
End Sub

 

[mikecox39]

Here you go

Thanks so much!