Friday, February 12, 2016

Covert All the XLS file into XLSX format from the selected folder

Sub Convert_XLS_TO_XLSX()
Dim fLname As String
Dim folderPath As String
Dim fExtn As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path & "\"
    .Title = "please select folder"
    .Show
    If .SelectedItems.Count = 0 Then
        MsgBox "folder not selected..", vbOKOnly + vbInformation, "Task Cancelled"
        Exit Sub
    Else
        folderPath = .SelectedItems(1)
    End If
End With

fLname = Dir(folderPath & "\")

While fLname <> ""
    fExtn = UCase(Right(fLname, Len(fLname) - InStrRev(fLname, ".")))
    If fExtn = "XLS" Then
        Workbooks.Open folderPath & "\" & fLname
        fLname = Left(fLname, InStrRev(fLname, ".")) & "xlsx"
        ActiveWorkbook.SaveAs folderPath & "\" & fLname, xlOpenXMLWorkbook
        ActiveWorkbook.Close True
    End If
    fLname = Dir
Wend

Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "all the XLS files has been converted into XLSX format", vbOKOnly + vbInformation, "File Conversion Done"

End Sub

No comments:

Post a Comment

Excel VBA to send email from excel with HTML table in a email body

'' Excel vba script to send email from outlook & email body in HTML table format. ''Write down email body in HTML tags...