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".

 

[Jette3173]

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".

Thank you!!!!

If you dont mind me asking a question, why we need path & "\"
I appreciate you so much Jeff!

 

[6StringJazzer]

The path must have a "\" before you add the filename. My code used

    path = ActiveWorkbook.path

but I guess I didn't include that line of code in my example above. This does not have the "\" at the end so I had to add it.

In your original code you used
"R:\Destination File Path\"
which includes it, so it doesn't need to be added.

 

[Jette3173]

Oh okay!
Thank you for explaining that! I had seen it before but it didn't make sense to me, I always thought in terms of including the "\" in the file path

Thank you again for your help!