topic Re: Run a procedure with parameters from Excel or with a VBscript in SAS Enterprise Guide

Run a procedure with parameters from Excel or with a VBscript

deleted_user — Tue, 07 Dec 2010 09:27:47 GMT

Hello,

I want to run an EG procedure from Excel. I'm trying to launch it with a vbscript. Sas is able to generate the code to run it but my procedure contains parameters that are prompted when the proc is runned manually (these parameters (year, month and date) are needed in some queries).

Unfortunatly when the script runs, the parameters are not prompt.
I can prompt informations from the script but I don't know how to run the procedure with this informations as parameters for the procedure.

Here is my code :

Option Explicit
Dim app ' As SASEGuide.Application
dim a ' as integer
dim b ' as integer
dim c ' as integer

a= InputBox("Jour")
b= InputBox("Mois")
c= InputBox("Année")

Call dowork (a,b,c)

'shut down the app
If not (app Is Nothing) Then
app.Quit
Set app = Nothing
End If

Sub dowork(a,b,c)
On Error Resume Next
'----
' Start up Enterprise Guide using the project name
'----
Dim prjName ' As String
Dim prjObject ' As SASEGuide.Project
Dim containerName ' As String
Dim containerObject ' As SASEGuide.Container
Dim containerColl ' As SASEGuide.ContainerCollection
Dim annee ' As integer
Dim mois' As integer
Dim jour' As integer

prjName = "\\xxxxxxxxx.egp" ' Project Name
containerName = "Flux de processus" ' Container Name

Set app = CreateObject("SASEGObjectModel.Application.4")
If Checkerror("CreateObject") = True Then
Exit Sub
End If

Set prjObject = app.Open(prjName,"")
If Checkerror("App.Open") = True Then
Exit Sub
End If

'-----
'Get The Container Collection and Object
'-----
Set containerColl = prjObject.ContainerCollection
If Checkerror("Project.ContainerCollection") = True Then
Exit Sub
End If

Dim i ' As Long
Dim count ' As Long
count = containerColl.count
For i = 0 To count
Set containerObject = containerColl.Item(i)
If Checkerror("ContainerCollection.Item") = True Then
Exit Sub
End If

If (containerObject.Name = containerName) Then
Exit For
Else
Set containerObject = Nothing
End If

Next

If not (containerObject Is Nothing) Then
'----
' Run the Container
'----
containerObject.Run
If Checkerror("Container.Run") = True Then
Exit Sub
End If
End If

'-----
' Save the new project
'-----
prjObject.Save
If Checkerror("Project.Save") = True Then
Exit Sub
End If

'-----
' Close the project
'-----
prjObject.Close
If Checkerror("Project.Close") = True Then
Exit Sub
End If

End Sub

Function Checkerror(fnName)
Checkerror = False

Dim strmsg ' As String
Dim errNum ' As Long

If Err.Number 0 Then
strmsg = "Error #" & Hex(Err.Number) & vbCrLf & "In Function " & fnName & vbCrLf & Err.Description
'MsgBox strmsg 'Uncomment this line if you want to be notified via MessageBox of Errors in the script.
Checkerror = True
End If

End Function

Thank you for your help

Georges

Re: Run a procedure with parameters from Excel or with a VBscript

TomKari — Wed, 08 Dec 2010 02:34:31 GMT

Very interesting idea!

I don't have a "good" solution, but here are a couple of workaround suggestions.

1. I'm pretty sure you can put your parameters into environment variables in VBScript. You could then retrieve them into SAS code using the sysget SAS function.

2. Even more kludgy; write the values to a parameter file, and then read the parameter file in SAS.

I hope somebody comes up with a more elegant solution than these!

Bonne chance,
Tom

Re: Run a procedure with parameters from Excel or with a VBscript

deleted_user — Wed, 08 Dec 2010 09:44:26 GMT

Hello,

If anyone is interested, I found what I need in another forum (I forgot to keep the link for reference, sorry).
The main idea is to open the project in background without running it, defining the parameters and the run it.
My code now look like that :

Option Explicit
Dim app ' As SASEGuide.Application
Dim j ' as integer
Dim m ' as integer
Dim a ' as integer

j = InputBox("Jour")
m = InputBox("Mois")
a = InputBox("Année")

Call dowork(j, m, a)

'shut down the app
If Not (app Is Nothing) Then
app.Quit
Set app = Nothing
End If

Sub dowork(j, m, a)
On Error Resume Next
'----
' Start up Enterprise Guide using the project name
'----
Dim prjName ' As String
Dim prjObject ' As SASEGuide.Project
Dim containerName ' As String
Dim containerObject ' As SASEGuide.Container
Dim containerColl ' As SASEGuide.ContainerCollection
Dim annee ' As integer
Dim mois ' As integer
Dim jour ' As integer
Dim parmList
Dim parm
Dim parm2
Dim parm3

prjName = "\\myfolder\myfile.egp" ' Project Name

Set app = CreateObject("SASEGObjectModel.Application.4")
If Checkerror("CreateObject") = True Then
Exit Sub
End If

Set prjObject = app.Open(prjName, "")
If Checkerror("App.Open") = True Then
Exit Sub
End If

'---
' discover the parameters
'---
Set parmList = prjObject.Parameters
WScript.Echo "Project has " & parmList.count & " parameters."

' work with the first parameter
Set parm = parmList.Item(0)
' WScript.Echo parm.Name & " parameter has default value of " & parm.DefaultValue
parm.Value = m
' WScript.Echo parm.Name & " parameter has been set to value of " & parm.Value

' work with the second parameter
Set parm2 = parmList.Item(1)
' WScript.Echo parm2.Name & " parameter has default value of " & parm2.DefaultValue
parm2.Value = a
' WScript.Echo parm2.Name & " parameter has been set to value of " & parm2.Value

' work with the third parameter
Set parm3 = parmList.Item(2)
' WScript.Echo parm3.Name & " parameter has default value of " & parm3.DefaultValue
parm3.Value = j
' WScript.Echo parm3.Name & " parameter has been set to value of " & parm3.Value

'---------
' run the project
'---------
prjObject.Run

' WScript.Echo parm.Name & " parameter has been set after Run to value of " & parm.Value
' WScript.Echo parm2.Name & " parameter has been set after Run to value of " & parm2.Value
' WScript.Echo parm3.Name & " parameter has been set after Run to value of " & parm3.Value

prjObject.Close
app.Quit
End Sub

Function Checkerror(fnName)
Checkerror = False

Dim strmsg ' As String
Dim errNum ' As Long

If Err.Number <> 0 Then
strmsg = "Error #" & Hex(Err.Number) & vbCrLf & "In Function " & fnName & vbCrLf & Err.Description
'MsgBox strmsg 'Uncomment this line if you want to be notified via MessageBox of Errors in the script.
Checkerror = True
End If

End Function

Re: Run a procedure with parameters from Excel or with a VBscript

TomKari — Wed, 08 Dec 2010 23:15:48 GMT

Very impressive!

I'm definitely going to keep this in my back pocket; I know it'll come in handy someday.

Tom

Re: Run a procedure with parameters from Excel or with a VBscript

ChrisHemedinger — Thu, 09 Dec 2010 13:36:45 GMT

There are lots of other examples of SAS Enterprise Guide automation scripts at:

http://support.sas.com/kb/40/781.html

Chris