0

I just want to copy the all avaible excel files and paste in one excel sheet. I dont want to find by header(since it is already avaialbe in power query) Just copy paste/consolidet all the data available. here's my code.

import xlwings as xw  # pip install xlwings

SOURCE_DIR = 'Files'

excel_files = list(Path(SOURCE_DIR).glob('*.xlsx'))
combined_wb = xw.Book()
t = time.localtime()
timestamp = time.strftime('%Y-%m-%d_%H%M', t)

for excel_file in excel_files:
    wb = xw.Book(excel_file)
    for sheet in wb.sheets:
        sheet.copy(after=combined_wb.sheets[0])
    wb.close()

combined_wb.sheets[0].delete()
combined_wb.save('output.xlsx')
if len(combined_wb.app.books) == 1:
    combined_wb.app.quit()
else:
    combined_wb.close()
Dilip Anand
  • 35
  • 6
  • What is the specific issue with your code? – BigBen Oct 11 '22 at 15:47
  • it opens excel file but no data saved in output – Dilip Anand Oct 11 '22 at 15:49
  • Make sure you look for the file in the right directory. It will [should] be in the same directory that the python file is run, not **'SOURCE_DIR'**. If your not sure you can put the full path and file name in **combined_wb.save()** so it saves where you want it. – moken Oct 14 '22 at 01:38
  • Does this answer your question? [Python: How to copy Excel worksheet from multiple Excel files to one Excel file that contains all the worksheets from other Excel files](https://stackoverflow.com/questions/67778710/python-how-to-copy-excel-worksheet-from-multiple-excel-files-to-one-excel-file) – mouwsy Feb 08 '23 at 11:50

0 Answers0