How to convert xlsm files to xlsx in folder

[Dodettesky]

Hello,

I had no luck finding a post specifically on converting all xlsm files in one folder to xlsx using VBA. Can anyone post a link to an old post?

Thanks!

Dodettesky

 

[amaresh achar]

H

Goal: 'To modify the code to extract input file path from one cell and the output file path from a different cell'

Assuming the INPUT PATH value is in A1 in this format: "C:\Users\Username\Documents\Myfolder1\"
where Myfolder1 is where the files reside that you want to convert to xlsx,

INPath = cells(1,"A")

and Assuming the OUTPUT PATH value is in B2 in this format: "C:\Users\Username\Documents\Myfolder2\"

OUTPath = cells(2,"B")

NOTE: The INPUT and OUTPUT paths must both end with a backwards slash, "\".

Sub RenameXLSMtoXLSX()
    'Makes a copy in xlsx format (smaller file size) then deletes the xlsm file if you want to...see below
    Dim Files As String, LRow As Integer
    Dim INPath, OUTPath As String

    INPath = Cells(1, "A")        'cell A1 value similar to: C:\Users\Username\Documents\Myfolder1\
    Files = Dir(INPath & "*.xlsm")
    OUTPath = Cells(2, "B")    'cell B1 value similar to: C:\Users\Username\Documents\Myfolder2\
    Application.ScreenUpdating = False

    Do While Files <> ""
        Application.DisplayAlerts = False
        Workbooks.Open Filename:=INPath & Files
        ActiveWorkbook.SaveAs Filename:=OUTPath & Left(Files, InStrRev(Files, ".")) & "xlsx", FileFormat:=xlOpenXMLWorkbook
        SetAttr INPath & Files, vbNormal
        ActiveWorkbook.Close SaveChanges:=False
        'Kill INPath & Files           'Uncomment this line if you want to delete the xlsm files
    Files = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

Let me know how you make out.
Perpa

Hi Perpa...
I need one more suggestion...
I need to add prefix "PRT_MX" to all the converted .xlsx files... How do I do that..!?

 

[amaresh achar]

H

@ amaresh achar.
Just to help me understand the advantage. Could you tell us how much you saved with how many different workbooks.

Hi jolivanes,

I have tried about 25 files which gets converted about in 12 to 15 seconds...

I am moving towards a bigger Target about 100 to 600 files...

 

[Perpa]

"Hi Perpa...
I need one more suggestion...
I need to add prefix "PRT_MX" to all the converted .xlsx files... How do I do that..!?"

amaresh achar,
Try this...
Change this line of code:

ActiveWorkbook.SaveAs Filename:=OUTPath & Left(Files, InStrRev(Files, ".")) & "xlsx", FileFormat:=xlOpenXMLWorkbook

To this:

ActiveWorkbook.SaveAs Filename:=OUTPath & "PRT_MX" & Left(Files, InStrRev(Files, ".")) & "xlsx", FileFormat:=xlOpenXMLWorkbook

Perpa