Moving Worsksheet after a Named Worksheet in Excel using pywin32 Dispatch

Question

I have tons of files where I need to copy a certain sheet from them to another workbook, they need to be placed after a sheet with a specific name, while keeping all the formatting in the sheet that is being moved.

I saw in another thread that pywin32 would be the way to go, however I am having a hard time with copying this sheet "After" the named sheet.

xl = Dispatch("Excel.Application")

xl.Visible = True  
xl.AskToUpdateLinks = False
xl.EnableEvents = False
xl.DisplayAlerts = False

wb1 = xl.Workbooks.Open(Filename=p1)
wb2 = xl.Workbooks.Open(Filename=p2)    
ws1 = wb1.Worksheets("ThisSheet")

##PROBLEM LINE HERE
ws1.Copy(After=wb2.Worksheets("AfterThisSheet"))
##END OF PROBLEM LINE

wb2.Sheets("ThisSheet").Name = "NewNameInNewWorkBook"
wb2.Close(SaveChanges=True)
wb1.Close(SaveChanges=True)

xl.Quit()

When I use "Before" instead of "After", this operation is done successfully but unfortunately the sheet I want to copy ends in the wrong place.

When I use "After" it throws back an error.

I can use other packages in python like pandas, xlrd etc. but then, they seem to have issues with keeping the formatting intact.

Thanks in Advance

Torrie

As you tagged excel, then here is some vba code I use for something similar : stackoverflow.com/a/30605765 – Sepoy

Answer 1

The solution turned out to be simple actually, it seems that the Copy method required both a specification for "Before" and "After"

##PROBLEM LINE HERE
ws1.Copy(After=wb2.Worksheets("AfterThisSheet"))
##END OF PROBLEM LINE

##CORRECT STATEMENT
ws1.Copy(None, After=wb2.Worksheets("AfterThisSheet"))
##END OF CORRECT STATEMENT

So "None" should be passed to the "Before" parameter, before specifying After.

Torrie