0

I have a user interface that generates, on a directory called BatForRundeck, a set of CSV files (several per user), xlsm files (one per user) and .bat files (one per user). The processing of these CSV files is done by the generated xlsm file. This xlsm is launched by a VBScript file with parameters but common to all users. The VBScript file is started by the generated .bat. This processing chain should be automated using Rundeck. I am using a new .bat that loops over the BatForRundeck directory to process user generated .bat. The code is as following:

@echo off 
for %%f in (C:\Users\myUserName\Documents\BatForRundeck\*10000*.bat) do call %%f

This .bat program works fine on my PC. But on Rundeck, this program runs but it does not launch Excel (xlsm) and CSV files are not processed.

I then tried another variant:

@echo off 
for %%f in (C:\Users\myUserName\Documents\BatForRundeck\*10000*.bat) do echo call %%f >> C:\Users\myUserName\Documents\BatForRundeck\toto.bat
start C:\Users\myUserName\Documents\BatForRundeck\toto.bat

Remarque : I didn't delete toto.bat, because I wanted to see what it contains.

I have the same result, this .bat works fine on my PC. But on Rundeck, this program does not launch Excel (xlsm).

Example content of toto.bat:

call C:\Users\myUserName\Documents\BatForRundeck\Macro_RSI_V10_4-10000442896544.bat 
call C:\Users\myUserName\Documents\BatForRundeck\Macro_RSI_V10_4-10000880367985.bat

The .bat Macro_RSI_V10_4-10000442896544.bat has the following code :

@echo off 
C:\Users\myUserName\Documents\BatForRundeck\Macro_RSI_V10_4.OID.vbs  Macro_RSI_V10_4.10000442896544.xlsm

And the Macro_RSI_V10_4-10000880367985.bat contains:

@echo off 
C:\Users\myUserName\Documents\BatForRundeck\Macro_RSI_V10_4.OID.vbs  Macro_RSI_V10_4.10000880367985.xlsm

The VBS Macro_RSI_V10_4.OID.vbs program contains the following code:

'Microsoft Excel Automation Basics
':: Open and Execute an Excel File.
'---------------------------------
'Retrieve the parameters that were passed.
Dim Arg
Set Arg = WScript.Arguments

'The first parameter begins with 0 as index.
Dim MacroName
MacroName = Arg(0) 'Ex : Macro_RSI_V10_4.10000442896544.xlsm

'create the excel object
Set objExcel = CreateObject("Excel.Application") 

'view the excel program and file, set to false to hide the whole process
objExcel.Visible = False 

'open an excel file (make sure to verify the location) .xls for 2003 or earlier
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\myUserName\Documents\BatForRundeck\" & MacroName)  
'close the workbook
'objWorkbook.Close 

'exit the excel program
objExcel.Quit

'release objects
Set objExcel = Nothing
Set objWorkbook = Nothing

Communication between Rundeck and my PC is working fine. I manage to launch .bat which create Text files in .txt or .csv formats. But I can't start Excel. The question I ask is whether there is some way to Rundeck special procedure to run an Excel file on my machine or another remote machine?

