Convert .xlsm file to .xlsx file on a folder

Trisha Sarkar

New Member
Joined
May 18, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,963
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,695
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,963
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
    Application.DisplayAlerts = False
and then you can turn them back on afterwards with:
VBA Code:
    Application.DisplayAlerts =True
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,963
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 18, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

New Member
Joined
May 18, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:
VBA Code:
    Application.DisplayAlerts = False
and then you can turn them back on afterwards with:
VBA Code:
    Application.DisplayAlerts =True
I have given that command in my macro

Application.DisplayAlerts = False
 

Forum statistics

Threads
1,141,203
Messages
5,704,942
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top