How to look in all folders in directory to change file extensions of excel file

0 votes

I have a lot of.xls excel files spread over several directories. These should be changed to have the.xlsx file extension. If I specify the folder file location, my code functions perfectly. However, I would like to change it so that it searches through all folders in the directory and instantly converts any.xls files to.xlsx. I feel a bit stuck. Below is my code:

    Dim strCurrentFileExt   As String
    Dim strNewFileExt       As String
    Dim objFSO              As Object
    Dim objFolder           As Object
    Dim objFile             As Object
    Dim xlFile              As Workbook
    Dim strNewName          As String
    Dim strFolderPath       As String

    strCurrentFileExt = ".xls"
    strNewFileExt = ".xlsx"

    strFolderPath = "C:\myExcelFolders"
    If Right(strFolderPath, 1) <> "\" Then
        strFolderPath = strFolderPath & "\"
    End If

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.getfolder(strFolderPath)
    For Each objFile In objFolder.Files
        strNewName = objFile.Name
        If Right(strNewName, Len(strCurrentFileExt)) = strCurrentFileExt Then
            Set xlFile = Workbooks.Open(objFile.Path, , True)
            strNewName = Replace(strNewName, strCurrentFileExt, strNewFileExt)
            Application.DisplayAlerts = False
            Select Case strNewFileExt
            Case ".xlsx"
                xlFile.SaveAs strFolderPath & strNewName, XlFileFormat.xlOpenXMLWorkbook
            Case ".xlsm"
                xlFile.SaveAs strFolderPath & strNewName, XlFileFormat.xlOpenXMLWorkbookMacroEnabled
            End Select
            xlFile.Close
            Application.DisplayAlerts = True
        End If
    Next objFile
Nov 24 in Others by Kithuzzz
• 20,660 points
22 views

1 answer to this question.

0 votes

Loop Through All Subfolders Using VBA

Dim strCurrentFileExt   As String
Dim strNewFileExt       As String
Dim objFSO              As Object
Dim objFolder           As Object
Dim objFile             As Object
Dim xlFile              As Workbook
Dim strNewName          As String
Dim strFolderPath       As String

strCurrentFileExt = ".xls"
strNewFileExt = ".xlsx"

strFolderPath = "C:\myExcelFolders"
If Right(strFolderPath, 1) <> "\" Then
    strFolderPath = strFolderPath & "\"
End If

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.getfolder(strFolderPath)
For Each SubFolder In objFolder.SubFolders
  For Each objFile In objFolder.Files
    strNewName = objFile.Name
    If Right(strNewName, Len(strCurrentFileExt)) = strCurrentFileExt Then
        Set xlFile = Workbooks.Open(objFile.Path, , True)
        strNewName = Replace(strNewName, strCurrentFileExt, strNewFileExt)
        Application.DisplayAlerts = False
        Select Case strNewFileExt
        Case ".xlsx"
            xlFile.SaveAs strFolderPath & strNewName, XlFileFormat.xlOpenXMLWorkbook
        Case ".xlsm"
            xlFile.SaveAs strFolderPath & strNewName, XlFileFormat.xlOpenXMLWorkbookMacroEnabled
        End Select
        xlFile.Close
        Application.DisplayAlerts = True
    End If
  Next objFile
Next
answered Nov 24 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How to change the background color of AppBar in Flutter?

Hi@akhtar, You can add backgroundColor keyword in your ...READ MORE

answered Aug 12, 2020 in Others by MD
• 95,380 points
1,429 views
0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
529 views
0 votes
1 answer

How to import excel file in Oracle SQL live

Hello, there are a few steps You'll ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
588 views
0 votes
0 answers
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
451 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,218 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
69 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
205 views
0 votes
1 answer

How to get sum of all matches of HLOOKUP in Excel?

Consider: =SUMPRODUCT((A1:E1="apple")*(A2:E2)) To include more ...READ MORE

answered Oct 16 in Others by narikkadan
• 37,660 points
71 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP