Excel keeps re-opening the workbook after macro has closed it.

[tewell53]

Sub SaveAndClose()
Dim TimePath As String                                                                     '/ Declaring file path name
Dim MyName As String                                                                       '/ Declaring file name
Current_date = Format(Now, "MMDDYY_hhmmss")                                                '/ Assigning date & Time to use in file name
MyName = ActiveWorkbook.Name                                                               '/ Assigning file name of current active workbook
TimePath = "G:\TECH\ib271t0o\MyBackups\" & MyName & "-" & Current_date & ".xlsm"           '/ Buliding full path name for backup file
    ActiveWorkbook.SaveAs Filename:=TimePath                                               '/ Saving backup file
    ActiveWorkbook.Close SaveChanges:=True                                                 '/ Saving any changes and closeing current active workbook
    Workbooks("PERSONAL.XLSB").Close SaveChanges:=False                                    '/ Closing PERSONAL workbook
End Sub

After this Marco completes Excel stays open and the particular workbook that this code is in re-opens. I do not want to run the code to quit Excel because the user may have other workbooks open.

 

[Jeffrey Mahoney]

First of all, closing somebody's Personal workbook is kinda wrong.

The macro you are showing exists in the workbook you are closing. If you have any more code after it closes, Excel will try to run that code by opening the workbook you just closed.

 

[tewell53]

Thanks, It is my workbook, but I understand your point. The code is associated with a button to save, backup and then close the workbook.

 

[tewell53]

Jeffery,

Sorry I understand your comment, but the workbook has a lot of code associated with it. How do I determine what code would be still running? I do have a timer running to display a digital clock.

My new Code:

Sub Backup()
    Dim originalName As String
    Dim newName As String
    Dim savePath As String

    ' Get the current workbook's name (including path)
    originalName = ActiveWorkbook.Name
    'MsgBox ActiveWorkbook.Name

    ' Extract the file name without extension
    newName = Left(originalName, InStrRev(originalName, ".") - 1)
    'MsgBox newName

    ' Add the current date in the format "ddmmmyyyy"
    newName = newName & "_" & Format(Now(), "yyyy-mm-dd_hhmmss")
    ' MsgBox newName
    
    ' Specify the save path (change this to your desired location)
    savePath = "G:\TECH\ib271t0o\MyBackups\"
    ' MsgBox savePath

    ' Save the copy with the new name
    ActiveWorkbook.SaveCopyAs savePath & newName & ".xlsm"
    'MsgBox savePath & newName
    
    ' Save and close both workbooks
      Application.OnTime startingTime, "MakingClock", , False
      ThisWorkbook.Close SaveChanges:=True
      Application.Quit
End Sub

 

[Jeffrey Mahoney]

With the code below, Application.quit is after you close the workbook. So, Excel is going to reopen the workbook to run the last line

ThisWorkbook.Close SaveChanges:=True
Application.Quit