SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Connect to SAS with VBA

Reply
New Contributor
Posts: 2

Connect to SAS with VBA

[ Edited ]

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

 

 

Grand Advisor
Posts: 9,740

Re: Connect to SAS with VBA

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

"

Super User
Posts: 981

Re: Connect to SAS with VBA

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

New Contributor
Posts: 2

Re: Connect to SAS with VBA

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.

Post a Question
Discussion Stats
  • 3 replies
  • 155 views
  • 0 likes
  • 3 in conversation