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, 2022 in Others by Kithuzzz
• 38,010 points
388 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, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,700 points
454 views
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,440 points
1,856 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, 2022 in Others by gaurav
• 23,260 points
1,376 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, 2022 in Others by gaurav
• 23,260 points
1,773 views
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,740 points
955 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
3,287 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, 2022 in Others by gaurav
• 23,260 points
578 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, 2022 in Others by Edureka
• 13,670 points
811 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, 2022 in Others by narikkadan
• 63,700 points
2,202 views
0 votes
1 answer
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