python - Error 1004 executing several macros with xlwings

[ad_1]

I'm trying to run an xls file containing several sheets and macros (all written in modules) with python using xlwings but it seems I'm having trubble when executing more than one macros successively. I've already tried several things to overcome this issue but I couldn't fine the solution yet :

Here is a simple example of my xlwings script and vba macros (all defined in modules) :

Typical VBA macro that i'm trying to run with xlwings, these macros are used to clean some range of a sheet when it finds data in it :

Sub Clear_data()
Application.ScreenUptating = False
Application.Calculation = xlCalculationAutomatic

last_col = Worksheets("Sheet1").Range("ZZ2").End(xlToLeft).Column

If last_col > 6 Then
 Worksheets("Sheet1").Range(Cells(2,7), Cells(6,last_col)).ClearContents
End If

Application.ScreenUptating = False
Application.Calculation = xlCalculationAutomatic
End Sub

Example of xlwings script that I'm running :

def clear_sheet1(filename = "", file_location = "") :

# Connection with the xls workbook
 fullpathname = os.path.join(file_location,filename)
 Workbook = xw.Book(fullpathname)

# Executing the vba macros with xlwings
 #1
 macro1 = Workbook.macro("NAME_OF_MY_MACRO_1")
 macro1()
 #2
 macro2 = Workbook.macro("NAME_OF_MY_MACRO_2")
 macro2()
 #3
 macro3 = Workbook.macro("NAME_OF_MY_MACRO_3")
 macro3()

# Saving and closing the Workbook
Workbook.save()
Workbook.close()
return()

1. When I'm executing all the vba macros in Excel, everything works fine.




2. When I'm runing my function to execute only one of the macros, it's still ok.




3. When I'm runing my function to execute successively several macros (as written in the script example below), I'm systematically getting a VBA error 1004. inside my second macro in the lines where I'm supposed to supress the datas found in a sheet : Worksheets("Sheet1").Range(Cells(2,7), Cells(6,last_col)).ClearContents


Would be much appreciate if someone could share his knowledge of this issue or help me finding what's wrong in my code ! Don't hesitates to ask me for more details if needed.

Thanks a lot :)

NB

[ad_2]

لینک منبع