Skip to content
Advertisement

Python: Open Excel file with win32

My code was working yesterday but today when I tried running it again it threw me an error and I’ve try to figure this out but I couldn’t. Hope you can help me out here. Thanks much!

import fnmatch
import os
import scipy as s
import pandas as pd
import win32com.client as win32 

for file in os.listdir('.'):
    if fnmatch.fnmatch(file, '*.xlsx'):
    print(file)
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    wb = excel.Workbooks.Open(file)
    excel.Visible = False
    excel.DisplayAlerts = False
    wb.DoNotPromptForConvert = True
    wb.CheckCompatibility = False

    ws = wb.Worksheets('Exec Summary')
    ws.Activate
    canvas = ws.Shapes

    for shp in canvas:
        if shp.TextFrame.Characters:
            print("Comments Found")
            print(shp.TextFrame2.TextRange)
            break
            wb.Close(True)

And this is the error the system threw:

—> 11 wb = excel.Workbooks.Open(file)

com_error: (-2147352567, ‘Exception occurred.’, (0, ‘Microsoft Excel’, “Sorry, we couldn’t find ZPC.xlsx. Is it possible it was moved, renamed or deleted?”, ‘xlmain11.chm’, 0, -2146827284), None)

I have checked and confirm that the file is already in the directory. Have you guys encountered this before?

Advertisement

Answer

Can you try doing a print of your file and see what comes up?

What is likely happening here is that you are not indicating the directory path correctly in os.listdir('.').
os.listdir('.') will check the default directory in which the python script you are running is located in.
If it is the same folder, it will work.
But if it is not, you have to specifically include the exact path of the folder. Example of how your exact filepath should look like:

filepath = r'C:UsersyournamehereDesktopyourfolderhere\' + 'ZPC.xlsx'  

Example of how your os.listdir should look like:

dirpath = r'C:UsersyournamehereDesktopyourfolderhere\'  
for file in os.listdir(dirpath):  
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    wb = excel.Workbooks.Open(dirpath + file)

Also, if you wish to have a more elegant way of joining dirpath and file
You can use

os.path.join(dirpath, file)
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement