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.

scrnshot_sas.PNG

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