|
|
|||||||
We have a small requirement to open an Excel file, run a macro, save the workbook and close Excel. In VBS this works: Code: Set objExcel = CreateObject("Excel.Application") objExcel.Application.Run "'D:\ExcelFile.xlsm'!Sheet1.OpenAnExistingWorkbookSet" objExcel.DisplayAlerts = False objExcel.Application.Save objExcel.Application.Quit Set objExcel = Nothing but I seem to be unable to translate it to kix. Should not be that difficult I think but it does not execute the macro. The VBS code works but the KIX code does not work. I made some changes to the code but no success yet. The settings in Excel are unchanged between the VBS and kix code. Somehow I'm missing something. The fact that COM is not something I find easy and fully understand does not help also. Code: $objExcel = CreateObject("Excel.Application") $objExcel.Application.Run "'D:\ExcelFile.xlsm'!Sheet1.OpenAnExistingWorkbookSet" $objExcel.DisplayAlerts = False $objExcel.Application.Save $objExcel.Application.Quit $objExcel = "" |
||||||||
|
|
|||||||
These two lines are really the only ones that could be questionable. $objExcel.Application.Run "'D:\ExcelFile.xlsm'!Sheet1.OpenAnExistingWorkbookSet" $objExcel.DisplayAlerts = False Maybe $objExcel.Application.Run("'D:\ExcelFile.xlsm'!Sheet1.OpenAnExistingWorkbookSet") $objExcel.DisplayAlerts = not 1 |
||||||||
|
|
|||||||
Yesterday evening I found a solution that works but I need to verify this with the owner of the Excel file to be sure that the macro runs. More info will follow later today. |
||||||||
|
|
|||||||
Working code on Windows 7 and Excel (Office) 2016. Code: ;Set Break state to on Break On ;Initiate Excel object $objExcel = CreateObject("Excel.Application") ;Set Excel object to visible $objExcel.Visible = True ;Open workbook $objWorkbook = $objExcel.Workbooks.Open("D:\MyExcelFile.xlsm") ;Run macro $objExcel.Run("MacroNameGoesHere") ;Save workbook $objWorkbook.Save $objWorkbook.Close ;Close Excel $objExcel.Quit |
||||||||
|
|
|||||||
The fact that this works: Code: ;Set Excel object to visible $objExcel.Visible = True Is just weird, "True" is not a boolean value in Kix. Seemingly the object accepts anything other then a boolean False value as True. |