VBA Save As in XLSX Format

[scohn80215]

Hi,

I have the following VBA script that works perfectly. But it saves the file as an XLS file and I need it to save as an XLSX. I've tried just changed the "(*.xls)" to "(*.xlsx)" but that doesn't work for me. Please help! Thanks!!

Sub Save_File()
Dim file_name As Variant
file_name = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel file (*.xls), *.xls")
If file_name <> False Then
ActiveWorkbook.SaveAs Filename:=file_name
MsgBox "File Saved!"
End If
End Sub

 

[SpillerBD]

Hi,

I have the following VBA script that works perfectly. But it saves the file as an XLS file and I need it to save as an XLSX. I've tried just changed the "(*.xls)" to "(*.xlsx)" but that doesn't work for me. Please help! Thanks!!

Sub Save_File()
Dim file_name As Variant
file_name = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel file (*.xls), *.xls")
If file_name <> False Then
ActiveWorkbook.SaveAs Filename:=file_name
MsgBox "File Saved!"
End If
End Sub

Should be "*.xlsm"
m for Macro Enabled

 

[scohn80215]

That's not my issue. I don't want it to save in a macro enabled workbook. I want it to save as XLSX.

Should be "*.xlsm"
m for Macro Enabled

 

[Joe4]

If this macro is in the workbook you are saving, that is not possible. You cannot save an Excel file with macros to an XLSX. Excel won't let you.
If you want to save a copy of it without the Macros, then that is probably the way to do it, have your macro make a copy of the workbook and save that copy.

 

[scohn80215]

The code I originally presented will cause the user to SAVE AS the workbook into a non-macro-enabled workbook. At that time the macro will be disabled. Until that point when it is saved, it will still be macro enabled workbook.

The above code prompts the user to save it. At that point, they no longer need the macro.

Try running the above code in a XLSM notebook. It perfectly saves it to a XLS workbook. Now I need it to save to an XLSX workbook.

If this macro is in the workbook you are saving, that is not possible. You cannot save an Excel file with macros to an XLSX. Excel won't let you.
If you want to save a copy of it without the Macros, then that is probably the way to do it, have your macro make a copy of the workbook and save that copy.

 

[Joe4]

The code I originally presented will cause the user to SAVE AS the workbook into a non-macro-enabled workbook. At that time the macro will be disabled.

It is not a matter of having the VBA disabled or not, XLSX doesn't allow you to save a file with VBA code, period.

Try running the above code in a XLSM notebook. It perfectly saves it to a XLS workbook. Now I need it to save to an XLSX workbook.

That is because XLS allows VBA code in it. XLSX does not. That is the whole point of that extension - you KNOW that it cannot have VBA code in it, making it less of a security concern.
That is why they did away with the XLS extension in the newer versions.

The only way you are going to get the VBA code to save it in XLSX format is to actually remove the VBA code. If you remove the code, then there is nothing to run, unless you make a copy of the workbook and run the code on that (or store this code somewhere outside of the workbook you are trying to save).

 

[scohn80215]

The original file is in XLSM. It's prompting the user to SAVE AS into a XLSX file.

SAVE AS means that it is now saving as a separate file. The macro just gets you to that point.

Before responding - take that code, put it in a XLSM file, and run it. Then post back. I don't know how else to explain this.

With all do respect, please don't respond to posts if you are not an advanced Excel user. These responses are extremely novice notions of how VBA works.

It is not a matter of having the VBA disabled or not, XLSX doesn't allow you to save a file with VBA code, period.


That is because XLS allows VBA code in it. XLSX does not. That is the whole point of that extension - you KNOW that it cannot have VBA code in it, making it less of a security concern.
That is why they did away with the XLS extension in the newer versions.

The only way you are going to get the VBA code to save it in XLSX format is to actually remove the VBA code. If you remove the code, then there is nothing to run, unless you make a copy of the workbook and run the code on that (or store this code somewhere outside of the workbook you are trying to save).

 

[Joe4]

The original file is in XLSM. It's prompting the user to SAVE AS into a XLSX file.

I did test it. That is an incorrect statement. It prompts to save it as an XLS file (not XLSX), because that is how you have the code written.
An XLS file is an old Excel extension that DOES allow VBA code. So it is valid to save it like that.

SAVE AS means that it is now saving as a separate file. The macro just gets you to that point.

That is true, but I don't think you are understanding the full extent of what that means, and what it is doing.
It is trying to save the ENTIRE workbook, "as-is" (VBA CODE INCLUDED!!!), under a different name.
Changing the file extension does NOT drop the VBA code automatically. It needs to be removed from the workbook that is trying to be saved.
That has to be done either manually, or by copying just the worksheet data to another file (and not the VBA code along with it).

Bottom-line: You cannot do a SaveAs on any file that contains VBA code to the XLSX extension, as XLSX is the "Macro-Free" Workbook extension.
Everything you are experiencing supports this statement, does it not?

 

[Joe4]

If you take a look at PGC's code here, he shows you how you can save a COPY of the file to another workbook without the VBA code (note how you have to create a Copy first, like I was saying).
https://www.mrexcel.com/forum/excel-questions/740217-save-copy-without-vba-modules.html#post3639464