DATA Step, Macro, Functions and more

Macro/vbscript breaking down?

Reply
Occasional Contributor
Posts: 7

Macro/vbscript breaking down?

Hi,

I am using the EG 4.3 ,where I have a certain project set up. Inside the project there are 3 simple macros (for interchanging some filters based on a name from a list) as well as approx 200 other query buiders, simple run and extraction programs etc. Now, I need to run this project 70 times based on those different names from the list, therefore I have found the following program in VBS that I have been using. It is as follows:

[pre]
Option Explicit
Dim app
Call dowork

If not (app Is Nothing) Then
app.Quit
Set app = Nothing
End If


Sub dowork()
On Error Resume Next
Dim prjName
Dim prjObject
Dim dc
Dim d
Dim tc
Dim t
Dim rc
Dim r
Dim outfile
Dim fileSys
Dim Contents
Dim ext

outfile = "" 'Output file name

Set fileSys = CreateObject("Scripting.FileSystemObject")
Set Contents = fileSys.OpenTextFile(outfile, 2, True)

'Name the project to run
prjName = "" 'Project Name

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

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

d = 0
do while d < 75

prjObject.run
If Checkerror("Project.run") = True Then
Exit Sub
End If

d = d + 1
Loop

prjObject.Close
If Checkerror("Project.Close") = True Then
Exit Sub
End If

End Sub

Function Checkerror(fnName)
Checkerror = False

Dim strmsg
Dim errNum

If Err.Number <> 0 Then
strmsg = "Error #" & Hex(Err.Number) & vbCrLf & "In Function " & fnName & vbCrLf & Err.Description
'MsgBox strmsg
Checkerror = True
End If

End Function
[/pre]

Now this all runs smoothly, however the problem I have is that it keeps on 'crashing' (i.e. stops processing) in random places at random times. Sometimes it does 20 items from the list and then stops, other times it just runs 2 times and stops. I have tried to split the project into 2 smaller ones (thinking that this could be due the the amount of queries), but the problem still prevails.

On the other hand I have identical VBS attached to different project (it's kind of a prestage to this one above), which has identical macros, but has only about 10 queries and this one runs smoothly (i.e. does it 70 times without interuption).

Does anyone know what could cause these interuptions and how to rectify this?

Many thanks in advance,

Simi
Frequent Contributor
Frequent Contributor
Posts: 94

Re: Macro/vbscript breaking down?

Does the last part of the loop that attempts to run produce any errors in the code?

prjObject.run
If Checkerror("Project.run") = True Then
Exit Sub
End If

If any errors crop up, then VBScript will terminate at that point, and won't run any further.

The CheckError function has a popup that's commented out - you could try removing that, and then you should get a popup at that point in the code which might help direct you to the error.

The difficulty you'll have in checking the EG logs is that the script will terminate before saving after it finds any error, so your project (and hence log files) will not be saved.

That said, if it's always at a particular point in the loop (i.e. for a certain value of "d") then you might be able to try running that part manually and see if you get the same issue.
Ask a Question
Discussion stats
  • 1 reply
  • 157 views
  • 0 likes
  • 2 in conversation