In a previous post, I show how to append multiple csv files into one big file. In this post, I show how to loop over
Let’s assume you have a set of Excel files that have the same structure (e.g., 4 worksheets in the same order). Below I show a Python script to extract one particular worksheet from the files and create one workbook with all those worksheets. Although it does not matter for this script, let’s assume our files have names such as 1980data.xlsx, 1981data.xlsx, 1982data.xlsx.
import os import win32com.client path = "C:/Folder" #This folder contains the spreadsheets pathmaster = "C:/Desktop/MyFile.xlsx" xl = win32com.client.DispatchEx('Excel.Application') xl.Visible = False wbmaster = xl.Workbooks.Open(Filename=pathmaster) os.listdir(path) for sheet in sheets: print(sheet) wb1 = xl.Workbooks.Open(Filename=os.path.join(path, sheet)) ws1 = wb1.Worksheets(4) ws1.Name = sheet[:4] ws1.Copy(Before=wbmaster.Worksheets(1)) wb1.Close(True) wbmaster.Close(SaveChanges=True)
In line 4 you can set the folder where you have all the Excel files. Then, line 5 defines the path to the file where you are going to store the extracted worksheets. In line 8, you can specify whether or not you want to see Excel in action doing all these things (i.e., Excel instances are going to start opening and closing as the loop goes on).
In line 18, you can set which worksheet you want to extract. In this case, I am extracting (i.e., copying) the 4th one. In the next line, 19, I am extracting the first four character of the file name, and I add that as the worksheet name in the new file. Finally, line 21 copies the worksheet from the current file to the file where you want to group all the worksheets.