Calling macro from python script bombs out when custom table style is used


A

accessnewbie

I have a python script (2.7.5) that calls a macro from an excel template. Both scripts ran fine until i tried to apply a custom table style within excel. I suspect this has something to do with the win32.com client application not recognizing custom table styles. Does anyone have a work around suggestion?

My python script:

import os.path
import win32com.client

mySourceExcelFile = "C:\\tests\\Source\\Cats.xlsm"
projectArea = ("C:\\tests\\Target\\results\\")
projectName = ("Cats")
print projectName
print repr(projectArea)
xlApp = win32com.client.DispatchEx('Excel.Application')
xlsPath = os.path.expanduser(mySourceExcelFile)
wb = xlApp.Workbooks.Open(Filename=xlsPath)
wb.Application.DisplayAlerts = False
xlApp.Run("FormatFile", projectArea, projectName)

xlApp.Quit()

My 2007 excel macro

Sub FormatFile(strResultsDir As String, targetFileName As String)
'
'
Application.DisplayAlerts = False

Workbooks.Open fileName:=strResultsDir & targetFileName & ".dbf"
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1", ActiveCell.SpecialCells(xlLastCell)), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
' ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight1"
ActiveSheet.ListObjects("Table1").TableStyle = "MyStyle"

ActiveWorkbook.SaveAs fileName:=strResultsDir & targetFileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Application.Quit

End Sub

Macro bombs out when it gets to my custom table style

ActiveSheet.ListObjects("Table1").TableStyle = "MyStyle"

Works fine when table style is one that comes with excel: TableStyleLight1
 
Ad

Advertisements


Top