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

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

Runtime error '1004' - unable to rename sheet with the same name as another sheet, object library reference, or workbook referenced by VisualBasic.

The macro has the following code:

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 also tried putting these calls in python directly, but get the same error message.

Any suggestions are greatly appreciated!

Thank.

0


source to share


2 answers


I ran the code in Excel VBA.
I am guessing the following line is not working.

Sheets("CC").Delete

And it is for this reason that you cannot give a new sheet the same name as an existing (not deleted) sheet.



Place Application.DisplayAlerts = False

before Sheets("CC").Delete

and Application.DisplayAlerts = True

after code completion.

I haven't used python, but it looks like the library swallows this error for you and lets you move on to the next statement.

Hope it helps.

+2


source


Behind the scenes, VB and VBA maintain COM object references for application, worksheets, and more. This is why you have a globals application, Worksheets, etc. It is possible that VBA is still holding a reference to the worksheet, which is why Excel didn't tidy it up correctly.

Try not to use these implicit globals and explicitly reference the elements in the object model. Alternatively, you can do this 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'

      

+1


source







All Articles