ExecuteCommand() argument working from cmd but not TM1

[ofintm1]

Hi All,

I am trying to use the ExecuteCommand() function in TI to run a VB script with parameters but I'm currently having trouble getting it to run.

I have tried using both variables to construct the argument string and hardcoding an example for testing (see code below), neither of which worked.

The same string when entered directly on the command line works correctly. I have tested this with both my own account and logged into the TM1 server with the TM1 service account, both of which were fine. This made me think TM1 had all the permissions it needed but possibly not?

The script takes a few seconds to run and I have tried changing the wait argument on and off in TI and it makes no difference.

Is there anything else I should check in terms of permissions? Is there a way to debug this to see further information on where it is failing? Anything wrong with the string argument below?

Further information, which could be useful:
The VB script is opening a workbook and running a VBA macro on the TM1 server. The macro opens another workbook based on the date parameters provided and it transforms it ready for import into TM1 as a .csv. It saves the output on the TM1 server.

I'm running the TI from TM1 Perspectives 10.2.2 via a terminal services server.


Any help would be much appreciated,

Thanks

Code: Select all

########################################################################################
#Execute command to run VBScript with date parameters
########################################################################################

sFolderPath = '\\testTM1svr\tm1\Load Files\';
sFileName = 'RunConvertFiles.vbs';
sYear = '2016';
sMonth = '6';
sDay = '17';

#sArguments = 'CScript "' | sFolderPath | sFileName | '" "' | sDay | '" "' | sMonth | '" "' | sYear | '"';

sArguments = 'CScript "\\testTM1svr\tm1\Load Files\RunConvertFiles.vbs" "167 "6" "2016"';

AsciiOutput(sFolderPath | 'script arguments debug.txt',sArguments);

ExecuteCommand(sArguments, 1);

[Karthik1710]

Most of the time these kinds of issues are due to presence of Space in a filepath and any improper usage of double quotes.

Can you post the output you got from ASCIIOUTPUT?

One more thing you can try is.. probably see if you can print the arguments from the vb script to check if they are received correctly?

[TrevorGoss]

Hello,

Most of the time these kinds of issues are due to presence of Space in a filepath and any improper usage of double quotes.

This may well be the case with your issue.

When using double quotes for the execute command function, we use this code:

Code: Select all

## Double quotes for spaces in commmand
vQuote = CHAR(34);

sCmd = 'cmd /c copy z:\Output\SourcePath\' |vQuote | 'test ---  data.csv' | vQuote | 'z:\Output\TargetPath\';

## Execute the command and do not wait for it to finish

ExecuteCommand(sCmd,0);

The Quote character is provided at run time, to enable it to be used as part of another argument.

[ofintm1]

Thanks both for your suggestions. I've just had a chance to look into this again, sorry for the late reply.

Karthik - the following was the ASCIIOutput. Can you confirm the first and final double quotes are expected (I think they are when using ASCIIOutput)?

"CScript "\\testTM1svr\tm1\Load Files\RunConvertFiles.vbs" "16" "6" "2016""

When you say print the arguments from the vb script, I presume you mean to a text file? I tried that by adding the following to the end of the script before it quits:

Code: Select all

Set objFSO=CreateObject("Scripting.FileSystemObject")

outFile="\\testTM1svr\tm1\Load Files\test_arguments_recevied.txt"
Set objFile = objFSO.CreateTextFile(outFile,True)
objFile.Write WScript.Arguments(0) & WScript.Arguments(1) & WScript.Arguments(2) & vbCrLf
objFile.Close

When run from the command line outside of TM1, this creates a textfile with "1662016" (without quotes) and the script runs successfully.

When run from the ExecuteCommand function in TM1, the file is not created at all - am I right in thinking this means the script is not run at all and the problem must be a security issue?

Trevor - thanks I gave the CHAR(34) function a go but unfortunately this did not resolve it either.

Code: Select all

sDoubleQuote = CHAR(34);
sArguments = 'CScript ' | sDoubleQuote | '\\testTM1svr\tm1\Load Files\RunConvertFiles.vbs' | sDoubleQuote | ' ' | sDoubleQuote | '16' | sDoubleQuote | ' ' | sDoubleQuote |'6' | sDoubleQuote |' ' | sDoubleQuote | '2016' | sDoublequote;

ExecuteCommand(sArguments,  0);

The ASCIIOutput from using this was exactly the same as above when typing the double quotes inside the single quote string in the TI. Both work from command line but not TM1.

Any other suggestions?

Thanks,

Oli

[jim wood]

You're running it using a UNC path for a network drive. Which user is running your TM1 service? I ask as you when you run it from the command line you may have access to the network share but the user running the TM1 service may not,

Jim.

[ofintm1]

To update - I finally found the answer at the end of a similar thread here:http://www.tm1forum.com/viewtopic.php?t=12402.

I created C:\Windows\SysWOW64\config\systemprofile\desktop on the TM1 server and suddenly everything worked.

Note that before that I had found the script was running but Excel was not opening. This seems to be an issue with automating Office on Win Server 2008 (and later).

Thanks,

Oli