Python's win32com.client not opening Excel and refreshing when run from a job scheduler

Question

We have a python program that is designed to open an Excel template document, run refreshall() twice (data and pivots), then saveas() a new filename in an output directory.

When I log in to the server and run the program, everything works as required - both refreshall() steps update and the new file is saved with the updated data. This is the case not matter how the Visible flag is set (True or False).

When we set it as a step in an MS SQL job, the output file is created, but the updates are not done. I've been all over Stack Overflow and the internet in general, and have found no answers to solve this.

Using the combrowse.py script to look at the COM objects, we can see that excel is in the "Running Objects" list when run logged in, but it is NOT in the list when it is running via the MS SQL job (reduced to a single step in the job).

I looked at stdout to see if that might be an issue. My theory is that there is no console in which to operate when run via the job, and so Excel does not start (and the refreshall() cannot run). I found that the stdout when run locally is a UTF-8 encoding and when run via the job is a cp1252 encoding. I couldn't get anything more useful than that.

Code snippet:

from AE import logging, encrypt
import os, sys, shutil, datetime, gc
import time
import win32com.client

script_name = 'PerSta'
log_id = logging.Start_script(script_name)

try:

    logging.Log_script_message(id = log_id, message = 'Opening excel')
    ExcelConn = win32com.client.DispatchEx("Excel.Application")
    logging.Log_script_message(id = log_id, message = 'ExcelConn is:')
    logging.Log_script_message(id = log_id, message = repr(ExcelConn))

    logging.Log_script_message(id = log_id, message = 'Opening {}'.format(script_name))
    PS = ExcelConn.Workbooks.Open(datadict.get('path') + datadict.get('filename'))
    ExcelConn.Interactive = False
    ExcelConn.Visible = False
    ExcelConn.DisplayAlerts = False
    ExcelConn.EnableEvents = False

    logging.Log_script_message(id = log_id, message = 'Refreshing excel first time')
    PS.RefreshAll()
    ExcelConn.CalculateUntilAsyncQueriesDone()
    time.sleep(pause_for_refresh)

    logging.Log_script_message(id = log_id, message = 'Refreshing excel second time')
    PS.RefreshAll() #Refresh again to update any pivots
    ExcelConn.CalculateUntilAsyncQueriesDone()
    time.sleep(pause_for_refresh)

    logging.Log_script_message(id = log_id, message = 'Saving workbook')
    PS.SaveAs(Report)
    time.sleep(pause_for_refresh)

    logging.Log_script_message(id = log_id, message = 'Closing workbook')
    PS.Close(SaveChanges = True)
    time.sleep(pause_for_refresh)
    PS = None

    logging.Log_script_message(id = log_id, message = 'Closing filehandle')
    ExcelConn.Quit()
    ExcelConn = None

except:
    logging.Log_script_message(id = log_id, message = 'Refreshed failed, closing filehandle')
    PS.Close(SaveChanges = False)
    PS = None
    ExcelConn.Quit()
    ExcelConn = None

I believe the issue lies in not having a screen for Excel to do its work, but I have not been able to prove that. We get NO ERRORS at all, either way it is run. I would expect that there would be an error in the job scenario, since it doesn't do what it says that it is, but that is not the case.

Any help would be much appreciated!!

--MIKE--

Edit: the Interactive, Visible, DisplayAlerts, and EnableEvents was put in as testing to see if we could use those to fix the issue. They did not work, no matter how they were set, but left them in in case they came up in discussion.