Execution of Excel VBA using Python script through Automic throws an error

Products

• CA Automic One Automation

Issue/Introduction

Issue and Scenario:

Executing Excel VBA using Python script through Automation Engine throws an error in the job report:

pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', "Microsoft Excel cannot access the file 'C:\\BIReport\\bin\\Testvb3.xlsm'. There are several possible reasons: 1. The file name or path does not exist. 2. The file is being used by another program. 3. The workbook you are trying to save has the same name as a currently open workbook.", 'xlmain11.chm', 0, -2146827284), None)

When executed from command line, the script runs with no errors. The issue is only present when the job is executed from Automation Engine. 

Files attached for scenario reproduction:

1. Testvb3.xlsm excel VBA file that creates a .pdf file with the current system time and date. If double-clicked, it will execute. 

2. run_excel.py that contains the following code: 

import os
import sys
import win32com.client
import datetime

#------------------------------------------------------
# Functiun do_log activities
#------------------------------------------------------
def do_log(P_message):
   now=datetime.datetime.now().strftime("%Y%m%d %H:%M:%S")
   print(now +" " + P_message)

#------------------------------------------------------
#-- Main
#------------------------------------------------------
#--- Get parameter excel filename
P_filename=sys.argv[1]
G_RC=0

do_log("Start excel Filename=" + P_filename)

#--- If file exist then start excel, open worksheet 
if os.path.exists(P_filename):
    do_log("Macro refresh started")
    xl = win32com.client.DispatchEx('Excel.Application')
    xl.Workbooks.Open(P_filename)  
    xl.Application.Quit()
    del xl
    do_log("Macro refresh completed")
else :
    do_log("ERROR - File not found")
    G_RC=1
 
exit(G_RC)

3. Export of JOBS

4. Requirement: have python installed. 

To reproduce: download all attached files, and put the run_excel.py and Testvb3.xlsm into the same directory; import the job and modify the Process tab of the job to reflect the directory the files are located. In the example, the files are in C:\BIReport\bin. 

Environment

Release : 12.3.X

Component : AUTOMATION ENGINE

OS Windows 10

Python 3.9.5

Microsoft Excel 2016

Cause

Windows OS. A Desktop folder is necessary in the systemprofile folder to open file by Excel, and it is no longer present in C:\Windows\System32\config\systemprofile directory.

Resolution

Error 'pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', "Microsoft Excel cannot access the file 'C:\\BIReport\\bin\\Testvb3.xlsm'. There are several possible reasons: 1. The file name or path does not exist. 2. The file is being used by another program. 3. The workbook you are trying to save has the same name as a currently open workbook.", 'xlmain11.chm', 0, -2146827284), None)' is coming from Windows OS, and is applicable to both older and newer versions of Microsoft OS, Excel, and Python. 

Please see Microsoft Community Post on that.

Resolution:

1. Create the following directories if these are not present:

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

  C:\Windows\System32\config\systemprofile\Desktop (this one typically missing)

2. Rerun the job.