Python: Group Excel Sheets & Save Them As Separate Workbooks

𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
This tutorial will show you how to group unique Excel sheets and save them as separate Excel Files/Workbooks. The solution is entirely written in Python. No VBA is required. In particular, we will be using the xlwings library.

📝 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀:
Download the sample files & Python Code:

✅ 𝗦𝗢𝗨𝗥𝗖𝗘 𝗖𝗢𝗗𝗘:
from pathlib import Path # Standard Python Module
import xlwings as xw # pip install xlwings

"""
Iterate over all excel files in a given source directory.
For each worksheet within the excel files create a new workbook.
The name of the new workbook will be equal to the worksheet's name.
If the workbook already exists, append the worksheet to the existing workbook.
"""

SOURCE_DIR = "Month_End_Data"
OUTPUT_DIR = "Output"

excel_files = list(Path(SOURCE_DIR).glob("*.xlsx"))

# Create a dict with the excel file name & path:
# excel_outputs = {'filename1':filepath1,
# 'filename2':filepath2}
output_paths = list(Path(OUTPUT_DIR).glob("*.xlsx"))
excel_outputs = dict(zip(output_names, output_paths))

for excel_file in excel_files:
wb = xw.Book(excel_file)
# Check, if the sheet name already exsits in the output folder (as a separate wb)
# Check, if that wb also contains out sheet (the wb's name)
# If not, we will copy/append the sheet to the existing wb
else:
# Create a new wb and copy the sheet to this new wb
# Afterwards, add the new file & path to the dict 'excel_outputs'
wb_tmp = xw.Book()
# Only quit the excel instance if no other wb is open
else:

𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀

☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?

[CodingIsFun]

*I will be here in the comments section. For any issues, please provide your exact error message, and I will try to help.*

[KhalilYasser]

Awesome as usual. Thank you very much.

[noeldoller]

Awesome excel automation as always. Appreciate if you can do more excel automation Sven. Copying range of Cell from multiple workbook to a single workbook where the date is updated daily and copy the source data to the destination workbook with new dates. thank you

[ikke]

I try to save xlwings modified wb under the same name but it says save failed. Used time.sleep() to wait a bit but did not help me. Workbook.save(path) works only path is different filename. Could you please help?

[rickwize]

I'm using an Anaconda Jupyter notebook, but when I attempt to use "from pathlib import path" I get an error that it cannot be imported?

[noeldoller]

Hi Sven, I tried to copy and run the code in my Pycharm and I got the error:

Can you please help me resolving the error, please
in line 42:

(base) C:\PYTHON PROJECTS\Excel split_data.py
Traceback (most recent call last):
File "split_data.py", line 42, in <module>

AttributeError: 'Sheet' object has no attribute 'copy'

[ermalgashimramori]

Pretty good tutorial, maybe slow down the last part "if and else part". As always great content.

[tuandhtl]

I have error at line: for excel_file in excel_files. The Syntax error: invalid syntax^
Please help me!!!!

[raza]

Hello bro I have three folders there are many files in the three folders, so I want to say that all three folders are files in this, all the same names and I want these three files and get inside the tape inside the septeration and all of them are not the same.

[Bzman]

Is there a way to format all the new workbooks ?