topic Re: Connecting to SAS fomr Excel/VBA in Microsoft Integration with SAS

Connecting to SAS fomr Excel/VBA

amueller11 — Wed, 16 Nov 2011 16:24:40 GMT

Hi there,

we have a bunch of Excel/VBA-programs to do our reporting.

Our data we select from a SQL-DB.

Now we changed our DB to SAS.

I'm now trying to connect to SAS to use the reporting as it is, but the connection doesn't work.

I use the following code to build up a connection to SAS.

Dim obObjectFactory As New SASObjectManager.ObjectFactory

Dim obServer As New SASObjectManager.ServerDef

Dim sasWS As sas.Workspace

obServer.MachineDNSName = "lcub1sa"

obServer.Protocol = SASObjectManager.Protocols.ProtocolBridge

obServer.Port = 18561

Set sasWS = obObjectFactory.CreateObjectByServer("My_Prod_Data", True, obServer, "myusername", "mypassword")

When running this code i get an exception

"SASMessage severity = "Error" : The client has connected to a SAS (9.2) Metadata Server (v1.0) when it intended to connect to a SAS Workspace Server."

Any ideas to avoid this exception and to set up a valid connection are wellcome.

Regard

Achim Müller

Re: Connecting to SAS fomr Excel/VBA

PhilC — Fri, 13 Sep 2019 13:30:22 GMT

@CaseySmith

I'm looking to answer this question. My Google skills are weak. Is there a good search term you'd recommend?

Re: Connecting to SAS fomr Excel/VBA

CaseySmith — Mon, 25 Nov 2019 23:17:14 GMT

Hey @PhilC,

I searched, "sas integration technologies client documentation", which led me here:
https://support.sas.com/rnd/itech/doc9/dev_guide/index.html

...then to here:
https://support.sas.com/rnd/itech/doc9/dev_guide/dist-obj/winclnt/index.html
...and to some code samples:
https://support.sas.com/rnd/itech/doc9/dev_guide/dist-obj/winclnt/omsamples.html

In the example above, the error is (as it says) a result of attempting to connect to a metadata server (usually port 8561) instead of a workspace server. If you change the port to the workspace server port (usually 8591), it should work.

Casey

ps- Sorry for the delayed response, I've fallen behind on Communities lately due to another project.