Hey guys,
In my folder, I have multiple Excel files. Each Excel File contains different data. Also, one of the Excel files has several Worksheets. I intend to copy all worksheets from all Excel
files into one Workbook using Python. Therefore I have created a blank Python file. First things first, I will import the required
libraries. From pathlib, I will import Path. Pathlib is a standard python module. So, no additional installation is required. Next, I will import xlwings as xw. If you do not have xlwings installed yet,
you can install it by typing 'pip install xlwings' in your command prompt or terminal. Once done, I will specify the source directory,
which contains the Excel files. In my case, the folder is called 'Files' and
is located in the same directory as the Python script. If you just want to copy & paste this script,
this is the only thing you would need to change. As the next step, I will store the excel file
paths from the given source directory in a python list. Afterwards, I will create a new excel workbook
by using xw.Book(). I am using this new Excel workbook to combine
all the data from the excel files. This new Workbook will look like this and
has only one empty worksheet so far. To merge the data, I will iterate over each
excel file by using a for-loop. Inside the for loop, I will first open the
Excel file. Then I am using another for loop to iterate
over each worksheet in the open Workbook. By doing so, I can now copy the worksheet
and paste it into our combined Workbook. I will paste it right after the first worksheet. Afterwards, I can close the Excel file. Next, I will do some cleanup and delete the
empty worksheet in our combined excel file before saving it. For the file name, I will type 'all worksheets.xlsx'. After I have saved the data, I will close
the Workbook. This part can be slightly tricky. Because if we run our script and no previous
excel file was open, we are creating a new instance of Excel. Just by closing the Workbook, we are not quitting
the Excel instance. The Excel instance would be still available
in our computer memory. However, I also want to avoid that we are
just quitting the Excel instance. Because in that case, all other Excel files,
which might be currently open your computer will be closed. Therefore I will check how many workbooks
are already open. If it is only one, so only our new combined
Workbook, I can quit the Excel instance. Otherwise, I will close the Workbook. And that's all there is to it. Let me save the script and head back to the
command prompt to execute our python file. Once done, we will have our new Workbook,
which contains all the worksheets from the other excel files. However, every time you run this script, it
will overwrite the existing Workbook. To prevent this, I will append the current
date and time as a unique value to the worksheet's name. Back in the python script, I will import time. I will store the current local time in a variable
called t. Next, I will format the time as follows. I can now take the timestamp and concatenate
it with our Excel file name. Once done, I can rerun the script. Our new excel file name will look like this. Ok, guys, and that's it for this tutorial. As always, if you have any questions, please
let me know in the comments. Thanks for watching, and see you in the next
video.
