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.



Answers

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'


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.



The simple answer is variant is weakly typed while duck typing is strongly typed.

Duck typing can be summed up nicely as "if it walks like a duck, looks like a duck, acts like a duck, then it's a duck." It computer science terms consider duck to be the following interface.

interface IDuck {
  void Quack();
}

Now let's examine Daffy

class Daffy {
  void Quack() {
    Console.WriteLine("Thatsssss dispicable!!!!");
  }
}

Daffy is not actually an IDuck in this case. Yet it acts just like a Duck. Why make Daffy implement IDuck when it's quite obvious that Daffy is in fact a duck.

This is where Duck typing comes in. It allows a type safe conversion between any type that has all of the behaviors of a IDuck and an IDuck reference.

IDuck d = new Daffy();
d.Quack();

The Quack method can now be called on "d" with complete type safety. There is no chance of a runtime type error in this assignment or method call.

By : JaredPar


This video can help you solving your question :)
By: admin