41333members
184398posts

How to call a Vbscript program either by ST

Highlighted
Ensign

How to call a Vbscript program either by ST

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,

 

5 REPLIES 5
Highlighted
Lieutenant JG

Re: How to call a Vbscript program either by ST

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.


Andrew Scott, Lead Engineer, AVEVA
Highlighted
Ensign

Re: How to call a Vbscript program either by ST

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,

 

 

Highlighted
Ensign

Re: How to call a Vbscript program either by ST

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

 

 

 

Highlighted
Sisko

Re: How to call a Vbscript program either by ST

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

Highlighted
Lieutenant JG

Re: How to call a Vbscript program either by ST

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/ 


Andrew Scott, Lead Engineer, AVEVA