Group Excel Sheets & Save Them As Separate Workbooks
Language:
This tutorial will show you how to group unique Excel sheets and 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.
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"))
output_names = [file.stem for file in output_paths]
excel_outputs = dict(zip(output_names, output_paths))
for excel_file in excel_files:
wb = xw.Book(excel_file)
for sheet in wb.sheets:
# Check, if the sheet name already exsits in the output folder (as a separate wb)
if sheet.name in excel_outputs:
# Check, if that wb also contains out sheet (the wb's name)
# If not, we will copy/append the sheet to the existing wb
wb_tmp = xw.Book(excel_outputs[sheet.name])
wb_tmp_sheets = [sheet.name for sheet in wb_tmp.sheets]
if wb.name not in wb_tmp_sheets:
sheet.copy(after=wb_tmp.sheets[0])
sht = xw.sheets.active
sht.name = wb.name
wb_tmp.save()
wb_tmp.close()
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()
sheet.copy(after=wb_tmp.sheets[0])
sht = xw.sheets.active
sht.name = wb.name
wb_tmp.sheets[0].delete()
output_path = Path(OUTPUT_DIR, sheet.name + ".xlsx")
wb_tmp.save(output_path)
excel_outputs[sheet.name] = wb_tmp.fullname
wb_tmp.close()
# Only quit the excel instance if no other wb is open
if len(wb.app.books) == 1:
wb.app.quit()
else:
wb.close()