I made a tool in Python that right-clicks an Excel file and divides it into files for each sheet.

background purpose

I received a request to divide each sheet in an Excel file as a separate file. Originally I think it is a VBA project, but it is troublesome to open the Excel file and start the macro. Or rather, I don't want to write VBA in the first place. So, I would like to make a tool in Python that right-clicks an Excel file and divides it into files for each sheet.

Installation of external libraries

You will need a library for working with Excel and a library for creating file paths. It is not standard, so install it in advance.

$ pip install win32com.client
$ pip install pathlib
$ pip install openpyxl

code

excel_split.py

import os
import glob
import win32com.client
import pathlib
import openpyxl
import sys

excel_dir = pathlib.Path(sys.argv[1]).parent #The path of the folder where the target Excel file is saved
input_file = str(sys.argv[1]) #Convert Excel file path to string

#Get a list of sheets(Use openpyxl)
book = openpyxl.load_workbook(input_file)
sheets_list = book.sheetnames

for i in range(len(sheets_list)):

    #Start Excel
    app = win32com.client.Dispatch("Excel.Application")
    app.Visible = False
    app.DisplayAlerts = False

    #Import workbook in Excel
    sheet_no = i + 1 #The excel sheet adds 1 for 1 start
    book = app.Workbooks.Open(input_file)
    book.WorkSheets(sheet_no).Activate()
    new_book = app.Workbooks.Add()

    #Save for each sheet
    new_file = sheets_list[i] + '.xlsx'
    path_obj = pathlib.Path(excel_dir, new_file) #Convert to path object
    output_file = path_obj.resolve() #Convert to absolute path
    book.Worksheets(sheet_no).Copy(Before=new_book.Worksheets(1)) #Copy the specified sheet to a new Excel file
    new_book.SaveAs(str(output_file)) #Save file

    #Exit Excel
    app.Quit()

Create bat file

The py file is not kicked when placed in the sendto folder. Create a bat file to kick the created excel_split.py. * PowerShell is also acceptable.

excel_split.bat

python \\Where to save the Python file\excel_split.py

Save to Sendto folder

Save the created bat file in the sendto folder. The sendto folder can be displayed by pressing the [Win] and [R] keys at the same time and entering "shell: sendto" in the search box.

How to Use

Right-click the Excel file → Send → click excel_split.bat to create a file for each sheet in the Excel file.

Supplement: If you want to make an EXE and distribute it to users

If you want to distribute it to users, you can start it without a bat file by putting the EXE version in the sendto folder as well. The following article introduces the method of EXE conversion.

https://qiita.com/sw1394/items/2123fa9aaecb766657e5