SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Having Problem with SAS connecting to VBA excel 2003

Reply
Occasional Contributor
Posts: 8

Having Problem with SAS connecting to VBA excel 2003

Need help for solving this problem....

I have code like this since my PC having upgrade from w2k to XP this vba script can't work as usually....

this is my code.....

Sub ProcessCreditShield()
Dim objWSM As New SASWorkspaceManager.WorkspaceManager
Dim objWS As SAS.Workspace
Dim objWSflag As Integer
Dim objConn As New ADODB.Connection
Dim objRS As New ADODB.Recordset
Dim objLibref As SAS.Libref
Dim objSP As SAS.StoredProcessService
Dim errorString As String
Dim szStatement As String

Dim szFiledirectory As String
Dim szFilename As String
Dim szExtension As String

Application.DisplayAlerts = False
Set objWS = objWSM.Workspaces.CreateWorkspaceByServer("Local", VisibilityProcess, _
Nothing, "", "", errorString)

szStatement = "proc printto log='C:\\saslog\\Add.log' " _
& "new; run;"
objWS.LanguageService.Submit szStatement

Set objSP = objWS.LanguageService.StoredProcessService
objSP.Repository = "file:G:\\GRCTELEMIS\\DATA MIS\\Daily Report\\SasProgram\\Project\\Autoexecute"
objSP.Execute Name:="ADD", NameValuePairs:=""
objWS.Close

szFiledirectory = "C:\ExcelProject\Output\"
szFilename = "ADD"
szExtension = ".xls"
If Not IsFileOpen(szFiledirectory & szFilename & szExtension) Then
Workbooks.Open FileName:=szFiledirectory & szFilename & szExtension
End If
Macro1
End Sub

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select
End Function

'======================================

on debugging process stop at line :

Set objWS = objWSM.Workspaces.CreateWorkspaceByServer("Local", VisibilityProcess, _
Nothing, "", "", errorString)


error message :




.......
........

'==================================

Any one can help me??

Thx and rgds,

Yadi Acho
Ask a Question
Discussion stats
  • 0 replies
  • 261 views
  • 0 likes
  • 1 in conversation