Convert .xlsm file to .xlsx file on a folder

[Trisha Sarkar]

Hi,

I am trying the below VBA to convert almost 40 files in a folder on my desktop from .xlsm format to .xlsx format.
I want the .xlsx files on the same folder I have the macro files and then also delete the .xlsm files using the same VBA. But, i am getting compline error while running this macro. Please can someone help me.

Sub RenameXLSMtoXLSX()
Dim Files As String, LRow As Integer
Files = Dir("U:\Desktop\Stats compile\*.xlsm")
MyPath = "<U:\Desktop\Stats compile\*.xlsm>"
Application.ScreenUpdating = False

Do While Files <> ""
Application.DisplayAlerts = False
Workbooks.Open Filename:=MyPath & Files
ActiveWorkbook.SaveAs Filename:=MyPath & Left(Files, InStrRev(Files, ".")) & "xlsx", FileFormat:=xlOpenXMLWorkbook
SetAttr MyPath & Files, vbNormal
ActiveWorkbook.Close SaveChanges:=False

Files = Dir
Loop
Application.ScreenUpdating = True

End Sub

 

[Marc L]

Hi,​

​

no compilation error on my side ‼​

​

But the execution should certainly fail according to the bad content of the variable MyPath so just correct it …​

 

[Joe4]

Welcome to the Board!

Additionally, do the files actually have VBA code in them?
If so, simply renaming them as "xlsx" will not work. You need to actually remove the VBA code from each one before saving as an "xlsx".

 

[igold]

Out of curiosity, why do you want to convert the file extension. By default Excel will save files with the .xlsx extension, unless of course the file contains a macro, in which case Excel will prompt you to save the file with the .xlsm extension. Even a file saved with the .xlsm extension that does not contain a macro would be completely transparent to the user upon opening.

That said, if the files with the .xlsm do contain macros you will unable to change the extension.

 

[Marc L]

You need to actually remove the VBA code from each one before saving as an "xlsx".

Wrong as the SaveAs method removes any VBA procedure from all the workbook project when saved to .xlsx format, just try manually …​

 

[Joe4]

Wrong as the SaveAs method removes any VBA procedure from all the workbook project when saved to .xlsx format, just try manually and see …

Sorry, it will work, but it requries a bit more information than you are providing.
I get a pop-up box telling me that the file has VBA code and I need to click "Yes" if I wish to continue and save it as a macro free workbook.
So, if you do not account for this in your VBA code, you are going to get a lot of pop-ups requiring manual intervention.

You can add the following line before your "SaveAs" command to suppress the warning and continue:

    Application.DisplayAlerts = False

and then you can turn them back on afterwards with:

    Application.DisplayAlerts =True

 

[Marc L]

​

As the main issue is related in post #2 …​

 

[Joe4]

As the main issue is related in post #2 …

But not the only one, which is why I started off my reply with "Additionally..."

 

[Trisha Sarkar]

Welcome to the Board!

Additionally, do the files actually have VBA code in them?
If so, simply renaming them as "xlsx" will not work. You need to actually remove the VBA code from each one before saving as an "xlsx".

The files i am trying to convert are all macro enabled and they do have VBA codes in them. So, i need to first convert those into xlsx format and then compile the data into one file. I have included this code, ActiveWorkbook.SaveAs Filename:=MyPath & Left(Files, InStrRev(Files, ".")) & "xlsx", FileFormat:=xlOpenXMLWorkbook and i thought this will save the files in xlsx format and that will not have any VBA in it.

Welcome to the Board!

Additionally, do the files actually have VBA code in them?
If so, simply renaming them as "xlsx" will not work. You need to actually remove the VBA code from each one before saving as an "xlsx".

 

[Trisha Sarkar]

Sorry, it will work, but it requries a bit more information than you are providing.
I get a pop-up box telling me that the file has VBA code and I need to click "Yes" if I wish to continue and save it as a macro free workbook.
So, if you do not account for this in your VBA code, you are going to get a lot of pop-ups requiring manual intervention.

You can add the following line before your "SaveAs" command to suppress the warning and continue:

    Application.DisplayAlerts = False

and then you can turn them back on afterwards with:

    Application.DisplayAlerts =True

I have given that command in my macro

Application.DisplayAlerts = False