BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
feyzi
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
AndreasMenrath
Pyrite | Level 9

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)

View solution in original post

14 REPLIES 14
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
AndreasMenrath
Pyrite | Level 9

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

TomKari
Onyx | Level 15

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

feyzi
Calcite | Level 5

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.

VBA ERROR.JPG

server.JPG

Any thoughts on how I can solve this?

Thank you ever so much again!

AndreasMenrath
Pyrite | Level 9

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)

vps
Fluorite | Level 6 vps
Fluorite | Level 6
Can you guide me. Its not working for me. I am getting Automation error

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '-2147213310 (80042002)':

Automation error
---------------------------
OK Help
---------------------------
vps
Fluorite | Level 6 vps
Fluorite | Level 6
Can you guide me how to execute block of code or say doing joins ?
feyzi
Calcite | Level 5

Thanks very much guys. Works perfectly!

analystritesh
Calcite | Level 5

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.

 

 

Doc_Duke
Rhodochrosite | Level 12

Please post your question as a new one.  This one was marked 'answered' so many people won't look at it further.

vps
Fluorite | Level 6 vps
Fluorite | Level 6
Hi feyzi -
Can you help me with sample code. i am not able to get this. getting an auotmation error

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '-2147213310 (80042002)':

Automation error
---------------------------
OK Help
---------------------------
vps
Fluorite | Level 6 vps
Fluorite | Level 6
even i get this error. Do you have solution

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '-2147467259 (80004005)':

Automation error
Unspecified error
---------------------------
OK Help
---------------------------
ChrisHemedinger
Community Manager
The error codes that you've shared are COM errors: caused when VBA cannot properly use the components that you've referenced.

I suggest that you either create a new topic and supply the code you are trying to use, plus some details of your installation -- or perhaps work with SAS Technical Support to see what's going on with your setup.

This topic already has a solution, so this isn't the best place to post your query.
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
vps
Fluorite | Level 6 vps
Fluorite | Level 6
Chris -
Thanks for the feedback. Can you suggest me a solved post., where can I refer this. Let me explain you again what I am trying to -
I want to build a connection using VBA and get data in to spreadsheet. The SAS is present in one of my server and I will be executing the macro on my local. Above was the code what I have mentioned. The error is prompted only when I try to open the recordset connecion.

Cheers

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 17584 views
  • 7 likes
  • 7 in conversation