Open all Excel files in one folder and add sheets

Question

I am trying to open all Excel (.xlsx) files in one folder and add three sheets (named M1, M2 and M3). Then save it in the existing Excel file (not mandatory). Can you please help? Thanks.

from openpyxl import load_workbook
wb2 = load_workbook(r'C:\Users\alex\mob\830.xlsx')
wb2.create_sheet('M1')
wb2.create_sheet('M2')
wb2.create_sheet('M3')
wb2.save(r'C:\Users\alex\mob\830.xlsx')

This works for each Excel file, but I want to iterate/loop/do it for all files in one folder. All files are .xlsx

Does this answer your question? [How do I list all files of a directory?](https://stackoverflow.com/questions/3207219/how-do-i-list-all-files-of-a-directory) — Itération 122442, Feb 06 '23 at 12:24

score 1 · Answer 1 · answered Feb 06 '23 at 12:30

You can use glob to list all files ending with specific extensions in a given directory:

import glob
from openpyxl import load_workbook

files = glob.glob("/some/random/location/*.xlsx")
for file in files:
    wb2 = load_workbook(file)
    wb2.create_sheet('M1')
    wb2.create_sheet('M2')
    wb2.create_sheet('M3')
    wb2.save(file)

What glob.glob() does is - it returns an array of all files matching a specific search. In the current search, you are looking for all files with the .xlsx extension.

Note that this only looks at the extension, not the contents of the file, so if you have a simple test.txt file and rename it to test.xlsx, your program will likely crash.

score 0 · Answer 2 · answered Feb 06 '23 at 12:52

0

This answered my question! Thank you very much.

In order for my computer to read the folder, I changed the directory to:

files = glob.glob(r'C:\Users\alex\mob\*.xlsx')

answered Feb 06 '23 at 12:52

Alex

1

Open all Excel files in one folder and add sheets

2 Answers2