Run time error 429 - ActiveX component can't create object.

[bittu2016]

Hi ,
I am trying to execute the SAS through Excel VBA 2016 , and I am getting an error : 429 - ActiveX component can't create object.
It worked in excel 2010 , could anyone help me.


Sub ExecuteSascmd()
Dim app
Dim prjObject
Dim codeObj
Dim prjName
Dim codeFile
Dim logFile
Dim sSASCmd
Dim fileSys, fReadStream, fileString
Dim profile As String
Dim i As Integer


Application.StatusBar = "Executing sas script at sever. Please wait....."
sSASCmd = sSASCmd & "%include'" & sampath & "/scripts/" & sasExecutable & "';"
sSASCmd = sSASCmd & "ENDSAS;"

Set app = CreateObject("SASEGObjectModel.Application.5.1")
app.SetActiveProfile (sasEG_profile)

Set prjObject = app.New()

Set fileSys = CreateObject("Scripting.FileSystemObject")

Set codeObj = prjObject.CodeCollection.Add

codeObj.Server = codeServer
codeObj.Text = sSASCmd
Application.DisplayAlerts = False

codeObj.Run
Application.DisplayAlerts = True
Application.StatusBar = "Data is uploaded to the server"

End Sub

[Kenneth Hobs]

Without SAS, your macros and such, I have no way of testing that. I guess you have defined sampath as a global variable and assigned the value elsewhere?

I guess the "m" in sampath means path for SAS macros. You have some other variables with no apparent values as well. That is why I set Option Explicit as first line of code in a Module and Compile code before a run.

[bittu2016]

Hi Kenneth,
here is variables declaration

Option ExplicitPublic outlookfolder As String
Public strFilePathAttachment As String
Public samuserid As String
Public sampwd As String
Public dfrFileName As String
Public todaydate As String
Public sampath As String
Public todaytime As String
Public proc_dt As String
Public sPath As String
Public nCol As Integer
Public sasEG_profile As String
Public codeServer As String
Public pathWinScp As String
Public output_filename As String
Public sasExecutable As String


Sub SetupVariable()



samuserid = Trim(ThisWorkbook.Sheets("Form").EGIDtxt.Value)
sampwd = Trim(ThisWorkbook.Sheets("Form").EGpwtxt.Value)
sampath = "/user/samgw/SAS"
todaydate = Trim(Format(Date, "yyyymmdd"))
todaytime = Trim(Format(Now, "yyyymmddhhmmss"))
proc_dt = Format(Date, "yyyymmdd")
sPath = Trim(ThisWorkbook.Sheets("Form").txtPath.Value)
sasEG_profile = "sasgridnode4"
codeServer = "SASApp"
sasExecutable = "script.sas"


output_filename = "Output_scriptresults_" & Format(Date, "yyyymmdd") & ".xlsx"


End Sub

[Kenneth Hobs]

I'm wondering if sampath still needs some work? If you want to use the local user's profile then should it not be fully qualified with drive C:?

Of course the subfolder "/SAS/scripts" must also exist.

You can hard code the user's profile path or let Excel do it:

sampath = Environ("userprofile") & "/SAS"

[bittu2016]

