Why does Excel macro work in Excel but not when called from Python?

Question

I have an Excel macro that deletes a sheet, copies another sheet and renames it to the same name of the deleted sheet. This works fine when run from Excel, but when I run it by calling the macro from Python I get the following error message:

' Run-time error '1004': Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by VisualBasic. '

The macro has code like the following:

Sheets("CC").Delete
ActiveWindow.View = xlPageBreakPreview
Sheets("FY").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "CC"

and the debugger highlights the error on the last line where the sheet is renamed. I've also tried putting these calls directly in python but get the same error message.

Any suggestions are much appreciated!

Thanks.

Answer 1

I ran the code inside Excel VBA.
I am guessing that the following line is failing.

Sheets("CC").Delete

And that is the reason, you can't give the new sheet same name as existing (non-deleted) sheet.

Put Application.DisplayAlerts = False before Sheets("CC").Delete and
Application.DisplayAlerts = True once you are finished with the code.

I haven't used python but it seems the library is swallowing that error for you and letting you go ahead to the next statement.

Hope that helps.

Answer 2

Behind the scenes, VB and VBA are maintaining references to COM objects for the application, worksheets etc. This is why you have the globals 'Application', 'Worksheets' etc. It is possible that VBA is still holding a reference to the worksheet, so Excel hasn't tidied it up properly.

Try not using these implicit globals and referencing the items in the object model explicitly. Alternatively you could do it directly in Python.

Here's a python script that will do something like what you want:

import win32com.client
xl = win32com.client.Dispatch ('Excel.Application')
xl.Visible = True
wb = xl.Workbooks.Add()
wb.Worksheets[0].Delete()
wb.Worksheets.Add()
wb.Worksheets[0].Name = 'Sheet1'