Python - Win32com - Open Workbook & Create a New Excel File for Each Tab

Question

I have an MS Excel Workbook that I would like to open and then loop through the tabs and create and save a workbook for each tab in the original workbook. So I open file A and there are tabs 1, 2, 3 and create and save a file B, C, D each with one a unique tab in it. I have the code for the VBA which creates a single copy of a worksheet but when I attempt to do this in Python I end up with all the tabs in each workbook. The following is the VBA that works:

 Sub ConvertTabsToFiles()
    Dim currPath As String
    currPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=currPath & "\" & xWs.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
 Next
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
 End Sub

The following is the Python Code that does not work:

 xlApp = win32.com.client.Dispatch("Excel.Application")
 xlwb = xlApp.Workbooks.Open("C:\Inputfile.xlsx")

 for sheet in xlwb.Worksheets:
     sheet.Copy
     xlApp.ActiveWorkbook.SaveAs("C:\Users\user\AppData\Local\Temp\\"+ sheet.Name+".xlsx")

Your help is really appreciated, I am stumped. Thanks ahead of time.

`sheet.Copy` is just a name, it doesn't do anything without being called...unless it's a property. — TankorSmash, Dec 30 '14 at 21:32
Thanks. How would I specify the exact worksheet I want to save if I can not use the name? Thanks — user2320821, Dec 30 '14 at 22:11
You'd need to create a new workbook for each `sheet`. I've never used the `win32com` stuff to do this, I've always used http://www.python-excel.org/. The idea is the same there though, open a new workbook, save a sheet, close book, save a different sheet. — TankorSmash, Dec 30 '14 at 22:15

score 6 · Accepted Answer · answered Jan 02 '15 at 21:56

Thanks for Schollii, I was able to get on the right track. The following is what worked for me, I hope it helps you:

for sheet in xlwb.Worksheets:
     xlApp = win32com.client.Dispatch("Excel.Application")
     nwb = xlApp.WorkbookAdd()
     sheet.Copy(Before=nwb.Sheet(1))
     nwb.SaveAs("C:\Users\user\AppData\Local\Temp\\" +sheet.Name+ ".xlsx")
     nwb.Close(True)

Thank you everyone. Especially Schollii for getting me on the right track. Also Thanks TankorSmash for the answer, too.

awesome. Dont' forget that the way to thank people on SO for the value of their post is to upvote their post. — Oliver, Jan 03 '15 at 00:15

Oliver · Answer 2 · 2015-01-03T00:11:04.673

5

You can save individual sheets via the SaveAs method:

for sheet in xlwb.Worksheets:
     filename = r"C:\Users\user\AppData\Local\Temp\" + sheet.Name + ".xlsx"
     sheet.SaveAs(filename)
     print('Saved sheet to', filename)

Note I put the 'r' prefix to the string otherwise the backslashes get interpreted by Python as special characters unless doubled which obfuscates the string.

edited Jan 03 '15 at 00:11

answered Dec 31 '14 at 23:18

Oliver

27,510
9
72
103

Schollii thanks that is exactly what I am attempting to do. I tried the code and it still saves a copy of the entire workbook and not just the individual tab/spreadsheet in the original. – user2320821 Jan 02 '15 at 14:13
1

Indeed, this seems to be either a bug or a badly documented function, and I can't try it locally, unfortunately. See for example the second answer to http://stackoverflow.com/questions/19940409/use-vba-macro-to-save-each-excel-worksheet-as-separate-workbook. Glad my post was of help although it wasn't completely accurate :) – Oliver Jan 03 '15 at 00:22

score 1 · Answer 3 · answered Jun 27 '20 at 04:10

1

for newer version of excel the codes changes from

xlApp.WorkbookAdd()

to

xlApp.Workbooks.Add()

answered Jun 27 '20 at 04:10

Sandesh Jadhav M

65
10

Python - Win32com - Open Workbook & Create a New Excel File for Each Tab

3 Answers3

Linked