Connect to SAS with VBA

[PascalA]

Hello Community,

I have a question to the following problem with VBA.

This code works fine:

Public Sub ThatWorks()
  'References in use
  '- SAS: Integrated Object Model (IOM) (SAS System 9.4) Type Library
  '- SASObjectManager 1.1 Type Library


  'Declare constants
  Const cintPort As Integer = 1234
  Const cstrMachineDNSName As String = "..."
  Const cintProtocol As Integer = 2 'SASObjectManager.ProtocolBridge
  Const cstrName As String = "MyName"

  'Declare variables
  Dim objSAS As SAS.Workspace
  Dim objOF As New SASObjectManager.ObjectFactory
  Dim objServerDef As New SASObjectManager.ServerDef
  

  'Server definition
  With objServerDef
    .Protocol = cintProtocol
    .MachineDNSName = cstrMachineDNSName
    .Port = cintPort
  End With

  'Create connection and connect.
  Set objSAS = objOF.CreateObjectByServer(cstrName, True, objServerDef, "...", "...")
    
  'Test
  With objSAS.LanguageService
    .Submit "%put Hello World;"
    Debug.Print .FlushLog(10000)
  End With
  
  'Disconnect and delete connection
  objSAS.Close
  
  Set objSAS = Nothing
  Set objOF = Nothing
  Set objServerDef = Nothing

End Sub

I use the references and all is okay.

But, when a collegue open the project (in this case, it's Microsoft Access, the sample is Microsoft Excel, but this is doesn't matter), he get's errors, because he has no SAS on his computer. But the project offers other possibilities, too.

So, I try late binding and creates objects to runtime.

Public Sub ThatDoesNotWorks()
  'No References in use


  'Declare constants
  Const cintPort As Integer = 1234
  Const cstrMachineDNSName As String = "..."
  Const cintProtocol As Integer = 2 'SASObjectManager.ProtocolBridge
  Const cstrName As String = "MyName"

  'Declare variables
  Dim objSAS As Object
  Dim objOF As Object
  Dim objServerDef As Object
  
  Set objSAS = CreateObject("SAS.Workspace")
  Set objOF = CreateObject("SASObjectManager.ObjectFactory")
  Set objServerDef = CreateObject("SASObjectManager.ServerDef")
  

  'Server definition
  With objServerDef
    .Protocol = cintProtocol
    .MachineDNSName = cstrMachineDNSName
    .Port = cintPort
  End With

  'Create connection and connect.
  Set objSAS = objOF.CreateObjectByServer(cstrName, True, objServerDef, "...", "...")
    
  'Test
  With objSAS.LanguageService
    .Submit "%put Hello World;"
    Debug.Print .FlushLog(10000)
  End With
  
  'Disconnect and delete connection
  objSAS.Close
  
  Set objSAS = Nothing
  Set objOF = Nothing
  Set objServerDef = Nothing

End Sub

The command

CreateObject("SAS.Workspace")

creates the error. If I don't use this line, then I have an error in line "CreateObjectByServer".

I don't know why.

Where is the difference?

Has anyone the same problem or have a hint?

Thank you in advance.

Many greets

Pascal

[ballardw]

It may help someone with more knowledge of VBA than I have to provide the actual text of the error when using:

"

The command

CreateObject("SAS.Workspace")

"

[JuanS_OCS]

Hello @PascalA,

I am not an expert on VBA, but only some background as java programmer.

My best guess is that probably your colleagues are missing the SAS Integration Technologies installed on their PCs, or some Microsoft components are missing. I would compare the installed programs and components on your machine and your colleagues' .

[PascalA]

Thank you for your replies.

Sorry, I forgot to describe the error message.
The problem is, that the command "CreateObject("SAS.Workspace") creates the error "Runtime Error 429 - ActiveX Component Can't Create Object". But only, when I not use the VBA references to SAS.

When I use the references, all is fine.
Well, it's just a question. I can use the references, but I would like to know, why it doesn't run with CreateObject.

When I find the solution, I post it here.

[edkazuo1]

Hi... this kind of error ocurrs when MS-Office is 64 bits version and SAS Integration Tecnologies is a 32 bit version .

[JeffMaggio]

I have the same issue. 

I would also note that when I early bind the SASObjectManager.ObjectFactory, the issue disappears. I can use:

  Dim obObjectFactory As New SASObjectManager.ObjectFactory
  Dim obObjectKeeper As Object
  Dim obServer As Object
  Dim obSAS As Object

  Set obObjectKeeper = CreateObject("SASObjectManager.ObjectKeeper")
  Set obServer = CreateObject("SASObjectManager.ServerDef")

  obServer.MachineDNSName = "sasmachine.sas.com"
  obServer.Protocol = 2
  obServer.Port = "1234"

'I can skip  Set obSAS = CreateObject("SAS.Workspace")
'and go right to

  Set obSAS = obObjectFactory.CreateObjectByServer("sas1", True, obServer, "username", "password")
  obObjectKeeper.AddObject 1, "sas1", obSAS

  obConnection.Open "provider=sas.iomprovider.1; SAS Workspace ID=" + obSAS.UniqueIdentifier

With a reference to the ObjectManager checked and IOM unchecked.

I am trying to switch to late binding for the same reason as the OP. The file offers additional value without connecting to SAS and not all users of the file are SAS users.

I would also note that switching to late binding worked fine when I was using SASWorkspaceManager, however, since some users now have newer computers with only SAS 7.1, the WorkspaceManager doesn't work for them.