Adding SaveAs/SaveCopyAs to my Macro

[Jette3173]

I have been working on a long/multiple macro and have finally gotten it where I want and decided it would be nice to add the final save as to the end.
I have two options that I have put together but neither work fully and I'm not sure what I am missing.
Please help and thank you for any guidance anyone can provide!!!

My goal is to take the macro enabled wb from my desktop folder and save it as a typical .xlsx wb on a shared drive.



Option 1: This saves as a file not as an excel sheet.

    Dim wb As String
    wb = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
   
   
    ActiveWorkbook.SaveAs Filename:= _
        "R:\Destination File Path\" & wb, FileFormat:=51, _
    CreateBackup:=False
       
    MsgBox "The file has been saved"

Option 2: This saves as a .xlsx, but upon opening the file states the extension is currupted.

    Dim wb As String
    wb = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
   
   
    ActiveWorkbook.SaveCopyAs Filename:= _
        "R:\Destination File Path\" & wb & ".xlsx"

    MsgBox "The file has been saved"

 

[6StringJazzer]

Option 1:

This saves as a file not as an excel sheet.

I don't understand what this means. If you save an Excel workbook, it's a file. When I run this I get a prompt asking if I want to save the file without macros. If I answer Save and Erase Features, it is correctly saved as a .xlsx file. Can you give more details about what result you are getting?

Although it's not an error, I prefer to use the enumeration names for built-in constants rather than the numbers. It documents the code better:

    ActiveWorkbook.SaveAs Filename:= _
        "R:\Destination File Path\" & wb, FileFormat:=xlOpenXMLWorkbook, _
    CreateBackup:=False

Option 2:

SaveCopyAs does exactly that--saves an exact copy. You cannot use SaveCopyAs to save a .xlsm workbook as a .xlsx workbook. If you force the extension to be .xlsx, then when you try to open it Excel will realize it's a macro-enabled file with a .xlsx extension and treat it as invalid.

 

[Jette3173]

Hello Jeff,
Thank you for helping me on this.
I have tried to look into the difference of SaveAs and SaveCopyAs and somehow didn't realize SaveCopyAs is an exact copy... (I feel smart)
That makes complete sense though and explains the extension corruption.

What I mean by file, is that is the 'extension' it is outputting. I do not receive any prompt asking if I want to save without macros. It just saves then when I check my folder it shows what I have attached.
I do have to close the excel file to delete the 'file' that was saved.


No change for using:

    ActiveWorkbook.SaveAs Filename:= _
        "R:\HRD-HRIS\Compliance\2024\" & wb, FileFormat:=xlOpenXMLWorkbook, _
    CreateBackup:=False

 

[6StringJazzer]

Looks like you need to include the extension. Try this code, which I have tested successfully. Turning off alerts will force the file to be saved without prompting the user.

    Application.DisplayAlerts = False
    
    ActiveWorkbook.SaveAs Filename:= _
        path & "\" & Replace(ActiveWorkbook.Name, ".xlsm", ".xlsx"), FileFormat:=xlOpenXMLWorkbook, _
    CreateBackup:=False
    
    Application.DisplayAlerts = True

If you want to fix the files you already have I think you can just rename them to add ".xlsx".