0

I am trying to combine multiple excel workbooks into one workbook, with multiple sheets. The names and contents from original workbooks are maintained in the final workbook. eg. let say I have test1.xls(sheeta,sheetb), test2.xls(sheet1), test3.xls(sheet2). The final file output.xls(sheeta,sheetb,sheet1,sheet2). I have seen other similar posts here, but did not find to work for me. Some of them do not copy exact content of the sheet. I have pictures and data in each sheet.

kristo
  • 57
  • 1
  • 5
  • What have you done this far and is there any error messages? – ex4 Apr 27 '20 at 03:26
  • And is there any reason you want to do this in Python. If this is a real life problem, I'd look at Excel's built-in Visual Basic. Then you could do the same later on with macro-enabled Excel-file and you wouldn't need to install Python with Excel-library on your computer. Looks a little bit like a homework for me though. – ex4 Apr 27 '20 at 03:28
  • @ex4, what if Excel isn't installed on his system? But Visual Basic is also quite slow for a lot of things. – Charlie Clark Apr 28 '20 at 14:03
  • @CharlieClark it's feels unexpected situation that someone works with Excel files like this but doesn't have Excel. Possible of course. And yes, VB is awful to write and usually very slow. But it works with this kind of stuff. – ex4 Apr 28 '20 at 16:47
  • There has been similar posting with good conversation: https://stackoverflow.com/questions/42344041/copy-worksheet-from-one-workbook-to-another-one-using-openpyxl . So it seems that there is no simple way to do that. It's doable to copy data in cells anyway. – ex4 Apr 28 '20 at 17:03
  • Does this answer your question? [Copy worksheet from one workbook to another one using Openpyxl](https://stackoverflow.com/questions/42344041/copy-worksheet-from-one-workbook-to-another-one-using-openpyxl) – ex4 Apr 28 '20 at 17:04
  • Sorry for the delay. I have VB code Sub GetSheets() Path = "C:\Users\ABC\" Filename = Dir(Path & "*.xlsx") Do While Filename <> "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub but it adds multiple of same file sometimes and I have to manually delete those. – kristo Apr 30 '20 at 02:20
  • I tried following python code import pandas as pd import glob import os import win32com.client as win32 path_read =r'C:/Users/ABC/' excel = Dispatch('Excel.Application') wb = excel.Workbooks.Add() for f in glob.glob(path_read + "/*.xls"): w = excel.Workbooks.Open(f) w.Sheets(1).Copy(wb.Sheets(1)) wb.SaveAs(os.path.join(os.getcwd(), "CM.xlsx")) excel.Application.Quit() but it copies only the data, my excel sheets has pictures which are not copied. – kristo Apr 30 '20 at 02:21

0 Answers0