Mohamad TAGHLOBI
  • 581
  • 5
  • 11
  • 1
    Don't waste your time on doing this with a batch file, it is clear that your Rundeck program is using vbscript, and you have clear patterns, not only with your vbs file names but between the xlsm and bat file names. It would make much more sense to me to do all of this in vbscript! – Compo Dec 28 '20 at 19:39
  • I totally agree with you @Compo. There is a lot of execution transfer in this processing chain. But this way has been validated by the team I work with and by our hierarchy. And I can't get past it. Unless you prove to them that Rundeck can only run the Excel file (.xlsm) by getting rid of the intermediate .bat. I tried to run a .vbs file (Macro_RSI_V10_4.10000442896544.vbs) directly through Rundeck, but Excel didn't launch either. – Mohamad TAGHLOBI Dec 28 '20 at 20:49
  • What do you get if you set the log level to "debug"? – David Oneill Dec 28 '20 at 21:04
  • I assume Rundeck is running under a service account. If it is having issues successfully running a program, some kind of error should be getting logged. I don't use RunDeck but I have used other programs that do what RunDeck can do and I do similar things to what you are doing with the Vbscript. Without knowing what error it is throwing, my best guess is the service account will need to be given permissions to the Microsoft Excel COM object. You can do that with the DCOM config utility. – Squashman Dec 28 '20 at 21:39
  • In debug mode, when I execute the .bat file, I have this part of log file. [wf:83f92a..] Complete: Workflow complete: [Step{stepNum=1, label='null'}: OperationCompleted(identity=[1], stepNum=1, newState=DataState{state={step.1.completed=true, step.any.state.success=true, before.step.1=false, step.1.state=success, after.step.1=true}}, stepResultCapture=StepResultCapture{stepResult=Dispatch successful (1 nodes), stepSuccess=true, statusString='null', controlBehavior=null, resultData=MultiDataContextImpl(map={ContextView(step:1, node:LA2010-303)=BaseDataContext{{exec={exitCode=0}}}, – Mohamad TAGHLOBI Dec 28 '20 at 21:42
  • In the other case when I use .VBS directly, I have this part of log file: /home/rundeck/C:\Users\myUserName\Documents\BatForRundeck\Macro_RSI_V10_4.10000442896544.vbs: No such file or directory [openssh-bastion-host.file-copier]: result code: 1 [workflow] finishExecuteNodeStep(LA2010-303): NodeDispatch: NonZeroResultCode: [openssh-bastion-host.file-copier]: external script failed with exit code: 1 Failed dispatching to node LA2010-303: [openssh-bastion-host.file-copier]: external script failed with exit code: 1 – Mohamad TAGHLOBI Dec 28 '20 at 21:43
  • Thank you Squashman. I'll pass the info on to the whole team. – Mohamad TAGHLOBI Dec 28 '20 at 21:45
  • The error you are getting wouldn't be fixed by my suggestion. – Squashman Dec 29 '20 at 04:56
  • Compo, David, and Squashman. Thank you for your interest in my post. I will see with my team, from January 4, 2021, how to get around this problem and if there is an alternative to Rundeck regarding this treatment. For now, I continue my research about this problem. – Mohamad TAGHLOBI Dec 29 '20 at 15:32
  • Hello! Using python it's possible, take a look: https://stackoverflow.com/a/35940929/10426011 you can create an inline python script pointed to your windows machine: https://stackoverflow.com/a/63741605/10426011 – MegaDrive68k Dec 29 '20 at 16:05
  • MegaDrive68k, Thank you for your reply. I will propose the idea proposed by your answer to my team. – Mohamad TAGHLOBI Dec 29 '20 at 20:19
  • Any time, I posted as an answer for future references. Happy new year! – MegaDrive68k Dec 30 '20 at 12:11

2 Answers2

0

After installing Python in 64 bits and then Python in 32 bits, after using OpenPyXL, xlrd, Win32com, Pythoncom, my problem persisted. Especially since the scripts .bat or .vbs worked very well on my Windows machine and launched Excel as expected.

The message:

pywintypes.com-error: (-2147352567, 'An exception has occurred.', (0, None, None, None, None, 0, -2146788248), None)

which caught my attention to the fact that it is rather a problem of the COM objects.

The solution is to run DCOMCNFG (shortcut Win+R, type DCOMCNFG and then Enter), go to Component Services --> My Computer --> DCOM Config --> Microsoft Excel Application. Now access the Properties and modify the option value in the Identity tab to "The interactive user".

Mohamad TAGHLOBI
  • 581
  • 5
  • 11
-1

An easy solution is to use a python script to launch your file, take at this, you can create an inline python script (or call an external script) on your Rundeck job pointed to your windows machine. Of course, you need to install the python interpreter on your Windows machine.

MegaDrive68k
  • 3,768
  • 2
  • 9
  • 51