Hello,
I'm running SAS Enterprise Guide 5.1 on an external server and I want to call SAS commands through VBA.
I'm really not knowledgeable in this area so I tried to use a code I found (below). I sort of get what it's doing but clearly not enough.
When I run the code I get the error below.
Wondering if anyone has had any experience with this and any help/guidance would be much appreciated!
Also I have the SAS references ticked in VBA as well as Microsoft ActiveXData Objects.
Thank you.
Emre
Error:
Run-time error '-2147213310 (80042002)';
<connectionAttempts>
<connectionAttempts>
<sasserver></sasserver>
<sasmachinednsname></sasmachinednsname>
<sasport>0</sasport>
<saslogin></saslogin>
<status>0x80040154</status>
Code (highlighted is the problem code):
Dim obWS As SAS.Workspace
Dim obWSM As New SASWorkspaceManager.WorkspaceManager
Sub Form_Load()
Dim obConn As New ADODB.Connection
Dim obRS As New ADODB.Recordset
Dim errorString As String
Rem start the SAS session
Set obWS = obWSM.Workspaces.CreateWorkspaceByServer("My", _
VisibilityProcess, Nothing, "", "", errorString)
Rem submit some SAS code
obWS.LanguageService.Submit _
"PROC IMPORT OUT=PATTERNS_NEW DATAFILE = ""\\Gblontfs01\test.xlsm"" DBMS = EXCELCS REPLACE; SHEET= Export; RUN; PROC APPEND BASE=EO.PATTERNS DATA=PATTERNS_NEW FORCE; RUN;"
Rem open an ADO connection to the data set
Dim connString As String
connString = "provider=sas.iomprovider.1; SAS Workspace ID=" _
+ obWS.UniqueIdentifier
obConn.Open connString
obRS.Open "work.a", obConn, adOpenStatic, adLockReadOnly, _
adCmdTableDirect
Rem write the table as html (visible in Word with tags)
obRS.MoveFirst
Dim sTable As String
sTable = "<TABLE BORDER=0><TBODY><TR><TD class="Data">"
Selection.TypeText sTable
sTable = obRS.GetString(, , "</TD><TD class="Data">", _
"</TD></TR><TR><TD class="Data">")
Selection.TypeText sTable
sTable = "</TD></TR></TBODY></TABLE>"
Selection.TypeText sTable
Rem tidy-up
obRS.Close
obConn.Close
obWS.Close
End Sub
Well the message says everything: you are connecting to the metadata server and not the workspaceserver.
The workspaceservers default port is 8591.
Perhaps the workspaceserver also runs on a different machine. Connect to your profile, go to the serverlist, locate the server you want to connect to and right click the context menu "properties" to see the right connection profile for your workspaceserver.
BTW: if you are using IWA (integrated windows authentication) you must not provide a username and password.
So i suggest this code:
obServer.MachineDNSName = "GBLONTAS68"
obServer.Protocol = SASObjectManager.Protocols.ProtocolBridge
obServer.Port = 8591
Set obSAS = obObjectFactory.CreateObjectByServer("sas", True, obServer, Nothing, Nothing)
I don't have a VBA-ready example, but I do have some examples in Windows PowerShell. The syntax is different, but the same objects are used.
I suggest using SAS Object Manager (instead of Workspace Manager, which is a bit older and supported only as legacy).
See this post:
Using Windows PowerShell to connect to a SAS Workspace server - The SAS Dummy
And also this sasCommunity page:
http://www.sascommunity.org/wiki/Create_Your_Own_Client_Apps_Using_SAS_Integration_Technologies
Chris
Hi,
you said you run EG and SAS on a remote server. So i guess that you don't have a local SAS installation.
When you call the function CreateWorkspaceByServer you should pass a reference to a ServerDef object as the third parameter. In this ServerDef object you define where your remote SAS Server is located (DNS-name, port, protocoll, ...).
You can retrieve most of that information that you need for the connection from the Enterprise Guide "Server List" by right clicking the server and selecting the context menu "properties".
By the way: can somebody at SAS please update the SAS/IT Client documentation?
I was not aware that the Workspace Manager was deprecated and seen as legacy. I just wasted half a day yesterday with some weird COM Exceptions when i tried to create a ServerDef object in a 64bit environment...
Andreas
Hi, Andreas
When I find problems in the documentation, I submit them to tech. support. Up till now, they've accepted them with good grace.
Tom
Hey guys,
Thanks very much for the help and sorry for the late reply.
I'm trying this code...
Sub Form_Load()
Dim obObjectFactory As New SASObjectManager.ObjectFactory
Dim obObjectKeeper As New SASObjectManager.ObjectKeeper
Dim obServer As New SASObjectManager.ServerDef
Dim obSAS As SAS.Workspace
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
obServer.MachineDNSName = "GBLONTAS68"
obServer.Protocol = SASObjectManager.Protocols.ProtocolBridge
obServer.Port = 8561
obObjectFactory.LogEnabled = True
Set obSAS = obObjectFactory.CreateObjectByServer("sas", True, obServer, "myusername", "mypassword")
obSAS.LanguageService.Submit ("DATA EO.VBATEST; SET EO.FX; RUN;")
End Sub
...and getting this error.
Any thoughts on how I can solve this?
Thank you ever so much again!
Well the message says everything: you are connecting to the metadata server and not the workspaceserver.
The workspaceservers default port is 8591.
Perhaps the workspaceserver also runs on a different machine. Connect to your profile, go to the serverlist, locate the server you want to connect to and right click the context menu "properties" to see the right connection profile for your workspaceserver.
BTW: if you are using IWA (integrated windows authentication) you must not provide a username and password.
So i suggest this code:
obServer.MachineDNSName = "GBLONTAS68"
obServer.Protocol = SASObjectManager.Protocols.ProtocolBridge
obServer.Port = 8591
Set obSAS = obObjectFactory.CreateObjectByServer("sas", True, obServer, Nothing, Nothing)
Thanks very much guys. Works perfectly!
Hi,
I am newbie in SAS. I am trying to run a sas code via VBA after setting up SAS Reference in VBA. However i am getting an error massage "Active X component Cann't create object". please note i am using BASE SAS 9.3 and windows 7- 64 bit and excel 64 bit too.
for me its seems like, VBA is unable to communicate with SAS and its unable to create a workspace object at first place.
I have tried my best but didn't get any concrete answer through google. Expert please advise and help me.
Thanks a lot in advance for giving your valuable time to look into this.
Please post your question as a new one. This one was marked 'answered' so many people won't look at it further.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.