Group Excel Sheets & Save Them As Separate Workbooks
Introduction
In this tutorial, I will show you how to split each worksheet from multiple Excel files into separate workbooks. This can be particularly useful when managing financial data across different months. For instance, I have a folder containing several Excel files representing financial data for January through May. Each file contains various worksheets, including Sales, Profit, Networking Capital, and Headcount.
To begin, let’s take a look at the structure of our data:
- January data: Sales, Profit, Networking Capital
- February data: Sales, Profit, Networking Capital, Headcount
- March data: Sales, Profit, Networking Capital
- April data: Sales, Profit, Networking Capital
- May data: Sales, Profit, Networking Capital, Headcount
Now, to split these worksheets into individual workbooks, I’ve created a Python script using the xlwings library. This script will help automate the process. Let’s dive into the steps involved:
Step 1: Install Required Libraries
First, you need to install the xlwings library. Open your command prompt or terminal and run:
pip install xlwings
Step 2: Set Up the Script
Next, create a new Python file and import the necessary libraries:
from pathlib import Path
import xlwings as xw
Now, let’s define the source and output directories. If your current Python script is in the same directory as your Excel files, you only need to specify the folder name. Otherwise, you can enter the full path to the folder.
source_dir = "Month_End_Data"
output_dir = "Output"
Step 3: Iterate Over Excel Files
We need to iterate over all Excel files in the specified source directory. For each worksheet within those files, we will create a new workbook:
excel_files = list(Path(source_dir).glob("*.xlsx"))
Next, let’s create a dictionary to store the names and paths of the Excel files in the output folder:
excel_outputs = {}
Step 4: Copy Worksheets to New Workbooks
For each Excel file, open it and iterate through its worksheets:
for excel_file in excel_files:
wb = xw.Book(excel_file)
for sheet in wb.sheets:
For each worksheet, create a new workbook and copy the worksheet into it:
Step 5: Save the New Workbooks
After copying the worksheet, set the name of the new workbook and save it:
Step 6: Handling Existing Workbooks
To avoid overwriting existing workbooks, check if a workbook with the same name already exists in the output folder before creating a new one. If it does, append the worksheet to the existing workbook instead:
Step 7: Run the Script
Run the script to split the worksheets into separate workbooks. Once completed, you should see the individual workbooks in your output folder, each containing the respective data:
Conclusion
This tutorial has walked you through the process of splitting Excel worksheets into separate workbooks using Python. By automating this process, you can save time and effectively manage your financial data. Feel free to modify the script to suit your needs, and if you have any questions, don’t hesitate to ask!