File saved as .xlsx Retaining the VB Modules

[Shiseiji]

The goal is to have a file without any modules and if no changes are made, when closed gives no warning to save changes.
My file has a large number of modules. The last module is to save the file as a .xlsx and I understood doing so should remove all the modules. The modules are being retained until the file is closed. When closing the file a warning is rendered about saving changes.

Applicate any assistance!
Ron

Sub SaveFinalasXLSX_m()
' properties name = SaveFinalasXLSX
    With Application
      .DisplayAlerts = False
      .ScreenUpdating = True
      .DisplayStatusBar = True
      .Calculation = xlManual
      .OnTime Now + TimeSerial(0, 0, 0.1), "m_ClearStatusBar"
      .StatusBar = "Saving as a regular Excel file."
      .OnTime Now + TimeSerial(0, 0, 10), "m_ClearStatusBar"
      .ScreenUpdating = False
    End With 'Application
'
    Dim WbPath As String
    Dim varPath As String
    Dim fPath As String
    Dim FileP As String 'file path entered into Range("FinalSave") on the [Start] worksheet.
    Dim ThisWb  As Workbook
'
    Set ThisWb = ActiveWorkbook
    With ThisWb
'
    varPath = ThisWorkbook.Path
    fPath = Replace(varPath, "\", "/") & "/Temp/"
    WbPath = ThisWorkbook.Path
'
    FileP = Range("FinalSave")
    ThisWb.SaveAs FileP & "AD_Listing_" & Format(Now, "mm-dd-yyyy  hmmAM/PM") & ".xlsx", FileFormat:=51
'
   With Application
      .DisplayAlerts = False
      .ScreenUpdating = True
      .DisplayStatusBar = True
      .Calculation = xlManual
      .OnTime Now + TimeSerial(0, 0, 0.1), "m_ClearStatusBar"
      .StatusBar = "Completed saving as a regular Excel file."
      .OnTime Now + TimeSerial(0, 0, 10), "m_ClearStatusBar"
      .ScreenUpdating = False
    End With 'Application
    End With 'ThisWb
End Sub

 

[Fluff]

How about adding these two lines

    FileP = Range("FinalSave")
    Application.DisplayAlerts = False
    ThisWb.SaveAs FileP & "AD_Listing_" & Format(Now, "mm-dd-yyyy  hmmAM/PM") & ".xlsx", FileFormat:=51
    Application.DisplayAlerts = True

 

[Shiseiji]

Fluff, appreciate your taking a look at this. The code below resulted in the message below when I immediately tried to closed the file:

Sub SaveFinalasXLSX_m()
' properties name = SaveFinalasXLSX
    With Application
      .DisplayAlerts = False
      .ScreenUpdating = True
      .DisplayStatusBar = True
      .Calculation = xlManual
      .OnTime Now + TimeSerial(0, 0, 0.1), "m_ClearStatusBar"
      .StatusBar = "Saving as a regular Excel file."
      .OnTime Now + TimeSerial(0, 0, 10), "m_ClearStatusBar"
      .ScreenUpdating = False
    End With 'Application
'
    Dim WbPath As String
    Dim varPath As String
    Dim fPath As String
    Dim FileP As String 'file path entered into Range("FinalSave") on the [Start] worksheet.
    Dim ThisWb  As Workbook
'
    Set ThisWb = ActiveWorkbook
    With ThisWb
'
    varPath = ThisWorkbook.Path
    fPath = Replace(varPath, "\", "/") & "/Temp/"
    WbPath = ThisWorkbook.Path
'---Replaced with Test 1 per https://www.mrexcel.com/board/threads/file-saved-as-xlsx-retaining-the-vb-modules.1190890/--
'    FileP = Range("FinalSave")
'    ThisWb.SaveAs FileP & "AD_Listing_" & Format(Now, "mm-dd-yyyy  hmmAM/PM") & ".xlsx", FileFormat:=51
'==Test 1==
    FileP = Range("FinalSave")
    Application.DisplayAlerts = False
    ThisWb.SaveAs FileP & "AD_Listing_" & Format(Now, "mm-dd-yyyy  hmmAM/PM") & ".xlsx", FileFormat:=51
    Application.DisplayAlerts = True
' Save Change Warning rendered when immediately closing file.
'==End Test==
'
   With Application
      .DisplayAlerts = False
      .ScreenUpdating = True
      .DisplayStatusBar = True
      .Calculation = xlManual
      .OnTime Now + TimeSerial(0, 0, 0.1), "m_ClearStatusBar"
      .StatusBar = "Completed saving as a regular Excel file."
      .OnTime Now + TimeSerial(0, 0, 10), "m_ClearStatusBar"
      .ScreenUpdating = False
    End With 'Application
    End With 'ThisWb
End Sub

 

[Fluff]

Hadn't noticed that you are already turning off alerts, so there is no need for those two lines I added.
That said you are not turning alerts on again, however if it's asking you if you want to save, then something has changed in the file, or it needs to be recalculated.

 

[jkpieterse]

You could add this to the ThisWorkbook module:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Right(ThisWorkbook.Name, 4) = "xlsx" Then
        'Assume file was saved as xlsx, hence we do not want the save changes question
        ThisWorkbook.Saved = True
    End If
End Sub

 

[Shiseiji]

Hadn't noticed that you are already turning off alerts, so there is no need for those two lines I added.

That said you are not turning alerts on again, however if it's asking you if you want to save, then something has changed in the file, or it needs to be recalculated.

No success with adding "calculate" just before the save or adding Jan's module to the ThisWorkbook module. The workbook is retaining the modules, but if I close it without saving and re-open it, the modules are gone.
Strange . . .

R

 

[Fluff]

The modules & code will not be removed until the workbook is closed.

 

[Shiseiji]

The modules & code will not be removed until the workbook is closed.

Thanks. Always learning . . .

Ron

 

[Fluff]

Glad we could help & thanks for the feedback.