We help IT Professionals succeed at work.

Convert  XLSX to XLSM in a folder

seistante
seistante asked
on
1,081 Views
Last Modified: 2012-05-08
Dear Experts,

I would like to convert all XLSX files in a folder to XLSM.
There's no subfolders.

Reason being is that I'm using successfully this Ron de Bruin code,
but now i need to send a worksheet with code and would like to avoid loads of clicks.

http://www.rondebruin.nl/copy4.htm
4) Copy a worksheet to all workbooks in a folder

Searched a lot and attached Brad's code looks as the closest but I'm unable to adjust it to do it preferably in one go. Such as find xlsx and replace with xlsm.

Thanks for your attention

Pedro

 
Sub ConvertToXLSX()
'Converts Excel files in a user-specified folder to the .xlsx format with macros removed
Dim f As String, flPath As String
Dim bStatus As Boolean
flPath = Application.GetSaveAsFilename(FileFilter:="Excel files, (*.xls*); *.xlsx", _
    Title:="Please pick any file in folder to be converted, then click 'Save'")
If flPath = "False" Then Exit Sub
 
flPath = Left(flPath, InStrRev(flPath, "\"))
f = Dir(flPath & "*.xls*")
bStatus = Application.DisplayStatusBar  'Remember if the status bar is being displayed
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.EnableEvents = False        'Don't run event macros when workbooks open
Application.DisplayAlerts = False       'Don't display alert messages
On Error GoTo errhandler
Do Until f = ""
    If Right(f, 1) <> "x" Then
        Application.StatusBar = "Now processing file: " & f
        With Workbooks.Open(f)
            .SaveAs flPath & Left(f, InStrRev(f, ".")) & "xlsx", FileFormat:=51      '.xlsx file format
            .Close SaveChanges:=False
        End With
        Kill flPath & f                 'Delete the original workbook
    End If
    f = Dir
Loop
errhandler:
    Application.StatusBar = False           'Clear the status bar text
    Application.DisplayStatusBar = bStatus  'Restore the status bar display to its previous state
    Application.ScreenUpdating = True
    Application.EnableEvents = True         'Run event macros when workbooks open
    Application.DisplayAlerts = True    'Display alert messages
End Sub

Open in new window

Comment
Watch Question

Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Brad,
Thank you so much!!
It works perfectly.
Picked one file and the lot was converted!
Very, very pleased with your help.
Pedro
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Pedro,
Thanks for the kind words and grade!

Brad

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.