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
– TankorSmash, Commented Dec 30, 2014 at 21:32
Thanks. How would I specify the exact worksheet I want to save if I can not use the name? Thanks — user2320821
– user2320821, Commented Dec 30, 2014 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 python-excel.org. The idea is the same there though, open a new workbook, save a sheet, close book, save a different sheet. — TankorSmash
– TankorSmash, Commented Dec 30, 2014 at 22:15

user2320821 · Accepted Answer · 2015-01-02 21:56:23Z

6

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.

answered Jan 2, 2015 at 21:56

user2320821

1,2313 gold badges15 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oliver Over a year ago

awesome. Dont' forget that the way to thank people on SO for the value of their post is to upvote their post.

Oliver · Accepted Answer · 2015-01-03 00:11:04Z

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 3, 2015 at 0:11

answered Dec 31, 2014 at 23:18

Oliver

30k12 gold badges81 silver badges114 bronze badges

2 Comments

user2320821 Over a year ago

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.

Oliver Over a year ago

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 stackoverflow.com/questions/19940409/…. Glad my post was of help although it wasn't completely accurate :)

Sandesh Jadhav M · Accepted Answer · 2020-06-27 04:10:11Z

1

for newer version of excel the codes changes from

xlApp.WorkbookAdd()

to

xlApp.Workbooks.Add()

answered Jun 27, 2020 at 4:10

Sandesh Jadhav M

7510 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related