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
 
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 …​
Yes, even i am getting a compile error on MyPath itself. i am not so sure how to fix that. I am sorry i am not so proficient in VBA.
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
This should do what you want:
VBA Code:
Sub RenameXLSMtoXLSX()

    Dim Files As String, MyPath As String, LRow As Integer, NewName As String
    Dim WB As Workbook

    Application.ScreenUpdating = False

    MyPath = "U:\Desktop\Stats compile\"
    Files = Dir(MyPath & "*.xlsm")

    Do While Len(Files) > 0
        Set WB = Workbooks.Open(MyPath & Files)
        NewName = Left(Files, InStrRev(Files, ".")) & "xlsx"
        Application.DisplayAlerts = False
        WB.SaveAs Filename:=MyPath & NewName, FileFormat:=xlOpenXMLWorkbook
        WB.Close SaveChanges:=False
        Kill (MyPath & Files)
        Application.DisplayAlerts = True
    
        Files = Dir
    Loop

    Application.ScreenUpdating = True

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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
Back
Top