CODEWITHSUNDEEP
pythonexcelautomationfile-handlingpywin32
Lucas Brito
Lucas Brito

Reputation: 21

How to Close All Excel Files in a Specific Directory Using Python?

I'm working on automating some tasks using Python, and one of the requirements is to close all Excel files that are currently open in a specific directory. I often have multiple Excel files open from the same folder, such as C:\MyExcelFiles, and I'd like to close them programmatically to ensure they don't interfere with further processing.

These Excel files are opened after extracting data from SAP. Since there are many files and SAP always opens them by default, I would like to close them automatically to streamline the workflow.

So far, I have only found solutions that close all open Excel files on the system, but ideally, I want to close just the files from the specific directory. I couldn't find a clear example that addresses cl

osing only the files from a given folder.

Does anyone have any suggestions or perhaps a script that can achieve this?

Thanks for your help!

I tried using pywin32 to close open Excel files, but the code I found closes all Excel files on the system, regardless of their location. I was expecting to find a solution that closes only the files from a specific directory, but couldn't achieve this yet.

# Delay de 5 segundos
        time.sleep(5)
        
        # Nome da planilha que queremos verificar
        excel_file_name = f"{valor.replace('*', '')}.xlsx"
        
        # Função para verificar se um arquivo Excel está aberto
        def is_excel_file_open(file_name):
            # Itera pelos processos ativos
            for proc in psutil.process_iter(['pid', 'name']):
                try:
                    # Verifica se o processo é do Excel
                    if proc.info['name'] == 'EXCEL.EXE':
                        for file in proc.open_files():
                            # Se o arquivo está aberto pelo Excel, retorna True
                            if file_name in file.path:
                                return proc
                except (psutil.NoSuchProcess, psutil.AccessDenied, psutil.ZombieProcess):
                    pass
            return None
        
        # Função para fechar o arquivo se estiver aberto
        def close_excel_file(file_name):
            process = is_excel_file_open(file_name)
            if process:
                print(f"Fechando o arquivo: {file_name}")
                process.terminate()  # Envia o comando de término para o processo
                process.wait()       # Espera até o fechamento completo do processo
                print("Arquivo fechado com sucesso!")
            else:
               print(f"O arquivo {file_name} não está aberto.")
        
        # Caminho completo do arquivo Excel
        excel_path = os.path.join("C:\TEMP\Avaliacao", excel_file_name)
        
        # Fecha o arquivo se estiver aberto
        close_excel_file(excel_path)

Upvotes: 2

Views: 48

Answers (1)

Charles Knell
Charles Knell

Reputation: 583

I suggest the following script:

import signal
import psutil
import os

# *** IMPORTANT ***
# Excel does not normally open separate processes for separate workbooks.
# To open a separate process for each workbook, after opening the first one, hold down
# the alt key when clicking on your Excel icon, for example in the start menu, until you see
# the message "Do you want to start a new instance of Excel?". Click Yes and open the next
# workbook. This would start a new process for each workbook.
#
# You could use one Excel process for the workbooks in the "specific directory" and another
# process for all of the other workbooks.

# If a process is an Excel process then if it has opened a workbook in the "specific directory"
# then it will be terminated by executing the following code
specific_directory = 'C:\\Users\\charl\\PYTHON\\stackoverflow_questions_2'


def get_open_files(process1):
    try:
        open_files = process1.open_files()
        return [f.path for f in open_files]
    except psutil.AccessDenied:
        return "Access denied. You might need to run the script with elevated permissions."
    except Exception as e:
        return f"An error occurred: {e}"


excel_processes = []
for process in psutil.process_iter(['pid', 'name']):
    if process.info['name'] and 'EXCEL' in process.info['name'].upper():
        excel_processes.append(process)

for process in excel_processes:
    files = get_open_files(process)

    if isinstance(files, list):
        for file in files:
            if specific_directory in file and file.endswith('.xlsx'):
                print(f"Terminating Excel process {process.pid} due to open file: {file}")
                os.kill(process.pid, signal.SIGTERM)
                break  # Stop after killing this process
    else:
        print(files)

Upvotes: 1

Related Questions