Not working :( , do you have a script for SAS connection through VBA ? , I want to execute the SAS script and save the results in a given destination.
Here is what I am trying to do.

17794

Thanks
Bittu

[bittu2016]

here is the complete script, could you please help me in fixing the error.



Option Explicit
Dim SASConnect As Boolean

Option Explicit
Public samuserid As String
Public sampwd As String
Public todaydate As String
Public sampath As String
Public todaytime As String
Public proc_dt As String
Public sPath As String
Public sasEG_profile As String
Public codeServer As String
Public pathWinScp As String
Public output_filename As String
Public sasExecutable As String


Sub SetupVariable()
samuserid = Trim(ThisWorkbook.Sheets("Form").EGIDtxt.Value)
sampwd = Trim(ThisWorkbook.Sheets("Form").EGpwtxt.Value)
sampath = "/user/samgw/SAS"
todaydate = Trim(Format(Date, "yyyymmdd"))
todaytime = Trim(Format(Now, "yyyymmddhhmmss"))
proc_dt = Format(Date, "yyyymmdd")
sPath = Trim(ThisWorkbook.Sheets("Form").txtPath.Value)
sasEG_profile = "sasgridnode"
codeServer = "SASApp"
sasExecutable = "script.sas"


If FileFolderExists("C:\Program Files\WinSCP\winscp.com") = True Then
pathWinScp = "C:\Program Files\WinSCP\winscp.com"
ElseIf FileFolderExists("C:\Program Files\WinSCP3\winscp3.com") = True Then
pathWinScp = "C:\Program Files\WinSCP3\winscp3.com"
ElseIf FileFolderExists("C:\Program Files (x86)\WinSCP3\winscp3.com") = True Then
pathWinScp = "C:\Program Files (x86)\WinSCP3\winscp3.com"
ElseIf FileFolderExists("C:\Program Files (x86)\WinSCP\winscp.com") = True Then
pathWinScp = "C:\Program Files (x86)\WinSCP\winscp.com"
Else
MsgBox ("WinSCP directory is not found as the default.")
End
End If
output_filename = "Output_scriptresults_" & Format(Date, "yyyymmdd") & ".xlsx"


End Sub


Public Function FileFolderExists(strFullPath As String) As Boolean
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
EarlyExit:
On Error GoTo 0


End Function




Public Sub MainProcess()


Call SetupVariable
Call ExecuteSas
Call FtpReceive


End Sub




Sub ExecuteSas()
Dim app
Dim prjObject
Dim codeObj
Dim prjName
Dim codeFile
Dim logFile
Dim sSASCmd
Dim fileSys, fReadStream, fileString
Dim profile As String
Dim i As Integer


Application.StatusBar = "Executing sas script at sever. Please wait....."
sSASCmd = sSASCmd & "%include'" & sampath & "/scripts/" & sasExecutable & "';"
sSASCmd = sSASCmd & "ENDSAS;"

Set app = CreateObject("SASEGObjectModel.Application.5.1")
app.SetActiveProfile (sasEG_profile)

Set prjObject = app.New()

Set fileSys = CreateObject("Scripting.FileSystemObject")

Set codeObj = prjObject.CodeCollection.Add

codeObj.Server = codeServer
codeObj.Text = sSASCmd
Application.DisplayAlerts = False

codeObj.Run
Application.DisplayAlerts = True
Application.StatusBar = "Data is uploaded to the server"

End Sub


Public Sub FtpReceive()


Dim fNum As Long

Application.StatusBar = "Downloading the excel file from sam"
Call SetupVariable
If FileFolderExists(sPath & "\output" & todaydate) = False Then
MkDir sPath & "\output" & todaydate
End If
fNum = FreeFile()
Open sPath & "\" & "FtpCommDownload.txt" For Output As #fNum
Print #1, "open " & samuserid & ":" & sampwd & "@sasgridnode"
Print #1, "option batch "
Print #1, "option confirm off"
Print #1, "cd " & sampath & "/data/data_" & todaydate
If Application.Version = "11.0" Then
Print #1, "get " & output_filename & " " & sPath & "\output" & todaydate & "\" & output_filename
Else
Print #1, "get " & output_filename & " " & sPath & "\output" & todaydate & "\" & output_filename
End If
Print #1, "chmod 777 " & output_filename
Print #1, "close"
Print #1, "exit"
Close

Shell pathWinScp & " /script=" & sPath & "\FtpCommDownload.txt", vbNormalNoFocus


Application.StatusBar = "Ready to view "
End Sub


Public Function FileFolderExists(strFullPath As String) As Boolean
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
EarlyExit:
On Error GoTo 0
End Function