excel saveas with yesterday date and without macros-CodePudding

i've read many similar problems like my but still cant solve one thing.

So i got a VBA code that save my file in the same folder with my file name that i want & yesterday date - i did it by writing file name in cell U8 with today() function ("myfilename today()-1") (couldn't do it normally by VBA code, always had error 400 so i took a shortcut like that and it's working) and now the problem is i don't wanna have any macros in this new saved file because it must be in xlsx format - ofc i add code to macro to changed new saved file format to .xlsx but it results as a error to open new file because it got macros and the format to open it's wrong so you cant even open it. Also tried some other results from stackoverflow but any of them worked for me so that's why im asking here to help.

My code:

Dim path As String

Dim filename1 As String



path = ThisWorkbook.path & "\"     'Same path as current project that the User opened.

filename1 = Range("U8").Text

Application.DisplayAlerts = False

ActiveWorkbook.SaveCopyAs Filename:=path & filename1

Application.DisplayAlerts = True

CodePudding user response：

Try this code:

Sub SubMacroFreeSave()
    
    'Declarations.
    Dim path As String
    Dim filename1 As String
    Dim file As Variant
    
    'Settings path as current project that the User opened.
    path = ThisWorkbook.path & "\"
    
    'Setting filename1 as name of the current project that the User opened followed by the yesterday's date.
    filename1 = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - Len(Split(ThisWorkbook.Name, ".")(UBound(Split(ThisWorkbook.Name, ".")))) - 1) & " " & Excel.WorksheetFunction.Text(Now - 1, "dd-mm-yyyy")
    
    'Turning off the screen updating.
    Application.ScreenUpdating = False
    
    'Turning off the display alerts.
    Application.DisplayAlerts = False
    
    'Saving a copy of the file as xlsm.
    ActiveWorkbook.SaveCopyAs Filename:=path & filename1 & ".xlsm"
    
    'Setting file as the copy just created.
    Set file = Application.Workbooks.Open(path & filename1 & ".xlsm")
    
    'Saving the copy again as a macro-free file.
    file.SaveAs Filename:=path & filename1 & ".xlsx", _
                FileFormat:=xlWorkbookDefault, _
                CreateBackup:=False
    
    'Deleting the first copy of the file.
    Kill path & filename1 & ".xlsm"
    
    'Closing the file (the macros will still be avaiable until the file is closed).
    file.Close
    
    'Turning on the display alerts.
    Application.DisplayAlerts = True
    
    'Turning on the screen updating.
    Application.ScreenUpdating = True
    
End Sub