View Full Version : Run time error 429 - ActiveX component can't create object.
bittu2016
12-08-2016, 07:01 PM
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
12-09-2016, 05:57 AM
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
12-09-2016, 09:07 AM
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
12-09-2016, 09:34 AM
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
12-09-2016, 10:32 AM
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
12-09-2016, 10:13 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.