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

 

[mole999]

xlsx does not allow for macros to run, and xlsm does. an extension name change causes an error

The essence of code is

    ActiveWorkbook.SaveAs Filename:="C:\Users\******\Desktop\Book2.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 

[Dodettesky]

xlsx does not allow for macros to run, and xlsm does. an extension name change causes an error

The essence of code is

    ActiveWorkbook.SaveAs Filename:="C:\Users\******\Desktop\Book2.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Thank you very much for taking the time to reply! I really appreciate it! Pardon my ignorance, but I am not able to figure out the complete code. I only learned VBA from Bill Jelen's book VBA and Macros: Microsoft Excel 2010, but I'm not quite at the level yet to write this particular code. I created an order form that has a macro button to reset the form. I do not want the processed purchase orders we have saved to be opened by my colleagues and for them to accidentally hit the Reset Form button and end up saving it without the data. As well, our shared drive is almost full, and the xlsm files take up more space. They have saved them as xlsm, and I want to save them as xlsx, but there's hundreds of them in the folder. I would like to run a macro that does that for all of them. Your help will be very much appreciated! Thanks!

 

[Perpa]

Thank you very much for taking the time to reply! I really appreciate it! Pardon my ignorance, but I am not able to figure out the complete code. I only learned VBA from Bill Jelen's book VBA and Macros: Microsoft Excel 2010, but I'm not quite at the level yet to write this particular code. I created an order form that has a macro button to reset the form. I do not want the processed purchase orders we have saved to be opened by my colleagues and for them to accidentally hit the Reset Form button and end up saving it without the data. As well, our shared drive is almost full, and the xlsm files take up more space. They have saved them as xlsm, and I want to save them as xlsx, but there's hundreds of them in the folder. I would like to run a macro that does that for all of them. Your help will be very much appreciated! Thanks!

Dodettesky,
I found this code on this forum some time back. It does what you are asking, but be sure you want to delete the xlsm files before you uncomment the 'Kill' line...You may want to run the macro as is and then move the xlsm files to another location. If you uncomment the 'Kill' line your macros are gone. Make sure that is what you want. The code should be saved in a standard code module. You can access it by opening the spreadsheet, then enabling macros, and then pressing 'Alt + F8' and selecting the name from the list, then 'Run'. The spreadsheet with this code should exist in another location than the directory where you are converting the files to xlsx.
Hope this is helpful.
Perpa

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
    'Change to whatever directory you have then  '\*.xlsm'
    Files = Dir("C:\Users\Username\Documents\Myfolder\*.xlsm")
    MyPath = "C:\Users\Username\Documents\Myfolder\"
    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

        'Kill MyPath & Files           'Uncomment this line if you want to delete the xlsm files

    Files = Dir
    Loop
    Application.ScreenUpdating = True

End Sub

 

[Dodettesky]

Thank you very much, Perpa! I'll try it in the office tomorrow.

Much appreciation,

Lourdes

 

[amaresh achar]

Dodettesky,
I found this code on this forum some time back. It does what you are asking, but be sure you want to delete the xlsm files before you uncomment the 'Kill' line...You may want to run the macro as is and then move the xlsm files to another location. If you uncomment the 'Kill' line your macros are gone. Make sure that is what you want. The code should be saved in a standard code module. You can access it by opening the spreadsheet, then enabling macros, and then pressing 'Alt + F8' and selecting the name from the list, then 'Run'. The spreadsheet with this code should exist in another location than the directory where you are converting the files to xlsx.
Hope this is helpful.
Perpa

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
    'Change to whatever directory you have then  '\*.xlsm'
    Files = Dir("C:\Users\Username\Documents\Myfolder\*.xlsm")
    MyPath = "C:\Users\Username\Documents\Myfolder\"
    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

        'Kill MyPath & Files           'Uncomment this line if you want to delete the xlsm files

    Files = Dir
    Loop
    Application.ScreenUpdating = True

End Sub

Hi Perpa,

This code does a great job...!! Thanks..!!

Can you please give a suggestion to modify the code to extract input file path and output file path from a cell value..!? (Paths are different for input and output file)

Thank you very much in advance,
Amaresh

 

[jolivanes]

Re: our shared drive is almost full

XLSX or XLSB: Why to save a workbook in binary format

Facts & Myths About Binary Excel Workbooks: Learn the advantages & disadvantages of Excel's binary file format XLSB

www.spreadsheet1.com

Or have a template and save finals as PDF or xlsx

 

[Perpa]

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

 

[amaresh achar]

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

Wow That works great...!! Thanks Perpa....!!!

 

[jolivanes]

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