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?

 

[mikecox39]

MrTeeny,

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.

I found the answer to the question here. How to insert a macro button to run macro in Excel?

Thanks for all your help, I really appreciate it.

 

[Fluff]

Glad to help & thanks for the feedback

 

[mikecox39]

I'm back

XL won't run this macro for some reason.

I get a different error message depending on which computer I run it on.

I put both error messages in my Dropbox.


Dropbox - Public


When I put a macro in a spreadsheet and save the workbook, the macro get saved right along with the workbook right?

 

[Fluff]

On your Desktop your missing the last few lines of code

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

On your laptop check what your macro security settings are

 

[mikecox39]

Here's the code I have on both machines now, just to be consistent.

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


    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
End If


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

I got "Block If without end If". So I went back to MrTinny's code in message #15 and noticed that the End If line was missing between Exit Sub and Xit. But now I'm getting "This macro has failed".

But at least it's progress!

As for the
"Macro may not be available in the workbook", it's there so why would I get this message
"Macros may be disabled" I searched around for how to make macro's available but didn't find an answer that answered the question. The only difference between the settings in that I didn't have "Show message bar" checked. Could you be a little more specific about this problem?

 

[mikecox39]

Ok I've worked this problem out, for the most part.

The Quick Access shortcut is working now but I don't know what I did to fix it, it just started working.

I discovered the path was wrong on the Laptop but my desktop was setup when the cloud was called SkyDrive, and now it's called OneDrive. It's listed as OneDrive in the folder Tree but in the path it's still Skydrive, so I changed it to Skydrive and it appears to be working.

There are still a couple wrinkles but I've figured out how to step through the macro and that's how I figured out what I fixed so far.

I just don't want you to try and address this issue until I've figured out just how well it's working. But it very late and I'm turning in. I'll post again when I've smoothed out all the wrinkles and ask any questions I may still have then.

 

[Fluff]

Not sure why your getting that message as both the if & Else statements contain Exit Sub, but try adding the bit in red

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


    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
End If

[COLOR=#ff0000]Exit Sub[/COLOR]

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

As for macro security go to Tools>Macro>Security and select Medium.

 

[mikecox39]

I've figured it out

The spreadsheet is stored on OneDrive, and is opened from there, so the first Save saves the file there, to the Dir it was opened from. There should be no need to ChDir, anywhere in this macro.


The SaveAs line provided the path and the filename, so no ChDir command, and that path is to the C: drive. Here is my modified code and it's working perfectly now!

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


If Environ("UserName") = "mikec" Then




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


    Application.DisplayAlerts = True
    Exit Sub




Else


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


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

I got that error message again when I clicked my "$" Quick Assess Macro icon so I removed it and added it back again, and the error stopped. So it appears to have something to do with changes in the macro maybe??? But until I know for sure I know this is a good workaround.

There is just one more thing (-: How do I get the macro to close the workbook when it is finished?

Thanks so much for your very patient help, I so appreciate it.

 

[Fluff]

Try

Option Explicit

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


If Environ("UserName") = "mikec" Then




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




Else


    ActiveWorkbook.Save
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\michael\Documents\Office\XL\Budget Personal.xlsm", FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Application.DisplayAlerts = True
[COLOR=#0000ff]    ThisWorkbook.Close[/COLOR]
    Exit Sub
End If


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

 

[mikecox39]

Try

[COLOR=#0000ff] ThisWorkbook.Close[/COLOR]
    
[/QUOTE]

This has been a real learning experience, among other things I've become familiar with the debugging process.
Thanks again for all you help.