How can I iteratively modify excel filetypes using win32com and os?

Question

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

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
– cecilj, Commented May 13, 2019 at 17:34

Matt M · Accepted Answer · 2019-05-13 17:46:17Z

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)
    ....

answered May 13, 2019 at 17:46

Matt M

7102 gold badges7 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cecilj Over a year ago

Thank you very much, kicking myself for this one.

Matt M Over a year ago

No problem. Been there, sometimes you just need a pair of fresh eyes

Collectives™ on Stack Overflow

How can I iteratively modify excel filetypes using win32com and os?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related