convert xlsx files to xls inside folders and subfolders in Excel VBA or Python

Question

I am trying to convert xlsx files into xls using vba macro or python code.So far,I could get some help on converting files in a single folder using the below code:

Sub ProcessFiles()
Dim Filename, Pathname, saveFileName As String
Dim wb As Workbook
Dim initialDisplayAlerts As Boolean

Pathname = ""  
Filename = Dir(Pathname & "*.xlsx")
initialDisplayAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
Do While Filename <> ""
    Set wb = Workbooks.Open(Filename:=Pathname & Filename, _
                            UpdateLinks:=False)
    wb.CheckCompatibility = False
    saveFileName = Replace(Filename, ".xlsx", ".xls")

    wb.SaveAs Filename:=Pathname & saveFileName, _
              FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
              ReadOnlyRecommended:=False, CreateBackup:=False

    wb.Close SaveChanges:=False
    Filename = Dir()
Loop
Application.DisplayAlerts = initialDisplayAlerts
End Sub

I am now trying to generalize this to all subfolders inside the given folder.

On a larger note,I am trying to build a macro which does the following:

Run this in batch mode where the parent folder is constant.
Process the code at background and put all the converted files in respective folders.

For example, Max is my main folder inside which there may be Med ,Det,Vis,Liv sub-folders.Inside each subfolder,there will be thousands of xlsx files which need to be converted and placed at the same location where the parent file is stored.

Please help.

Thanks, Maddy

Please ask one concrete question per question. E.g. "how to recursively enumerate xlsx files in excel-vba", "how to get user input in excel-vba". Only ask for one programming language per question. — kay

Martin Evans Martin Evans · Accepted Answer · 2016-01-28T12:52:57

You could do this in Python as follows. This will take all the xlsx files from a single folder and write them using the same name in xls format:

import win32com.client as win32
import glob
import os

excel = win32.gencache.EnsureDispatch('Excel.Application')

for excel_filename in glob.glob(r'c:\excel_files_folder\*.xlsx'):
    print excel_filename
    wb = excel.Workbooks.Open(excel_filename)
    wb.SaveAs(os.path.splitext(excel_filename)[0] + '.xls', FileFormat=56, ConflictResolution=2) 

excel.Application.Quit()

Where 56 is the format number to be used, these are listed on the Microsoft website.

To do this on a whole directory structure, you could use os.walk as follows:

import win32com.client as win32
import os

excel = win32.gencache.EnsureDispatch('Excel.Application')

for dirpath, dirnames, filenames in os.walk(r'c:\excel_files_folder'):
    for filename in filenames:
        name, ext = os.path.splitext(filename)
        if ext == '.xlsx':
            wb = excel.Workbooks.Open(os.path.join(dirpath, filename))
            wb.DoNotPromptForConvert = True
            wb.CheckCompatibility = False
            excel.DisplayAlerts = False
            wb.SaveAs(os.path.join(dirpath, name + '.xls'), FileFormat=56, ConflictResolution=2) 

excel.Application.Quit()

convert xlsx files to xls inside folders and subfolders in Excel VBA or Python

1 Answers