How to call a Vbscript program either by ST

[hemanthv]

Hi,

i have the following scripting program. I would like to call it depending on specific condition like day end or hour end. so that excel macro is executed which query clearscada for data and print it to a folder.

Only problem i have is how to execute using a scheduler or a ST program. is there is mechanism to execute it?

Public Sub ExceltoPDF()

Dim objExcel

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'C:\Hawk Backups\batch.xlsm'!Module1.Macro1"
objExcel.DisplayAlerts=False
objExcel.Application.Quit
Set objExcel = Nothing

End Sub

thanks

regards,

[AndrewScott · Accepted solution]

You should be able to do this using a system command, see Core Reference > Core Configuration > Configuring System Commands section of the help. You'll need to save the VBScript into a ".vbs" file that can be run by the system command using the Windows Scripting Host (e.g. cscript.exe <filename>).

You can use a System Command database object along with a Schedule database object. Alternatively, you can use a logic program with a SYSTEM function.

The system command will run on the current main server and therefore both the spreadsheet and script will need to be copied onto all servers (if you have a redundant system). Each server must be setup to allow system commands to be run, see System Administration > Server Administration > System Calls Settings section on the help (e.g. specify the Windows user account to be used).

You can also do this without using ClearSCADA at all, by using the Windows Task Scheduler to run the script on a schedule.

[hemanthv]

Hi Andrew,

I have tried the following

PROGRAM SynchReportDirectories

VAR
Synch, Synch2: DINT;
END_VAR;

Synch:= SYSTEM('"C:\Windows\System32\cmd.exe" "C:\Hawk Backups\EXCEL_EXPORT\excelcommand.bat"');

END_PROGRAM

But in system status windows - it is timing out. also tried cscript.exe and passing the file - it showed success but did not execute the file.

i have attached snapshot of system status,

[hemanthv]

Hi Andrew,

ExcelCommand.bat is

@echo Off
cscript.exe "C:\Hawk Backups\bch.vbs" > c:\Hawk Backups\log.txt
@echo on

bch.vbs file contains

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'C:\Hawk Backups\batch.xlsm'!Module1.Macro1"
objExcel.DisplayAlerts=False
objExcel.Application.Quit
Set objExcel = Nothing

[sbeadle]

Hi Hemanth,

I would check whether there are any security issues, particularly around the execution of vbs scripts.

Perhaps replace your script as a test with a simple 'echo "test" > somefilesomewhere.txt and check that Geo SCADA runs it. 

Steve

[AndrewScott]

It looks like Microsoft Excel is not designed to be compatible with server-side automation. Bear in mind that the ClearSCADA server is running as a Windows service and therefore Excel will also be running under the Windows service but using user impersonation for the specified Windows user.

https://support.microsoft.com/en-us/help/257757/considerations-for-server-side-automation-of-office 

However, a Google search shows some people have been able to get Excel working from a Windows service. The following instructions allowed me to launch Excel without it hanging on my PC:

https://bharathkumaran.wordpress.com/2011/10/25/running-excel-from-a-windows-service/