After Saving a ".xlms" With VBA as ".xlsx" Excel Still Asks About Save Without Macros

[Shiseiji]

I use the code below to save a my .xlsm as a .xlsx file. However when I save the .xlsx file I still receive a warning about macos in the non-macro enabled workbook. Any assistance is appreciated.

Sub m_SaveWBasXLSX()
'
    With Application
      .ScreenUpdating = False 'True
      .EnableEvents = True 'False
      .DisplayAlerts = False
      .Calculation = xlManual 'xlCalculationAutomatic
    End With ' Application
    '
    Dim ThisWb As Workbook
    '
    Set ThisWb = ActiveWorkbook
    ChDrive "S:"
    ThisWb.SaveAs "S:\AD_Listing_" & Format(Now, "mm-dd-yyyy_hmmAM/PM") & ".xlsx", FileFormat:=51
    ThisWb.Save
    With Application
      .ScreenUpdating = True 'False
      .EnableEvents = True 'False
      .DisplayAlerts = False
      .Calculation = xlCalculationAutomatic 'xlManual
    End With ' Application

End Sub

 

[rlv01]

You can use Application.DisplayAlerts

  Application.DisplayAlerts = False
  ThisWb.SaveAs "S:\AD_Listing_" & Format(Now, "mm-dd-yyyy_hmmAM/PM") & ".xlsx", FileFormat:=51
  Application.DisplayAlerts = True

 

[rlv01]

Sorry, I just noticed that you use DisplayAlerts earlier in your code. Where are you getting the message? Because I don't get anything when I use

 Application.DisplayAlerts = False

 

[Joe4]

However when I save the .xlsx file I still receive a warning about macos in the non-macro enabled workbook

Why are you trying to do this?
"xlsx" is set aside specifically for Excel files WITHOUT VBA.
There are "xlsm" and "xlsb" extensions are the extensions for Excel files with VBA.

 

[rlv01]

If you are just trying to save a macro-free copy while keeping the source workbook open, this might work:

Sub m_SaveWBasXLSX()
    '
    Dim TempPath As String
    Dim ThisWb As Workbook, DestWB As Workbook
    
    Set ThisWb = ActiveWorkbook
    ChDrive "S:"
    TempPath = Environ("TEMP") & "\Temp_" & ThisWb.Name
    
    Application.DisplayAlerts = False
    ThisWb.SaveCopyAs (TempPath)
    Set DestWB = Application.Workbooks.Open(FileName:=TempPath)
    DestWB.SaveAs FileName:="S:\AD_Listing_" & Format(Now, "mm-dd-yyyy_hmmAM/PM") & ".xlsx", FileFormat:=51
    DestWB.Close
    
    Kill TempPath
    Application.DisplayAlerts = True
End Sub

 

[Shiseiji]

Why are you trying to do this?

I use the code save a my .xlsm as a .xlsx file so the file isn't a .xlsm. The users of the file don't need the VBA or need to try and figure out how to manage the security. The file remains open after the save as .xlsx. If I do anything to the file, or a date cell updates, then I need to again save the file with the current .xlsx format. VBA should be a non-issue in the .xlsx file. Yet I receive the warning.

 

[Shiseiji]

If you are just trying to save a macro-free copy while keeping the source workbook open, this might work:

Sub m_SaveWBasXLSX()
    '
    Dim TempPath As String
    Dim ThisWb As Workbook, DestWB As Workbook
   
    Set ThisWb = ActiveWorkbook
    ChDrive "S:"
    TempPath = Environ("TEMP") & "\Temp_" & ThisWb.Name
   
    Application.DisplayAlerts = False
    ThisWb.SaveCopyAs (TempPath)
    Set DestWB = Application.Workbooks.Open(FileName:=TempPath)
    DestWB.SaveAs FileName:="S:\AD_Listing_" & Format(Now, "mm-dd-yyyy_hmmAM/PM") & ".xlsx", FileFormat:=51
    DestWB.Close
   
    Kill TempPath
    Application.DisplayAlerts = True
End Sub

Thanks! If you have a moment, can you explain why forcing the file to save first in a temp location corrects the problem? I used code similar to what I posted for years without this problem.

Thanks!

Ron

 

[Joe4]

I use the code save a my .xlsm as a .xlsx file so the file isn't a .xlsm. The users of the file don't need the VBA or need to try and figure out how to manage the security. The file remains open after the save as .xlsx. If I do anything to the file, or a date cell updates, then I need to again save the file with the current .xlsx format. VBA should be a non-issue in the .xlsx file. Yet I receive the warning.

Then you either need to strip the VBA out of the the file before saving it, or you have to save a copy of it without the VBA code (like rlv01 shows).

I used code similar to what I posted for years without this problem.

Are you sure this wasn't using the old "xls" extension?
That old extension allowed for VBA, so could be used for workbooks both with and without VBA.

 

[rlv01]

Thanks! If you have a moment, can you explain why forcing the file to save first in a temp location corrects the problem? I used code similar to what I posted for years without this problem.

Thanks!

Ron

It's not so much the temp location, that's just good housekeeping for a temporary file that will eventually be deleted. Rather the key is making use of the Workbook object 'SaveCopyAs' method so that you can spawn off a macro-free .xlsx version without affecting, or closing the source workbook containing the macro.

 

[Shiseiji]

Then you either need to strip the VBA out of the the file before saving it, or you have to save a copy of it without the VBA code (like rlv01 shows).


Are you sure this wasn't using the old "xls" extension?
That old extension allowed for VBA, so could be used for workbooks both with and without VBA.

Yes, this one has been a .xlsx for years. It's in " continuous Improvement" as I try to learn better ways to do things. But I will go back through the versions to see if I simply didn't make a good note on a change.