topic Re: Calling SAS through VBA in SAS Enterprise Guide

Calling SAS through VBA

feyzi — Mon, 18 Mar 2013 11:48:12 GMT

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)';

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

Re: Calling SAS through VBA

ChrisHemedinger — Mon, 18 Mar 2013 12:52:06 GMT

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

Re: Calling SAS through VBA

AndreasMenrath — Tue, 19 Mar 2013 09:09:42 GMT

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

Re: Calling SAS through VBA

TomKari — Tue, 19 Mar 2013 16:39:49 GMT

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

Re: Calling SAS through VBA

feyzi — Fri, 22 Mar 2013 15:54:00 GMT

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!

Re: Calling SAS through VBA

AndreasMenrath — Mon, 25 Mar 2013 09:41:48 GMT

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)

Re: Calling SAS through VBA

feyzi — Mon, 25 Mar 2013 11:41:52 GMT

Thanks very much guys. Works perfectly!

Re: Calling SAS through VBA

analystritesh — Fri, 09 Oct 2015 05:14:10 GMT

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.

Re: Calling SAS through VBA

Doc_Duke — Fri, 09 Oct 2015 12:33:48 GMT

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

Re: Calling SAS through VBA

vps — Mon, 09 Nov 2015 05:44:12 GMT

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
---------------------------

Re: Calling SAS through VBA

vps — Mon, 09 Nov 2015 05:45:37 GMT

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
---------------------------

Re: Calling SAS through VBA

vps — Wed, 11 Nov 2015 10:38:57 GMT

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
---------------------------

Re: Calling SAS through VBA

ChrisHemedinger — Wed, 11 Nov 2015 13:09:51 GMT

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.

Re: Calling SAS through VBA

vps — Thu, 12 Nov 2015 05:10:04 GMT

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

Re: Calling SAS through VBA

vps — Thu, 26 Nov 2015 07:08:33 GMT

Can you guide me how to execute block of code or say doing joins ?