I have a simple PowerShell script as below
$excel = new-object -ComObject excel.application
$filePath = 'filename.xlsm'
$wb = $excel.workbooks.open($filePath)
$excel.Run('Macro')
$excel.quit()
Stop-Process -Name "Excel"
When I trigger the script either in SSIS package or through Task Scheduler, it works as expected. But when I deploy the SSIS package in SSMS and trigger the script as SQL Server agent job, I get the error as "Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed due to the following error: 80040154 Class not registered(Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))..".
I made sure I have Excel installed and also entries exist for both InProcServer and LocalServer with CLSID {00024500-0000-0000-C000-000000000046} in Registry Editor. I also registered the dll in InProcHandler32 which is ole32.dll using regsvr32.exe. I also tried replacing the powerShell script with python using win32com library. But getting the error "pywintypes.com_error: (-2147221005, 'Invalid Class string', None, None). We want to migrate the jobs in Task Scheduler to SQL Server agent.
I am not sure why my log shows CLSID as {00000000-0000-0000-0000-000000000000}, when I am trying to open excel with CLSID {00024500-0000-0000-C000-000000000046}. I am not sure even if they are related. Can anyone help me troubleshoot the error.