7

I am having an issue with using win32com and Task Scheduler. An Excel File save operation is not taking place despite other aspects of the script performing successfully. This is being run on an automation machine; there is no active login session. See facts below.

I am new to Win32COM so I may not understand when and how it can be used in Python.

Background

I have a Python script that runs overnight through Task Scheduler which gathers data from an internal database and outputs it into a report. The original script used csv to output data, but I learned about win32com to create xlsx files using Excel VBA commands.

I extended my existing chronjob with code to wrap around existing database operations that use pymysql:

import win32com.client as win32
try:
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    excel.DisplayAlerts = False
    wb = excel.Workbooks.Add()
    basesht = wb.Sheets('Sheet1')
except:
    #E-mail notification of failure.
    sys.exit()

The script goes through a number of database operations which take several minutes. Some export to a .csv with csv, and some create a new sheet and export the acquired data with this code:

#For Each Report in Report Set (DB = Object extending pymysql)
resultSet = db.qryfetchall()
headers = list(resultSet[0].keys())
r, c = 1, len(headers)
wksht.Range(wksht.Cells(r,1),wksht.Cells(r,c)).Value = headers
for row in resultSet:
    r += 1
    wksht.Range(wksht.Cells(r,1),wksht.Cells(r,c)).Value = [str(i) for i in list(row.values())]
    wksht.Columns.AutoFit()
else:
    wksht.Range("A1").Value = "No Results"

The program ends with this:

if wb.Sheets.Count > 1:
    basesht.Delete()
    wb.SaveAs(consolidated) #consolidated = save path
excel.Application.Quit()

Facts

  1. The task in Task Scheduler is setup properly with an administrator account. This is confirmed by other files in the script being created.
  2. Running the script while logged into the automation machine will properly create the Excel file and save.
  3. Task Scheduler reports 0x1 error upon completion.

I dived headfirst into Win32Com so I expect I am missing something. Why won't this script save a file?

MavenACTG
  • 173
  • 9
  • I am not familiar with Blocking vs. Non-Blocking when it comes to Dispatches and the COM, but it may be related. Is my attempt to dispatch and use COM while a user is not logged delving into more advanced computer programming topics? I would need to understand this if I hope to do more advanced Excel VBA manipulation for chronjobs. At the very least, is it possible to find out what error Task Scheduler is throwing, or other information? – MavenACTG Feb 27 '17 at 17:56
  • I am having problems with COM as well. Did you try using openpyxl instead of COM. What you seems to be doing should be achievable through openpyxl without too much hassle – Atanas Atanasov Oct 21 '20 at 08:42

1 Answers1

0

Well its been almost 4 years but no answer, hope its still relevant as I faced this issue recently and managed to solve it. There are 2 reasons for this problem:

  1. DCOM Permission Issue
  2. Running Office Component Non-interactively (Like running through Task Scheduler)

First, have to solve the DCOM Permission Issue:

  1. Go to Start > Run and type dcomcnfg
  2. In the left pane, drill down to Component Services > Computers > My Computer and select DCOM Config
  3. Then find Microsoft Excel Application, and right click select Properties
  4. Go to the Identity tab, select "this user" and enter username/password and then click Apply and Ok

Secondly, Let office component run non-interactively:

Create these two folders (both folders are necessary for a 64bit system):

32Bit:

C:\Windows\System32\config\systemprofile\Desktop

64Bit:

C:\Windows\SysWOW64\config\systemprofile\Desktop

amir.rafieian
  • 96
  • 1
  • 2