BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
4 REPLIES 4
TomKari
Onyx | Level 15
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
deleted_user
Not applicable
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
TomKari
Onyx | Level 15
Very impressive!

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

Tom
ChrisHemedinger
Community Manager
There are lots of other examples of SAS Enterprise Guide automation scripts at:

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

Chris
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1871 views
  • 0 likes
  • 3 in conversation