1

I am trying to iterate over a directory that contains a number of xlsm (Macro-enabled excel) files, opening each file and saving as an xlsx file. Ideally, I would have three directories, one that holds my script, one where the unmodified files live, and another where the modified files will be saved.

The directory, macro_dir, contains: 'test1.xlsm', 'test2.xlsm', 'test3.xlsm'

I cannot get my code to work when I loop through each file in the directory. Code chunk A (below) works: In the excel.Workbooks.Open method, the file variable was an absolute path to one of the xlsm files and the wb.SaveAs method contained an absolute path with the new filename and extension.

A. Working code:

import win32com.client as win32

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

wb = excel.Workbooks.Open(r'C:\Users\Documents\macro_dir\test1.xlsm')
excel.DisplayAlerts = False
wb.DoNotPromptForConvert = True
wb.CheckCompatibility = False
wb.SaveAs(r'C:\Users\Documents\macro_dir\output1.xlsx', FileFormat=51, ConflictResolution=2)
excel.Application.Quit()

B. My attempt at turning this into a loop:

import os
import win32com.client as win32

dir = r'C:\Users\Documents\macro_dir'

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

for file in os.listdir(dir):
    wb = excel.Workbooks.Open(file)
    excel.DisplayAlerts = False
    wb.DoNotPromptForConvert = True
    wb.CheckCompatibility = False
    wb.SaveAs(file[:-4] + 'xlsx', FileFormat=51, ConflictResolution=2)
    excel.Application.Quit()

I expected code chunk B (above) to modify each .xlsm file and save them as .xlsx to the same directory. However, the code produces the following error:

com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', "Sorry, we couldn't find test1.xlsm. Is it possible it was moved, renamed or deleted?", 'xlmain11.chm', 0, -2146827284), None)

EDIT: I cannot simply change the file extension, the file format must be converted by opening the file in excel and saving as an .xlsx

cecilj
  • 142
  • 1
  • 8
  • Possible duplicate of [Changing file extension in Python](https://stackoverflow.com/questions/2900035/changing-file-extension-in-python) – David Sidarous May 13 '19 at 17:28
  • 1
    It seems that those answers just change the file extension, rather than the format. This will not work with macro-enable excel files as I have tried it manually. – cecilj May 13 '19 at 17:34

1 Answers1

1

Its your for loop that is causing the error, not win32com. You aren't using the full path.

This should fix it.

for file in os.listdir(dir):
    file = os.path.join(dir, file)
    wb = excel.Workbooks.Open(file)
    ....
Matt M
  • 691
  • 2
  • 6
  • 17