Macro to Save any .XLS (Excel 20003) to a .XLSX (Excel 2007-2010) to the original directory

Sub SavesXLStoXLSXReopen ()

‘Macro made by Joe Tandle of NYIM Training, LLC. 2012. Contact. for edits or questions.

‘Saves the XLS as an XLSX
‘Defines Variables
Dim nameOfCurrentWorkbook As Variant
Dim nameOfCurrentWorkbookPath As Variant

‘Finds the name of the current workbook and takes off the .xls
nameOfCurrentWorkbook = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) – 4)

‘finds the current path of the workbook
nameOfCurrentWorkbookPath = ActiveWorkbook.Path
‘saves as an XLSX without dialog
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & “\” & nameOfCurrentWorkbook, FileFormat:=xlOpenXMLWorkbook
Application.EnableEvents = False

‘Closes Workbook
ActiveWorkbook.Close False

‘Reopens the currently saved XSLX workbook
Workbooks.Open (nameOfCurrentWorkbookPath & “\” & nameOfCurrentWorkbook & “.xlsx”)

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *